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

Hi everyone,

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. Instead, the ALTER TABLE command results in
the same error as the CREATE INDEX statement.

Other ALTER TABLE commands seem to be unaffected by the invalid index:

ALTER TABLE t0 SET WITHOUT OIDS; -- no error
ALTER TABLE t0 ALTER c0 SET NOT NULL; -- no error
ALTER TABLE t0 ALTER c0 SET DEFAULT 1; -- no error
ALTER TABLE t0 RENAME c0 TO c1; -- no error

As a workaround, the invalid INDEX can be dropped (which is a good
idea anyway, since it consumes space):

DROP INDEX i0;
ALTER TABLE t0 SET WITH OIDS; -- no error

I'm using the following Postgres version: psql (11.4 (Ubuntu
11.4-1.pgdg19.04+1)).

Best,
Manuel

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

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

On Sat, Jul 06, 2019 at 01:36:21AM +0200, Manuel Rigger wrote:
> Okay, thanks for the explanation and sorry for the false alarm!

Another thing which you may be interested in is that support for SET
WITH OIDS has been dropped as of v12.
--
Michael