Merge lp:~cjwatson/launchpad/db-codeimport-git-url-per-target-rcs into lp:launchpad/db-devel

[Colin Watson]

Split the unique CodeImport(url) index into one for each target RCS type.

See https://code.launchpad.net/~cjwatson/launchpad/codeimport-git-url-per-target-rcs/+merge/310696 for rationale.

This can't be applied hot since it needs to drop an existing index.

[William Grant (wgrant)]

Why can't this be applied hot? DROP INDEX CONCURRENTLY is a thing -- we should just do it in the other order.

review: Approve (db)

[Colin Watson (cjwatson)]

I was going from the comment about deleting indexes on https://dev.launchpad.net/Database/LivePatching, but also, https://www.postgresql.org/docs/9.3/static/sql-dropindex.html says that you can't drop UNIQUE indexes with DROP INDEX CONCURRENTLY. However, testing on dogfood would appear to disagree, so sure, let's do it hot.

Switching the order seems like a good plan either way.

=== added file 'database/schema/patch-2209-80-1.sql'
--- database/schema/patch-2209-80-1.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-80-1.sql	2016-11-12 02:33:33 +0000
@@ -0,0 +1,12 @@
+-- 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;
+
+DROP INDEX codeimport__url__idx;
+CREATE UNIQUE INDEX codeimport__url__branch__idx
+    ON CodeImport(url) WHERE branch IS NOT NULL;
+CREATE UNIQUE INDEX codeimport__url__git_repository__idx
+    ON CodeImport(url) WHERE git_repository IS NOT NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 80, 1);