CREATE UNIQUE INDEX structuralsubscription__product__subscriber__key
ON StructuralSubscription(product, subscriber) WHERE product IS NOT NULL;
CREATE UNIQUE INDEX structuralsubscription__project__subscriber__key
ON StructuralSubscription(project, subscriber) WHERE project IS NOT NULL;
-- We want to do this.
-- ALTER TABLE ONLY StructuralSubscription
-- ADD CONSTRAINT structuralsubscription__distribution__sourcepackagename__subscriber__unique
-- UNIQUE (distribution, sourcepackagename, subscriber);
-- However, we also want to do this, *if* the sourcepackagename is NULL.
-- ALTER TABLE ONLY StructuralSubscription
-- ADD CONSTRAINT structuralsubscription__distribution__subscriber__unique
-- UNIQUE (distribution, subscriber);
-- The second constraint will disallow sourcepackagename flexibility in the
-- first. Therefore, we use a unique index instead, as seen below.
CREATE UNIQUE INDEX
structuralsubscription__distribution__sourcepackagename__subscriber__key
ON StructuralSubscription(distribution, sourcepackagename, subscriber)
WHERE distribution IS NOT NULL AND sourcepackagename IS NOT NULL;
CREATE UNIQUE INDEX structuralsubscription__distribution__subscriber__key
ON StructuralSubscription(distribution, subscriber)
WHERE distribution IS NOT NULL AND sourcepackagename IS NULL;
CREATE UNIQUE INDEX structuralsubscription__distroseries__subscriber__key
ON StructuralSubscription(distroseries, subscriber)
WHERE distroseries IS NOT NULL;
-- NB. Currently we can't subscribe to a (distroseries, sourcepackagename)
-- so no need for the second partial distroseries index like the two
-- distribution indexes.
CREATE UNIQUE INDEX structuralsubscription__milestone__subscriber__key
ON StructuralSubscription(milestone, subscriber)
WHERE milestone IS NOT NULL;
CREATE UNIQUE INDEX structuralsubscription__productseries__subscriber__key
ON StructuralSubscription(productseries, subscriber)
WHERE productseries IS NOT NULL;
-- Drop obsolete indexes - the above constraints make them redundant.
DROP INDEX structuralsubscription__distribution__sourcepackagename__idx;
DROP INDEX structuralsubscription__distroseries__idx;
DROP INDEX structuralsubscription__milestone__idx;
DROP INDEX structuralsubscription__product__idx;
DROP INDEX structuralsubscription__productseries__idx;
DROP INDEX structuralsubscription__project__idx;
INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 65, 0);
The data migration looks fine, and should be fast enough with production data to run as part of the patch.
I've reworked the indexes below to minimize index size, and dropped redundant indexes. patch-2208-65-0.sql
-- CREATE CONSTRAINTS ------- ------- ------- ------- ------- ------- ------- ---
CREATE UNIQUE INDEX structuralsubsc ription_ _product_ _subscriber_ _key ription( product, subscriber) WHERE product IS NOT NULL;
ON StructuralSubsc
CREATE UNIQUE INDEX structuralsubsc ription_ _project_ _subscriber_ _key ription( project, subscriber) WHERE project IS NOT NULL;
ON StructuralSubsc
-- We want to do this. ription ription_ _distribution_ _sourcepackagen ame__subscriber __unique ription ription_ _distribution_ _subscriber_ _unique
-- ALTER TABLE ONLY StructuralSubsc
-- ADD CONSTRAINT structuralsubsc
-- UNIQUE (distribution, sourcepackagename, subscriber);
-- However, we also want to do this, *if* the sourcepackagename is NULL.
-- ALTER TABLE ONLY StructuralSubsc
-- ADD CONSTRAINT structuralsubsc
-- UNIQUE (distribution, subscriber);
-- The second constraint will disallow sourcepackagename flexibility in the
-- first. Therefore, we use a unique index instead, as seen below.
CREATE UNIQUE INDEX ubscription_ _distribution_ _sourcepackagen ame__subscriber __key ription( distribution, sourcepackagename, subscriber)
structurals
ON StructuralSubsc
WHERE distribution IS NOT NULL AND sourcepackagename IS NOT NULL;
CREATE UNIQUE INDEX structuralsubsc ription_ _distribution_ _subscriber_ _key ription( distribution, subscriber)
ON StructuralSubsc
WHERE distribution IS NOT NULL AND sourcepackagename IS NULL;
CREATE UNIQUE INDEX structuralsubsc ription_ _distroseries_ _subscriber_ _key ription( distroseries, subscriber)
ON StructuralSubsc
WHERE distroseries IS NOT NULL;
-- NB. Currently we can't subscribe to a (distroseries, sourcepackagename)
-- so no need for the second partial distroseries index like the two
-- distribution indexes.
CREATE UNIQUE INDEX structuralsubsc ription_ _milestone_ _subscriber_ _key ription( milestone, subscriber)
ON StructuralSubsc
WHERE milestone IS NOT NULL;
CREATE UNIQUE INDEX structuralsubsc ription_ _productseries_ _subscriber_ _key ription( productseries, subscriber)
ON StructuralSubsc
WHERE productseries IS NOT NULL;
-- Drop obsolete indexes - the above constraints make them redundant. ription_ _distribution_ _sourcepackagen ame__idx; ription_ _distroseries_ _idx; ription_ _milestone_ _idx; ription_ _product_ _idx; ription_ _productseries_ _idx; ription_ _project_ _idx;
DROP INDEX structuralsubsc
DROP INDEX structuralsubsc
DROP INDEX structuralsubsc
DROP INDEX structuralsubsc
DROP INDEX structuralsubsc
DROP INDEX structuralsubsc
INSERT INTO LaunchpadDataba seRevision VALUES (2208, 65, 0);