On Tue, Aug 27, 2019 at 5:59 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > > 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. In that bug, an index is being created in a trigger. I can certainly see how that might lead to index corruption. But, an FK constraint trigger (assuming ON UPDATE NO ACTION / ON DELETE NO ACTION), is not making any data change, so there is no way it could possibly corrupt an index. So it seems that in order to prevent the bug, it is also banning scenarios which have no possibility of triggering it. One check might be to see if the function/procedure of the trigger in question is defined STABLE (which means it can't modify any table data or schema). (I don't know if the auto-generated FK constraint triggers would be marked as STABLE or not, but, if they are NO ACTION they could be.) If the trigger is STABLE, then index corruption would be impossible. > 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. In the real app, there are a lot more than just one FK, I removed the rest in my reproduce script. But, you are right, I could probably then do something like SET CONSTRAINTS ALL IMMEDIATE; I think there is a real problem in that code that used to work fine stops working on upgrade. Ideally, either it should still work, or if for some reason it is impossible, at least the documentation should explain that. Thanks Simon
