Re: reindex concurrently and two toast indexes

On Thu, Mar 05, 2020 at 05:57:07PM +0100, Julien Rouhaud wrote:
> I agree that the approach wasn't quite robust.  I'll try to look at adding a
> new command for isolationtester, but that's probably not something we want to
> put in pg13?

Yes, that's too late.

> Note that while looking at it, I noticed another bug in RIC:
>
> [...]
>
> # reindex table concurrently t1;
> WARNING:  0A000: cannot reindex invalid index "public.t1_val_idx_ccold" concurrently, skipping
> LOCATION:  ReindexRelationConcurrently, indexcmds.c:2821
> WARNING:  XX002: cannot reindex invalid index "pg_toast.pg_toast_16395_index_ccold" concurrently, skipping
> LOCATION:  ReindexRelationConcurrently, indexcmds.c:2867
> REINDEX
> # reindex index concurrently t1_val_idx_ccold;
> REINDEX
>
> That case is also fixed in this patch.

This choice is intentional.  The idea about bypassing invalid indexes
for table-level REINDEX is that this would lead to a bloat in the
number of relations to handling if multiple runs are failing, leading
to more and more invalid indexes to handle each time.  Allowing a
single invalid non-toast index to be reindexed with CONCURRENTLY can
be helpful in some cases, like for example a CIC for a unique index
that failed and was invalid, where the relation already defined can be
reused.
--
Michael