Horton

Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY

Álvaro Herrera <alvherre@kurilemu.de>

View thread
From: Álvaro Herrera <alvherre@kurilemu.de>
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>, Michael Paquier <michael@paquier.xyz>, Noah Misch <noah@leadboat.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2025-12-07T21:07:45Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Replace flaky CIC/RI isolation tests with a TAP test

  2. Disable recently added CIC/RI isolation tests

  3. Fix infer_arbiter_index for partitioned tables

  4. Stabilize tests some more

  5. Put back alternative-output expected files

  6. Remove doc and code comments about ON CONFLICT deficiencies

  7. Avoid use of NOTICE to wait for snapshot invalidation

  8. Fix ON CONFLICT with REINDEX CONCURRENTLY and partitions

  9. Fix ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY

  10. Fix new test for CATCACHE_FORCE_RELEASE builds

  11. Improve test case stability

  12. Fix infer_arbiter_index during concurrent index operations

  13. Doc: cover index CONCURRENTLY causing errors in INSERT ... ON CONFLICT.

  14. Fix infer_arbiter_indexes() to not assume resultRelation is 1.

  15. Revert temporal primary keys and foreign keys

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)