Merge ~cjwatson/launchpad:db-artifactory-publishing-columns-not-null into launchpad:db-devel

[Colin Watson]

Make Artifactory publishing columns non-nullable

The backfill jobs have all finished everywhere now.

[William Grant (wgrant)]

Neither SPPH.format nor BPPH.binarypackageformat are indexed, so this will likely heavily lock the tables for Some Time.

I also forget if `ALTER COLUMN ... SET NOT NULL` will use an index at all.
We have catalog hacked for this sort of thing in the past, but ideally we'd avoid it again.

review: Needs Information (db)

[Colin Watson (cjwatson)]

It is apparently impossible to avoid the long lock and sequential scan in PostgreSQL < 12 without catalog hacking, although as of 12 it can be done using check constraints (not indexes):

  https://www.postgresql.org/message-id/1034971590734717%40mail.yandex.ru

Unmerged commits

3e854fa... by Colin Watson on 2023-01-31

Make Artifactory publishing columns non-nullable

The backfill jobs have all finished everywhere now.

	docs:0 (build)
	lint:0 (build)
	mypy:0 (build)

1 → 3 of 3 results

First • Previous • Next • Last

diff --git a/database/schema/patch-2211-08-0.sql b/database/schema/patch-2211-08-0.sql
new file mode 100644
index 0000000..6fc3e2f
--- /dev/null
+++ b/database/schema/patch-2211-08-0.sql
@@ -0,0 +1,16 @@
+-- Copyright 2023 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE Archive
+    ALTER COLUMN publishing_method SET NOT NULL,
+    ALTER COLUMN repository_format SET NOT NULL;
+
+ALTER TABLE SourcePackagePublishingHistory
+    ALTER COLUMN format SET NOT NULL;
+
+ALTER TABLE BinaryPackagePublishingHistory
+    ALTER COLUMN binarypackageformat SET NOT NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 08, 0);