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-26T18:34:16Z
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

Attachments

Hello,

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.

Here's one possible fix.  I had to take the attach operation on
invalidate-catalog-snapshot-end to a new step of s1, instead of
occurring in the setup block.  I understand that this is because no step
can run until the setup of all steps completes, so if one setup gets
stuck, we're out of luck.  And then, session s4 can do a conditional
wakeup of session s1.

Patch attached.  Thoughts?

Maybe there's some other way to go about this -- for instance I
considered the idea of moving the injection point somewhere else from
InvalidateCatalogSnapshot().  I don't have any ideas about that though,
but I'm willing to listen if anybody has any.


The output in both cases is different (we get more notices in the weird
build and also s1 goes to sleep in different order), so I had to add an
alternative expected file.  The diffs look okay to me -- essentially
they say, in the normal case, the injection_points_attach() returns
immediately, but in the other case, s1 goes to sleep and is awakened
when it sees the 'case' expression by session 4:

--- expected/index-concurrently-upsert-predicate.out	2025-11-26 19:13:58.702213673 +0100
+++ expected/index-concurrently-upsert-predicate_1.out	2025-11-26 19:04:16.745666956 +0100
@@ -1,8 +1,9 @@
 Parsed test spec with 5 sessions
 
 starting permutation: s1_attach_invalidate_catalog_snapshot s4_wakeup_s1_setup s5_noop s3_start_create_index s1_start_upsert s4_wakeup_define_index_before_set_valid s2_start_upsert s5_wakeup_s1_from_invalidate_catalog_snapshot s4_wakeup_s2 s4_wakeup_s1
+s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
 injection_points_attach
 -----------------------
                        
 (1 row)
 
@@ -14,18 +15,15 @@
 injection_points_attach
 -----------------------
                        
 (1 row)
 
+s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
+s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
 step s1_attach_invalidate_catalog_snapshot: 
 	SELECT injection_points_attach('invalidate-catalog-snapshot-end', 'wait');
-
-injection_points_attach
------------------------
-                       
-(1 row)
-
+ <waiting ...>
 step s4_wakeup_s1_setup: 
 	select case when
 			(select pid from pg_stat_activity
 			      where wait_event_type = 'InjectionPoint' and
 			      wait_event = 'invalidate-catalog-snapshot-end') is not null
@@ -35,10 +33,16 @@
 case
 ----
     
 (1 row)
 
+step s1_attach_invalidate_catalog_snapshot: <... completed>
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
 step s5_noop: 
  <waiting ...>
 step s3_start_create_index: 
 	CREATE UNIQUE INDEX CONCURRENTLY tbl_pkey_special_duplicate ON test.tbl(abs(i)) WHERE i < 10000;
  <waiting ...>


-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Los minutos y los segundos son mercadería de la ciudad, donde un infeliz se
afana por no perder ni siquiera un segundo y no advierto que obrando de ese
modo pierde una vida."              ("La vuelta de Don Camilo", G. Guareschi)