db: Switch to statement rather than row trigger
Created by: ryanslade
The trig_soft_delete_orphan_repo_by_external_service_repo was previously triggered per row when an external_service_repos row was deleted.
We have switched this to run after the entire statement affecting external_service_repos has run. Running per row led to a quadratic number of statments being run.
Onc significant side effect of this change is that after any row is deleted in external_service_repos ANY orphaned repos will be soft deleted rather than repos only affected by the deleted row. The reason for this is that after statement triggers don't have access to what changed in Postgre 9.6 which we need to support. From 10 we could use transition tables.
Given that whenver we add a repo we should ALWAYS also add an entry in the external_service_repos table this should not pose an issue.
Performance:
In testing locally, deleting a an external service with a large number of repos dropped from around 20s to 8s. When the number of repos owned by the external service was doubled, the runtime also doubled showing that we are now O(n).
Closes: https://github.com/sourcegraph/sourcegraph/issues/16069
Merge request reports
Activity
Created by: sourcegraph-bot
Notifying subscribers in CODENOTIFY files for diff 98ffef32bfcbf5fc7eb7e7e5f82c836c9a8bdad9...1ad05b36f182098f2c4952c5da1a20da5849a85d.
Notify File(s) @LawnGnome internal/db/external_services_test.go @unknwon enterprise/internal/db/perms_store_test.go Created by: codecov[bot]
Codecov Report
Merging #16163 (1ad05b3) into main (98ffef3) will increase coverage by
0.00%
. The diff coverage isn/a
.@@ Coverage Diff @@ ## main #16163 +/- ## ======================================= Coverage 52.81% 52.81% ======================================= Files 1646 1646 Lines 82351 82351 Branches 7316 7316 ======================================= + Hits 43493 43494 +1 Misses 35006 35006 + Partials 3852 3851 -1
Flag Coverage Δ go 52.57% <ø> (+<0.01%)
integration 28.49% <ø> (ø)
storybook 28.03% <ø> (ø)
typescript 53.39% <ø> (ø)
unit 35.90% <ø> (ø)
Impacted Files Coverage Δ ...nterprise/cmd/frontend/auth/httpheader/provider.go 0.00% <0.00%> (-20.00%)
...prise/cmd/frontend/auth/httpheader/config_watch.go 83.33% <0.00%> (+33.33%)