On Tue, 2019-08-27 at 12:00 +1000, Simon Kissane wrote: > We have an application that works fine with Postgres 9.6, but fails > with this error when we try installing it against 11.5 > > I simplified the problem down to the following reproduce script: > > BEGIN TRANSACTION; > CREATE TABLE resource (resource_id BIGINT NOT NULL PRIMARY KEY, > resource_type BIGINT NOT NULL); > ALTER TABLE ONLY resource ADD CONSTRAINT resource_type_fk FOREIGN KEY > (resource_type) REFERENCES resource (resource_id) DEFERRABLE > INITIALLY DEFERRED; > INSERT INTO resource (resource_id,resource_type) values (1,1); > INSERT INTO resource (resource_id,resource_type) values (2,1); > INSERT INTO resource (resource_id,resource_type) values (3,2); > CREATE UNIQUE INDEX IF NOT EXISTS resource_type_2_singleton ON > resource (resource_type) WHERE resource_type=2; > COMMIT; > > That script works fine in Postgres 9.6, but run it against 11.5 you > get the error: > > ERROR: cannot CREATE INDEX "resource" because it has pending trigger > events > STATEMENT: CREATE UNIQUE INDEX IF NOT EXISTS > resource_type_2_singleton ON resource (resource_type) WHERE > resource_type=2; This is fallout of commit 0d1885266630: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d1885266630eee1de5c43af463fe2b921451932 This commit is the fix for a bug: https://www.postgresql.org/message-id/flat/A737B7A37273E048B164557ADEF4A58B53A4DC9A%40ntex2010i.host.magwien.gv.at This might be a false positive hit or not, I am not certain. Maybe the check is not required for AFTER triggers. Anyway, the problem can be avoided by running SET CONSTRAINTS resource_type_fk IMMEDIATE; right before the CREATE INDEX, so I don't think it is a real problem. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
