REINDEX CONCURRENTLY causes ALTER TABLE to fail

Manuel Rigger <rigger.manuel@gmail.com>

From: Manuel Rigger <rigger.manuel@gmail.com>
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2019-07-17T12:53:54Z
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.



Commits

  1. Fix handling of expressions and predicates in REINDEX CONCURRENTLY