launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

The proposal to merge lp:~cjwatson/launchpad/db-bugsummary-statement-triggers into lp:launchpad/db-devel has been updated.

Description changed to:

Rewrite the BugSummaryJournal maintenance triggers to make use of the new transition tables provided to AFTER ... FOR EACH STATEMENT triggers as of PostgreSQL 10.  Instead of using row-level triggers which accumulate changes in a temporary table and flush it into the journal, we now write directly to the journal at the end of each statement.

There's a considerable amount of complexity here due to transition tables only being visible in the trigger function itself, not in functions that it calls.  I used a combination of LATERAL and dynamic commands to minimise the amount of repeated code resulting from this.  This does mean that the outermost part of each trigger function's query needs to be replanned each time the trigger runs, but I don't expect that to make a significant performance difference.

The transformations related to bug tags aren't completely obvious.  I eliminated summarise_bug and unsummarise_bug from the call chain, as they didn't seem to be pulling their weight.  When BugTag is changed, rather than decrementing/incrementing all the BugSummary rows that the changed bugs expand to, it now makes more sense to do so only for the rows relating to the changed tags.  This necessitated extending bugsummary_journal_bugtaskflat and friends to take an array of tags, so it now processes all tags on the bug plus NULL when handling BugTaskFlat changes, and only the changed tags when handling BugTag changes.

I can't say for sure whether this will fix the periodic bug update timeouts we've been seeing, since we've never completely got to the bottom of their cause.  However, reducing the number of times the trigger functions need to be called and eliminating their use of an explicit temporary table seem likely to improve matters.

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/db-bugsummary-statement-triggers/+merge/371297
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/db-bugsummary-statement-triggers into lp:launchpad/db-devel.