Launchpad itself

sanitizedb fails due to triggers attempting to modify mutating tables

Bug #479805 reported by Stuart Bishop
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
High
Stuart Bishop
Launchpad itself 3.1.11

Bug Description

sanitizedb.py turns all our foreign key constraints into ON DELETE CASCADE foreign key constraints to allow us to delete private data and all its dependencies.

Unfortunately, on some tables we have triggers. One limitation of triggers is that they should not modify a mutating table, which for us only becomes apparent with the cascading deletes.

psycopg2.IntegrityError: insert or update on table "bug" violates foreign key constraint "bug_duplicateof_fk"
DETAIL: Key (duplicateof)=(42) is not present in table "bug".
CONTEXT: SQL statement "UPDATE Bug SET date_last_message = max_datecreated FROM ( SELECT BugMessage.bug, max(Message.datecreated) AS max_datecreated FROM BugMessage, Message WHERE BugMessage.id <> $1 AND BugMessage.bug = $2 AND BugMessage.message = Message.id GROUP BY BugMessage.bug ) AS MessageSummary WHERE Bug.id = MessageSummary.bug"
PL/pgSQL function "set_bug_date_last_message" line 7 at SQL statement

Related branches

lp://qastaging/~stub/launchpad/sanitize-db
Stuart Bishop (community): Approve
Diff: 715 lines (+0/-701)
3 files modified
lib/lp/scripts/utilities/sanitizedb.py (+0/-597)
lib/lp/scripts/utilities/tests/test_sanitizedb.py (+0/-89)
utilities/sanitize-db.py (+0/-15)
Revision history for this message
Stuart Bishop (stub) wrote :

High priority since lots of people seem to want this for I don't know what reasons.

Changed in launchpad-foundations:
status: New → Triaged
importance: Undecided → High
assignee: nobody → Stuart Bishop (stub)
Revision history for this message
Stuart Bishop (stub) wrote :

I think we will need to special case these as the script trips over them on the production data - I don't think we can handle this programatically and there shouldn't be many cases to worry about.

Stuart Bishop (stub)
Changed in launchpad-foundations:
status: Triaged → Fix Committed
milestone: none → 3.1.12
Curtis Hovey (sinzui)
Changed in launchpad-foundations:
milestone: 3.1.12 → 3.1.11
Diogo Matsubara (matsubara)
Changed in launchpad-foundations:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.