Re: ALTER TABLE SET WITH OIDS fails after failed CONCURRENTLY index creation

Okay, thanks for the explanation and sorry for the false alarm!

Best,
Manuel

On Sat, Jul 6, 2019 at 12:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.com> writes:
> > Consider the following example:
>
> > CREATE TABLE t0(c0 INT);
> > INSERT INTO t0(c0) VALUES(0), (0);
> > CREATE UNIQUE INDEX CONCURRENTLY i0 ON t0(c0);
> > ALTER TABLE t0 SET WITH OIDS; -- expected: no error, actual: ERROR:
> > could not create unique index "i0" DETAIL:  Key (c0)=(0) is
> > duplicated.
>
> > The concurrent creation of the UNIQUE INDEX fails, which is expected.
> > However, I would expect that the index is then ignored and that the
> > ALTER TABLE is unaffected.
>
> I'm afraid your expectations are too high.  This isn't a bug, although the
> documentation for CREATE INDEX CONCURRENTLY perhaps needs to call out the
> hazard more explicitly.  The docs already say
>
>    Another caveat when building a unique index concurrently is that the
>    uniqueness constraint is already being enforced against other
>    transactions when the second table scan begins. This means that
>    constraint violations could be reported in other queries prior to the
>    index becoming available for use, or even in cases where the index
>    build eventually fails. Also, if a failure does occur in the second
>    scan, the “invalid” index continues to enforce its uniqueness
>    constraint afterwards.
>
> Basically, once an invalid index is in place, it's going to cause you
> problems until you drop it or make it valid.  This is the price of
> not taking any exclusive locks in CREATE INDEX CONCURRENTLY: we don't
> really have the option to clean up nicely after failure, because
> that'd require an exclusive table lock :-(
>
>                         regards, tom lane