Deleting all collections under a capture results in "Statement Timeout" for users with `estuary_support/` role

[dyaffe]

Bug
If you use our new feture to delete all collections under a task, it results in a statement timeout. A couple that can be referenced:

estuary/marketing/github_estuary/source-github
estuary/marketing/search-console/source-google-search-console

Screenshots

[travjenkins]

Called: https://eyrcnmuzzyriypdajwdk.supabase.co/rest/v1/rpc/draft_collections_eligible_for_deletion

Response:

{
    "code": "57014",
    "details": null,
    "hint": null,
    "message": "canceling statement due to statement timeout"
}

[travjenkins]

I do not see where this would be an issue with the UI. Tried looking into it on the back end and not really sure where to look.

I see logs in Supabase for the PostgREST call and the corresponding Postgres calls failing due to timeout.

We'll need someone from the back end to look into this. Moving it over to flow as I am not really sure the best place to put this.

[psFried]

This seems to be related to slow evaluation of RLS policies. TLDR is that this only affects users with the estuary_support/ role. For other users, this feature seems to be working as intended. Here's the query plan when running as a user that doesn't have the estuary_support/ role:

QUERY PLAN
Nested Loop (cost=248812.39..750272.54 rows=22 width=119) (actual time=184.277..184.290 rows=0 loops=1)
CTE target_collections
-> Index Only Scan using idx_live_spec_flows_forward on live_spec_flows (cost=248807.35..248811.98 rows=44 width=8) (actual time=43.640..43.665 rows=59 loops=1)
Index Cond: (source_id = '0a:dd:37:7b:0b:d9:64:00'::macaddr8)
Filter: ((hashed SubPlan 2) OR (hashed SubPlan 4))
Heap Fetches: 0
SubPlan 2
-> Seq Scan on live_specs (cost=0.00..124350.00 rows=21388 width=8) (actual time=6.731..43.315 rows=96 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 37180
SubPlan 1
-> Function Scan on auth_roles r (cost=0.25..12.75 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=37276)
Filter: ((live_specs.catalog_name)::text ^@ (role_prefix)::text)
Rows Removed by Filter: 1
SubPlan 4
-> Seq Scan on live_specs live_specs_1 (cost=0.00..124350.00 rows=21388 width=8) (never executed)
Filter: (SubPlan 3)
SubPlan 3
-> Function Scan on auth_roles r_1 (cost=0.25..12.75 rows=5 width=0) (never executed)
Filter: ((live_specs_1.catalog_name)::text ^@ (role_prefix)::text)
-> Subquery Scan on eligible_collection_ids (cost=0.00..501223.73 rows=44 width=8) (actual time=184.277..184.284 rows=0 loops=1)
-> HashSetOp Except (cost=0.00..501223.29 rows=44 width=12) (actual time=184.276..184.284 rows=0 loops=1)
-> Append (cost=0.00..501222.85 rows=175 width=12) (actual time=43.644..184.249 rows=118 loops=1)
-> Subquery Scan on "SELECT 1" (cost=0.00..1.32 rows=44 width=12) (actual time=43.644..43.690 rows=59 loops=1)
-> CTE Scan on target_collections (cost=0.00..0.88 rows=44 width=8) (actual time=43.643..43.682 rows=59 loops=1)
-> Subquery Scan on "SELECT 2" (cost=501216.72..501220.65 rows=131 width=12) (actual time=140.522..140.547 rows=59 loops=1)
-> HashAggregate (cost=501216.72..501218.03 rows=131 width=8) (actual time=140.521..140.539 rows=59 loops=1)
Group Key: target_collections_1.target_id
Batches: 1 Memory Usage: 40kB
-> Append (cost=248809.43..501216.40 rows=131 width=8) (actual time=62.094..140.493 rows=59 loops=1)
-> Merge Join (cost=248809.43..250556.36 rows=75 width=8) (actual time=62.093..69.852 rows=59 loops=1)
Merge Cond: ((target_collections_1.target_id)::macaddr8 = (lsf.source_id)::macaddr8)
-> Sort (cost=2.08..2.19 rows=44 width=8) (actual time=0.046..0.054 rows=59 loops=1)
Sort Key: target_collections_1.target_id
Sort Method: quicksort Memory: 27kB
-> CTE Scan on target_collections target_collections_1 (cost=0.00..0.88 rows=44 width=8) (actual time=0.000..0.006 rows=59 loops=1)
-> Index Only Scan using idx_live_spec_flows_forward on live_spec_flows lsf (cost=248807.35..250505.67 rows=19054 width=8) (actual time=62.042..69.765 rows=129 loops=1)
Filter: ((hashed SubPlan 8) OR (hashed SubPlan 10))
Rows Removed by Filter: 25276
Heap Fetches: 9714
SubPlan 8
-> Seq Scan on live_specs live_specs_2 (cost=0.00..124350.00 rows=21388 width=8) (actual time=4.048..30.014 rows=96 loops=1)
Filter: (SubPlan 7)
Rows Removed by Filter: 37180
SubPlan 7
-> Function Scan on auth_roles r_3 (cost=0.25..12.75 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=37276)
Filter: ((live_specs_2.catalog_name)::text ^@ (role_prefix)::text)
Rows Removed by Filter: 1
SubPlan 10
-> Seq Scan on live_specs live_specs_3 (cost=0.00..124350.00 rows=21388 width=8) (actual time=3.758..28.590 rows=96 loops=1)
Filter: (SubPlan 9)
Rows Removed by Filter: 37180
SubPlan 9
-> Function Scan on auth_roles r_4 (cost=0.25..12.75 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=37276)
Filter: ((live_specs_3.catalog_name)::text ^@ (role_prefix)::text)
Rows Removed by Filter: 1
-> Merge Join (cost=248809.43..250658.07 rows=56 width=8) (actual time=70.630..70.632 rows=0 loops=1)
Merge Cond: ((target_collections_2.target_id)::macaddr8 = (lsf_1.target_id)::macaddr8)
-> Sort (cost=2.08..2.19 rows=44 width=8) (actual time=0.019..0.025 rows=41 loops=1)
Sort Key: target_collections_2.target_id
Sort Method: quicksort Memory: 27kB
-> CTE Scan on target_collections target_collections_2 (cost=0.00..0.88 rows=44 width=8) (actual time=0.001..0.008 rows=59 loops=1)
-> Index Only Scan using idx_live_spec_flows_reverse on live_spec_flows lsf_1 (cost=248807.35..250607.69 rows=19010 width=8) (actual time=60.987..70.594 rows=70 loops=1)
Filter: (((source_id)::macaddr8 <> '0a:dd:37:7b:0b:d9:64:00'::macaddr8) AND ((hashed SubPlan 12) OR (hashed SubPlan 14)))
Rows Removed by Filter: 25335
Heap Fetches: 9714
SubPlan 12
-> Seq Scan on live_specs live_specs_4 (cost=0.00..124350.00 rows=21388 width=8) (actual time=3.655..27.940 rows=96 loops=1)
Filter: (SubPlan 11)
Rows Removed by Filter: 37180
SubPlan 11
-> Function Scan on auth_roles r_5 (cost=0.25..12.75 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=37276)
Filter: ((live_specs_4.catalog_name)::text ^@ (role_prefix)::text)
Rows Removed by Filter: 1
SubPlan 14
-> Seq Scan on live_specs live_specs_5 (cost=0.00..124350.00 rows=21388 width=8) (actual time=3.691..29.823 rows=96 loops=1)
Filter: (SubPlan 13)
Rows Removed by Filter: 37180
SubPlan 13
-> Function Scan on auth_roles r_6 (cost=0.25..12.75 rows=5 width=0) (actual time=0.000..0.000 rows=0 loops=37276)
Filter: ((live_specs_5.catalog_name)::text ^@ (role_prefix)::text)
Rows Removed by Filter: 1
-> Index Scan using live_specs_pkey on live_specs ls (cost=0.41..5.38 rows=1 width=63) (never executed)
Index Cond: ((id)::macaddr8 = (eligible_collection_ids.target_id)::macaddr8)
Filter: (SubPlan 6)
SubPlan 6
-> Function Scan on auth_roles r_2 (cost=0.25..12.75 rows=5 width=0) (never executed)
Filter: ((ls.catalog_name)::text ^@ (role_prefix)::text)
Planning Time: 1.875 ms
Execution Time: 185.015 ms

For a user that does have estuary_support/, explain analyze just times out. But here's the explain output:

QUERY PLAN
Nested Loop (cost=239076.23..721064.03 rows=22 width=119)
CTE target_collections
-> Index Only Scan using idx_live_spec_flows_forward on live_spec_flows (cost=239071.18..239075.81 rows=44 width=8)
Index Cond: (source_id = '0a:dd:37:7b:0b:d9:64:00'::macaddr8)
Filter: ((hashed SubPlan 2) OR (hashed SubPlan 4))
SubPlan 2
-> Seq Scan on live_specs (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 1)
SubPlan 1
-> Function Scan on auth_roles r (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs.catalog_name)::text ^@ (role_prefix)::text)
SubPlan 4
-> Seq Scan on live_specs live_specs_1 (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 3)
SubPlan 3
-> Function Scan on auth_roles r_1 (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs_1.catalog_name)::text ^@ (role_prefix)::text)
-> Subquery Scan on eligible_collection_ids (cost=0.00..481751.39 rows=44 width=8)
-> HashSetOp Except (cost=0.00..481750.95 rows=44 width=12)
-> Append (cost=0.00..481750.51 rows=175 width=12)
-> Subquery Scan on "SELECT 1" (cost=0.00..1.32 rows=44 width=12)
-> CTE Scan on target_collections (cost=0.00..0.88 rows=44 width=8)
-> Subquery Scan on "SELECT 2" (cost=481744.38..481748.31 rows=131 width=12)
-> HashAggregate (cost=481744.38..481745.69 rows=131 width=8)
Group Key: target_collections_1.target_id
-> Append (cost=239073.26..481744.06 rows=131 width=8)
-> Merge Join (cost=239073.26..240820.19 rows=75 width=8)
Merge Cond: ((target_collections_1.target_id)::macaddr8 = (lsf.source_id)::macaddr8)
-> Sort (cost=2.08..2.19 rows=44 width=8)
Sort Key: target_collections_1.target_id
-> CTE Scan on target_collections target_collections_1 (cost=0.00..0.88 rows=44 width=8)
-> Index Only Scan using idx_live_spec_flows_forward on live_spec_flows lsf (cost=239071.18..240769.50 rows=19054 width=8)
Filter: ((hashed SubPlan 8) OR (hashed SubPlan 10))
SubPlan 8
-> Seq Scan on live_specs live_specs_2 (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 7)
SubPlan 7
-> Function Scan on auth_roles r_3 (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs_2.catalog_name)::text ^@ (role_prefix)::text)
SubPlan 10
-> Seq Scan on live_specs live_specs_3 (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 9)
SubPlan 9
-> Function Scan on auth_roles r_4 (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs_3.catalog_name)::text ^@ (role_prefix)::text)
-> Merge Join (cost=239073.26..240921.90 rows=56 width=8)
Merge Cond: ((target_collections_2.target_id)::macaddr8 = (lsf_1.target_id)::macaddr8)
-> Sort (cost=2.08..2.19 rows=44 width=8)
Sort Key: target_collections_2.target_id
-> CTE Scan on target_collections target_collections_2 (cost=0.00..0.88 rows=44 width=8)
-> Index Only Scan using idx_live_spec_flows_reverse on live_spec_flows lsf_1 (cost=239071.18..240871.52 rows=19010 width=8)
Filter: (((source_id)::macaddr8 <> '0a:dd:37:7b:0b:d9:64:00'::macaddr8) AND ((hashed SubPlan 12) OR (hashed SubPlan 14)))
SubPlan 12
-> Seq Scan on live_specs live_specs_4 (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 11)
SubPlan 11
-> Function Scan on auth_roles r_5 (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs_4.catalog_name)::text ^@ (role_prefix)::text)
SubPlan 14
-> Seq Scan on live_specs live_specs_5 (cost=0.00..119484.12 rows=20506 width=8)
Filter: (SubPlan 13)
SubPlan 13
-> Function Scan on auth_roles r_6 (cost=0.25..12.75 rows=5 width=0)
Filter: ((live_specs_5.catalog_name)::text ^@ (role_prefix)::text)
-> Index Scan using live_specs_pkey on live_specs ls (cost=0.41..5.38 rows=1 width=63)
Index Cond: ((id)::macaddr8 = (eligible_collection_ids.target_id)::macaddr8)
Filter: (SubPlan 6)
SubPlan 6
-> Function Scan on auth_roles r_2 (cost=0.25..12.75 rows=5 width=0)
Filter: ((ls.catalog_name)::text ^@ (role_prefix)::text)