launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog into lp:launchpad/db-devel.

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

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog/+merge/288480

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.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/gpgkey-fks-db-hack-the-catalog into lp:launchpad/db-devel.

=== modified file 'database/schema/patch-2209-75-0.sql'
--- database/schema/patch-2209-75-0.sql	2016-03-03 13:48:05 +0000
+++ database/schema/patch-2209-75-0.sql	2016-03-09 03:18:08 +0000
@@ -8,34 +8,55 @@
     ADD COLUMN signing_key_fingerprint text,
     ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
         signing_key_fingerprint IS NULL
-        OR valid_fingerprint(signing_key_fingerprint));
+        OR valid_fingerprint(signing_key_fingerprint))
+        NOT VALID;
 
 ALTER TABLE packageupload
     ADD COLUMN signing_key_owner integer REFERENCES Person,
     ADD COLUMN signing_key_fingerprint text,
     ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
         signing_key_fingerprint IS NULL
-        OR valid_fingerprint(signing_key_fingerprint));
+        OR valid_fingerprint(signing_key_fingerprint))
+        NOT VALID;
 
 ALTER TABLE revision
     ADD COLUMN signing_key_owner integer REFERENCES Person,
     ADD COLUMN signing_key_fingerprint text,
     ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
         signing_key_fingerprint IS NULL
-        OR valid_fingerprint(signing_key_fingerprint));
+        OR valid_fingerprint(signing_key_fingerprint))
+        NOT VALID;
 
 -- Already has "owner".
 ALTER TABLE signedcodeofconduct
     ADD COLUMN signing_key_fingerprint text,
     ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
         signing_key_fingerprint IS NULL
-        OR valid_fingerprint(signing_key_fingerprint));
+        OR valid_fingerprint(signing_key_fingerprint))
+        NOT VALID;
 
 ALTER TABLE sourcepackagerelease
     ADD COLUMN signing_key_owner integer REFERENCES Person,
     ADD COLUMN signing_key_fingerprint text,
     ADD CONSTRAINT valid_signing_key_fingerprint CHECK (
         signing_key_fingerprint IS NULL
-        OR valid_fingerprint(signing_key_fingerprint));
+        OR valid_fingerprint(signing_key_fingerprint))
+        NOT VALID;
+
+-- 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.
+UPDATE pg_constraint SET convalidated=true
+FROM pg_class, pg_namespace
+WHERE
+    pg_class.oid = pg_constraint.conrelid
+    AND pg_namespace.oid = pg_class.relnamespace
+    AND pg_constraint.conname = 'valid_signing_key_fingerprint'
+    AND pg_namespace.nspname = 'public'
+    AND pg_class.relname IN (
+        'archive', 'packageupload', 'revision', 'signedcodeofconduct',
+        'sourcepackagerelease');
 
 INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 75, 0);