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: Michael Paquier <michael@paquier.xyz>, Noah Misch <noah@leadboat.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2025-11-27T12:32:44Z
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

Hi,

On 2025-Nov-27, Mihail Nikalayeu wrote:

> On Wed, Nov 26, 2025 at 7:34 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> > We ran into one more problem with the new test, evidenced by timeouts by
> > buildfarm member prion.  For CATCACHE_FORCE_RELEASE builds on two of the
> > tests, we get a few invalidations of the catalog snapshot ahead of what
> > we expect, and because we have an injection point to sleep there, those
> > tests get stuck.
> 
> Oh, I missed that. Non-yet pushed tests are probably affected too.

Yeah, I suspect as much.

> > Here's one possible fix.
> 
> I have tried to move the setup of invalidate-catalog-snapshot-end to
> s1_start_upsert as the first command - but for some reason it wasn't
> working the way I expected. But maybe I missed something.

Right, this is why I said that this is one possible fix.  I mean, maybe
there are other ways to fix it.  I'm not sure it's the simplest or the
most robust, but I don't want to spend too much time looking for other
ways either.

> Solution seems reasonable to me, another related ideas:
> * replace "select case when" with function like
> injection_points_wakeup_if_waiting to avoid the possible race between
> select and wake up (but AFAIK it is not possible in the current case)
> * introduce some injection_points function to enter "ignore all runs,
> but still allowed to attach/detach" mode and "normal" mode.. As first
> command of setup - enter such "setup mode", as last - back to normal.

Ah, I had thought about the first one of these ideas, but not the second
one.  I noted both in the commit message, in case somebody is motivated
to implement them.

Thanks for reviewing.  I have pushed it now.  Looking at the next one.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)