Search Postgresql Archives

Re: cannot CREATE INDEX because it has pending trigger events

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux