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

[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.

[William Grant (wgrant)]

review: Approve (db)

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);