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-02T13:07:47Z
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-01, Mihail Nikalayeu wrote:

> From af5e27d4150dd53d313122c02da7ce4d3c07f332 Mon Sep 17 00:00:00 2001
> From: Mikhail Nikalayeu <mihailnikalayeu@gmail.com>
> Date: Sun, 30 Nov 2025 16:48:55 +0100
> Subject: [PATCH v16 1/3] Modify the ExecInitPartitionInfo function to consider
>   partitioned indexes that are potentially processed by REINDEX CONCURRENTLY
>  as  arbiters as well.
> 
> This is necessary to ensure that all concurrent transactions use the same set of arbiter indexes.

Thanks, pushed this one after some more editorialization.  I rewrote
most comments and renamed variables, but I also changed the code
somewhat.  For instance I made it use the ResultRelInfo's array of
indexes instead of doing RelationGetIndexList, because it seemed to
match better with the usage of the list index to match the indexes in
the array again later.  Maybe now would be a good time to dust off your
stress tests and verify that everything is still working as intended.

In doing these changes, I realized that there are several places in the
code (this one, but also others) that are using
get_partition_ancestors(), which I think might be a somewhat expensive
routine.  It obtains ancestors by recursing up the hierarchy, and at
each step does an indexscan on pg_inherits.  I bet this is not very nice
for performance.  I bet we can make this visible in a profile with an
inheritance hierarchy not terribly deep and a few hundred partitions.
We currently don't have a syscache on pg_inherits, as far as I
understand because back then we didn't think it was going to give us any
advantages, but maybe it would be useful for this and other operations.
If not a syscache, then maybe a different way to cache ancestors for a
relation, perhaps straight in the relcache entry.

Thanks for working on this,

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers