Re: cannot CREATE INDEX because it has pending trigger events

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