Re: cannot CREATE INDEX because it has pending trigger events

On Tue, Aug 27, 2019 at 5:59 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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