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

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 is n/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%)