Re: Index created in BEFORE trigger not updated during INSERT

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2017-05-24 08:26:24 -0400, Robert Haas wrote:
>> I'm willing to bet that nobody ever thought about that case very hard.
>> It seems like we should either make it work or prohibit it, but I
>> can't actually see quite how to do either off-hand.

> Hm, strategically sprinkled CheckTableNotInUse() might do the trick?

+1. We can't reasonably make it work: the outer query already has its
list of indexes that need to be inserted into. Also, if you try to
make the index via ALTER TABLE ADD CONSTRAINT in the trigger, that will
give you "cannot ALTER TABLE "mytable" because it is being used by active
queries in this session" because of the check in AlterTable().

It doesn't seem terribly hard to fix the CREATE INDEX case to behave
likewise, but I wonder how many other cases we've missed?

One small issue is that naive use of CheckTableNotInUse would produce an
error reading "cannot CREATE INDEX "mytable" because ...", which is not
exactly on point. It'd be better for it to say "cannot create an index on
"mytable" because ...". However, given that it took twenty years for
anybody to notice this, maybe it's close enough.

regards, tom lane