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

Manuel Rigger <rigger(dot)manuel(at)gmail(dot)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