Re: REINDEX CONCURRENTLY causes ALTER TABLE to fail

On Wed, Jul 17, 2019 at 02:53:54PM +0200, Manuel Rigger wrote:
> 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.

Thanks. The issue comes from the handling of the collation part
within the index predicates in pg_index.indpred. I can see a slight
difference in the tree with a switch from COLLATE to REFLABEL for
example.

In your test case, if you actually remove the collation part, this
fails similarly:
=# CREATE UNIQUE INDEX i0 ON t0((1 / t0.c0)) WHERE ('-H') >=
(t0.c1::TEXT);
ERROR: 23505: could not create unique index "i0"
DETAIL: Key ((1 / c0))=(0) is duplicated.

The root of the problem comes from the fact that we rely on
BuildIndexInfo() for the index information. This uses
RelationGetIndexExpressions() to fetch the set of expressions and
RelationGetIndexPredicate() for the predicates. However, the issue is
that those routines apply some extra flattening operations for the
planner when storing this data in the relation cache, causing the
expressions stored in the catalogs for the copy to be changed. So
what we store are the expressions that the planner uses. No wonder
that this gets broken.

There are a couple of approaches that we could do to fix that. The
first one I could think about is to change the relcache level so as we
don't apply planner-level optimizations when creating a concurrent
index entry. Another one, which is less invasive, is to just update
the list of expressions and predicates after calling
BuildIndexInfo(). Still that means overriding the contents of the
relcache with what has been optimized for the planner to what we want
to use for the reindex build and I think that this weakens the logic
of index_build.
--
Michael