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

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