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