Обсуждение: cannot CREATE INDEX because it has pending trigger events
Hi 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) DEFERRABLEINITIALLY 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; To explain the data model (the above data model is simplified from that of the application): a) We have a table called "resource", each row of which is some type of "resource" (there are more columns with other infoabout the resource, not included in above reproduce script) b) The types of resources are themselves resources, so resource_type has an FK to resource_id c) We make all the FKs deferred to simplify loading of data (so we can load the rows in any order) d) For certain types of resources, we want a singleton constraint – only one resource of given type is allowed to exist atany one time. That is what resource_type_2_singleton index is doing. (Actually, only one of the INSERT statements is necessary to trigger the issue; if you include just the first INSERT, youstill get the error; include no INSERTs, error doesn't happen) If I swap the order of the CREATE UNIQUE INDEX and the INSERT, so the index gets created first, the error doesn't happen. I don't understand why having a deferred FK to check should stop me from creating a unique index. I also don't understandwhy this worked in 9.6 but not anymore. Thank you Simon Kissane
On Tue, Aug 27, 2019 at 9:33 AM Simon Kissane <skissane@medallia.com> wrote: > If I swap the order of the CREATE UNIQUE INDEX and the INSERT, so the index gets created first, the error doesn't happen. It also works removing the INITIALLY DEFERRED from the foreign key, since it seems you are creating tuples in the right order (at least in this simplified model). The index is not created because the foreign key is validated at the transaction commit. > > I don't understand why having a deferred FK to check should stop me from creating a unique index. I also don't understandwhy this worked in 9.6 but not anymore. I've tested against 12beta2, it would be nice to understand what changed in (I suspect) SET TRANSACTION. Luca
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
On Tue, Aug 27, 2019 at 5:59 PM Laurenz Albe <laurenz.albe@cybertec.at> 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