Merge ~cjwatson/launchpad:db-archive-file-date-indexes into launchpad:db-devel

[Colin Watson]

Add ArchiveFile.{date_created,date_superseded} indexes

These allow efficient queries of the state of a path at a given time, and ensure that only one file holds a given path.

Following up on https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/373970 from long ago now that the associated code has finally landed.

[William Grant (wgrant)]

Should this be applied hot instead? Also a couple of other comments.

review: Needs Information (db)

[Colin Watson (cjwatson)]

Er yes, this should indeed be hot. I'll re-propose this against master.

Unmerged commits

38297b3... by Colin Watson on 2023-01-06

Add ArchiveFile.{date_created,date_superseded} indexes

These allow efficient queries of the state of a path at a given time,
and ensure that only one file holds a given path.

LP: #1765933

	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-15-0.sql b/database/schema/patch-2211-15-0.sql
new file mode 100644
index 0000000..730b566
--- /dev/null
+++ b/database/schema/patch-2211-15-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;
+
+-- Allow efficient queries of the state of a path at a given time.
+CREATE INDEX archivefile__archive__date_created__date_superseded__path__idx
+    ON ArchiveFile (archive, date_created, date_superseded, path)
+    WHERE date_created IS NOT NULL;
+
+-- Only one file may hold a given path at once.
+CREATE UNIQUE INDEX archivefile__archive__path__date_superseded__key
+    ON ArchiveFile (archive, path)
+    WHERE date_superseded IS NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 15, 0);