Merge lp:~cjwatson/launchpad/db-archive-dirty into lp:launchpad/db-devel

[Colin Watson]

Add Archive.dirty_suites column.

See https://code.launchpad.net/~cjwatson/launchpad/archive-api-dirty/+merge/310208 for rationale.

[William Grant (wgrant)]

This is going to be heavily, heavily contended, bloat Archive massively, and cause most copies to fail. It probably makes sense to instead have a non-unique ArchiveDirtySuite or similar table, which each dirtying operation adds to. The publisher records all the IDs that were there when it started, and deletes those when it completes.

review: Needs Fixing (db)

[Colin Watson (cjwatson)]

Why would it be heavily-contended or have anything to do with copies? This is used only for cases not already covered by pending publications and similar. It will be written to only when updating staged files in primary archives, when somebody calls Archive.markSuiteDirty by hand, or when the publisher deals with one of those requests and sets it back to NULL. I'd expect it to see on the order of a hundred writes a day, total.

Regarding bloat, it'll be NULL in most rows nearly all the time. I admit that's a little bit profligate, but that's only on the order of a hundred thousand NULLs for the whole database, which doesn't seem like a terribly big deal.

Given that, I don't think it's worth a separate table.

[William Grant (wgrant)]

Ah, so this is just for manual recovery -- the original intent of explicitly recording suite dirtiness was to explicitly record dirtiness for each operation that would dirty a suite, making publisher crashes automatically benign, and also reducing the performance penalty of uncondemned deletions to zero. This design is much less useful, but also easier and not terribly expensive to replace with the real thing later.

review: Approve (db)

[Colin Watson (cjwatson)]

I didn't know there was an original plan. :-) But indeed, easy to replace once the replacement has been fleshed out.

=== added file 'database/schema/patch-2209-71-1.sql'
--- database/schema/patch-2209-71-1.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-71-1.sql	2016-11-07 17:00:35 +0000
@@ -0,0 +1,10 @@
+-- Copyright 2016 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 ADD COLUMN dirty_suites text;
+
+COMMENT ON COLUMN Archive.dirty_suites IS 'A JSON-encoded list of suites in this archive that should be considered dirty on the next publisher run regardless of publications.';
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 71, 1);