Horton

Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY

Mikhail Nikalayeu <mihailnikalayeu@gmail.com>

View thread
From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
To: Álvaro Herrera <alvherre@kurilemu.de>
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-07T22:18:22Z
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

Hello!

On Sun, Dec 7, 2025 at 10:07 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> 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).

For my taste it feels too complicated for such a case.

What is about changing the logic of this check to the next:
For each valid index used as arbiter in a partitioned table we need to
have a valid in particular partition (but it is okay to also have an
"ready"-only as additional).

If some of the arbiters is invalid in the partitioned table (but we
have valid compatible in any case) - it is okay. We just have to have
an appropriate "companion" for every valid arbiter.

Such a check looks correct to me, at least at the very end of the weekend.

Thanks,
Mikhail.