Skip to content
Snippets Groups Projects

db: Switch to statement rather than row trigger

Merged Administrator requested to merge orphaned-repo-trigger into main

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

Merged by avatar (Jul 7, 2025 5:56pm UTC)

Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • 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%) :arrow_up:
    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%) :arrow_down:
    ...prise/cmd/frontend/auth/httpheader/config_watch.go 83.33% <0.00%> (+33.33%) :arrow_up:
Please register or sign in to reply
Loading