Launchpad itself

Merge lp:~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog into lp:launchpad/db-devel

  1. gpgkey-fks-db-hack-the-catalog
  2. Merge into db-devel
Proposed by William Grant
Status: Merged
Merged at revision: 13357
Proposed branch: lp:~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog
Merge into: lp:launchpad/db-devel
Diff against target: 64 lines (+26/-5)
1 file modified
database/schema/patch-2209-75-0.sql (+26/-5)
To merge this branch: bzr merge lp:~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog
Reviewer Review Type Date Requested Status
William Grant db Approve
Review via email: mp+288480@code.launchpad.net

Commit message

Avoid table scans for fingerprint CHECK constraints by hacking the catalog.

Description of the change

Avoid table scans for fingerprint CHECK constraints by hacking the catalog.

Pre-validating the constraints is slow (full table scan), and ALTER TABLE ...
VALIDATE CONSTRAINT before 9.4 takes a very unpleasant ACCESS EXCLUSIVE lock,
so we seem to be stuck with minutes of downtime. But we know that the columns
are new and null, so the constraints are definitely satisfied at this point.
Manually hack them to validated in pg_catalog.pg_constraint.

Verified with "pg_dump --schema pg_catalog launchpad_dev" and the test suite
that convalidated is the only change and the server doesn't do anything
silly like crashing.

Yes, I just changed a DB patch that was already on staging. Edgy.

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/schema/patch-2209-75-0.sql'
2--- database/schema/patch-2209-75-0.sql 2016-03-03 13:48:05 +0000
3+++ database/schema/patch-2209-75-0.sql 2016-03-09 03:18:08 +0000
4@@ -8,34 +8,55 @@
5 ADD COLUMN signing_key_fingerprint text,
6 ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
7 signing_key_fingerprint IS NULL
8- OR valid_fingerprint(signing_key_fingerprint));
9+ OR valid_fingerprint(signing_key_fingerprint))
10+ NOT VALID;
11
12 ALTER TABLE packageupload
13 ADD COLUMN signing_key_owner integer REFERENCES Person,
14 ADD COLUMN signing_key_fingerprint text,
15 ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
16 signing_key_fingerprint IS NULL
17- OR valid_fingerprint(signing_key_fingerprint));
18+ OR valid_fingerprint(signing_key_fingerprint))
19+ NOT VALID;
20
21 ALTER TABLE revision
22 ADD COLUMN signing_key_owner integer REFERENCES Person,
23 ADD COLUMN signing_key_fingerprint text,
24 ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
25 signing_key_fingerprint IS NULL
26- OR valid_fingerprint(signing_key_fingerprint));
27+ OR valid_fingerprint(signing_key_fingerprint))
28+ NOT VALID;
29
30 -- Already has "owner".
31 ALTER TABLE signedcodeofconduct
32 ADD COLUMN signing_key_fingerprint text,
33 ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
34 signing_key_fingerprint IS NULL
35- OR valid_fingerprint(signing_key_fingerprint));
36+ OR valid_fingerprint(signing_key_fingerprint))
37+ NOT VALID;
38
39 ALTER TABLE sourcepackagerelease
40 ADD COLUMN signing_key_owner integer REFERENCES Person,
41 ADD COLUMN signing_key_fingerprint text,
42 ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
43 signing_key_fingerprint IS NULL
44- OR valid_fingerprint(signing_key_fingerprint));
45+ OR valid_fingerprint(signing_key_fingerprint))
46+ NOT VALID;
47+
48+-- Pre-validating the constraints is slow (full table scan), and
49+-- ALTER TABLE ... VALIDATE CONSTRAINT before 9.4 takes a very unpleasant
50+-- ACCESS EXCLUSIVE lock, so we seem to be stuck with minutes of downtime.
51+-- But we know that the columns are new and null, so the constraints are
52+-- definitely satisfied at this point. Manually hack them to validated.
53+UPDATE pg_constraint SET convalidated=true
54+FROM pg_class, pg_namespace
55+WHERE
56+ pg_class.oid = pg_constraint.conrelid
57+ AND pg_namespace.oid = pg_class.relnamespace
58+ AND pg_constraint.conname = 'valid_signing_key_fingerprint'
59+ AND pg_namespace.nspname = 'public'
60+ AND pg_class.relname IN (
61+ 'archive', 'packageupload', 'revision', 'signedcodeofconduct',
62+ 'sourcepackagerelease');
63
64 INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 75, 0);

Subscribers

People subscribed via source and target branches

to status/vote changes: