ALTER TABLE SET WITH OIDS fails after failed CONCURRENTLY index creation

From: Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: ALTER TABLE SET WITH OIDS fails after failed CONCURRENTLY index creation
Date: 2019-07-02 10:06:53
Message-ID: CA+u7OA4XYhc-qyCgJqwwgMGZDWAyeH821oa5oMzm_HEifZ4BeA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Manuel Rigger 2019-07-02 12:47:03 GROUP BY and inheritance issue
Previous Message David G. Johnston 2019-07-02 09:22:19 Re: BUG #15882: Select .... UNION ALL