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
