REINDEX CONCURRENTLY causes ALTER TABLE to fail

From: Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: REINDEX CONCURRENTLY causes ALTER TABLE to fail
Date: 2019-07-17 12:53:54
Message-ID: CA+u7OA5Hp0ra235F3czPom_FyAd-3+XwSJmX95r1+sRPOJc9VQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi everyone,

Consider the following statement sequence:

CREATE TABLE t0(c0 INTEGER , c1 BOOLEAN);
INSERT INTO t0(c0, c1) VALUES(1369652450, FALSE), (414515746, TRUE),
(897778963, FALSE);
CREATE UNIQUE INDEX i0 ON t0((1 / t0.c0)) WHERE ('-H') >=
(t0.c1::TEXT) COLLATE "C";
REINDEX TABLE CONCURRENTLY t0;
ALTER TABLE t0 ALTER c1 TYPE TEXT; -- could not create unique index
"i0" DETAIL: Key ((1 / c0))=(0) is duplicated.

The REINDEX TABLE CONCURRENTLY causes the ALTER TABLE to fail, which
is unexpected. Without the CONCURRENTLY, the ALTER TABLE works as
expected. When first executing a concurrent reindex, which is followed
by a non-current one, the error still occurs, which might suggest that
the concurrent reindex breaks some internal state.

I tested this on trunk. On the latest release version, the
CONCURRENTLY option is not available.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mary Kellen Park 2019-07-17 14:54:03 Re: PLEASE HELP!
Previous Message Daniel Gustafsson 2019-07-17 07:57:56 Re: BUG #15909: Doc for `initdb` on `--data-checksums` says "cannot be changed later", but *can* be in Pg 12.