Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY

On 2025-Dec-06, Mihail Nikalayeu wrote:

> Such commands add an indisready, but not indisvalid index on pt, which
> is added to to the list of potential arbiters.
> It happens because of [0].
>
> From my perspective, the correct solution - is to just remove the
> error message, because it looks obsolete now. Or somehow calculate
> compensation offset differently - but I am not sure it is a good idea.

Hmm, as I recall it's quite intentional that the index on a partitioned
table is marked !indisvalid; such indexes are only supposed to be marked
valid once indexes on all partitions have been attached. As I recall,
if you remove that prohibition, some pg_dump scenarios fail.

I'd rather consider the idea of avoiding indexes marked !indisvalid on
partitioned tables as arbiter lists ... but then we need to verify the
scenario where there is one, and INSERT ON CONFLICT runs concurrently
with ALTER INDEX ATTACH PARTITION for the last partition lacking the
index (which is the point where the index is marked indisvalid on the
partitioned table). There may not be a problem with that, because we
grab AccessExclusiveLock on the index partition, so no query can be
running concurrently ... unless the INSERT is targeting a partition
other than the one where the index is being attached. (On the
partitioned table and index, we only have ShareUpdateExclusiveLock).

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Crear es tan difícil como ser libre" (Elsa Triolet)