Fix ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY

When REINDEX CONCURRENTLY is processing the index that supports a
constraint, there are periods during which multiple indexes match the
constraint index's definition.  Those must all be included in the set of
inferred index for INSERT ON CONFLICT, in order to avoid spurious
"duplicate key" errors.

To fix, we set things up to match all indexes against attributes,
expressions and predicates of the constraint index, then return all
indexes that match those, rather than just the one constraint index.
This is more onerous than before, where we would just test the named
constraint for validity, but it's not more onerous than processing
"conventional" inference (where a list of attribute names etc is given).

This is closely related to the misbehaviors fixed by bc32a12e0db2, for a
different situation.  We're not backpatching this one for now either,
for the same reasons.

Author: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CANtu0ojXmqjmEzp-=aJSxjsdE76iAsRgHBoK0QtYHimb_mEfsg@mail.gmail.com

4 files changed, 350 insertions, 0 deletions

diff --git a/src/test/modules/injection_points/Makefile b/src/test/modules/injection_points/Makefile
index 7b3c0c4b716..0a9716db27c 100644
--- a/src/test/modules/injection_points/Makefile
+++ b/src/test/modules/injection_points/Makefile
@@ -19,6 +19,7 @@ ISOLATION = basic \
 	    syscache-update-pruned \
 	    index-concurrently-upsert \
 	    reindex-concurrently-upsert \
+	    reindex-concurrently-upsert-on-constraint \
 	    index-concurrently-upsert-predicate
 
 TAP_TESTS = 1
diff --git a/src/test/modules/injection_points/expected/reindex-concurrently-upsert-on-constraint.out b/src/test/modules/injection_points/expected/reindex-concurrently-upsert-on-constraint.out
new file mode 100644
index 00000000000..c1ac1f77c61
--- /dev/null
+++ b/src/test/modules/injection_points/expected/reindex-concurrently-upsert-on-constraint.out
@@ -0,0 +1,238 @@
+Parsed test spec with 4 sessions
+
+starting permutation: s3_setup_wait_before_set_dead s3_start_reindex s1_start_upsert s4_wakeup_to_set_dead s2_start_upsert s4_wakeup_s1 s4_wakeup_s2
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_set_local
+--------------------------
+                          
+(1 row)
+
+step s3_setup_wait_before_set_dead: 
+	SELECT injection_points_attach('reindex-relation-concurrently-before-set-dead', 'wait');
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step s3_start_reindex: 
+	REINDEX INDEX CONCURRENTLY test.tbl_pkey;
+ <waiting ...>
+step s1_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s4_wakeup_to_set_dead: 
+	SELECT injection_points_detach('reindex-relation-concurrently-before-set-dead');
+	SELECT injection_points_wakeup('reindex-relation-concurrently-before-set-dead');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s2_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s4_wakeup_s1: 
+	SELECT injection_points_detach('check-exclusion-or-unique-constraint-no-conflict');
+	SELECT injection_points_wakeup('check-exclusion-or-unique-constraint-no-conflict');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s1_start_upsert: <... completed>
+step s4_wakeup_s2: 
+	SELECT injection_points_detach('exec-insert-before-insert-speculative');
+	SELECT injection_points_wakeup('exec-insert-before-insert-speculative');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s2_start_upsert: <... completed>
+step s3_start_reindex: <... completed>
+
+starting permutation: s3_setup_wait_before_swap s3_start_reindex s1_start_upsert s4_wakeup_to_swap s2_start_upsert s4_wakeup_s2 s4_wakeup_s1
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_set_local
+--------------------------
+                          
+(1 row)
+
+step s3_setup_wait_before_swap: 
+	SELECT injection_points_attach('reindex-relation-concurrently-before-swap', 'wait');
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step s3_start_reindex: 
+	REINDEX INDEX CONCURRENTLY test.tbl_pkey;
+ <waiting ...>
+step s1_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s4_wakeup_to_swap: 
+	SELECT injection_points_detach('reindex-relation-concurrently-before-swap');
+	SELECT injection_points_wakeup('reindex-relation-concurrently-before-swap');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s2_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s4_wakeup_s2: 
+	SELECT injection_points_detach('exec-insert-before-insert-speculative');
+	SELECT injection_points_wakeup('exec-insert-before-insert-speculative');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s4_wakeup_s1: 
+	SELECT injection_points_detach('check-exclusion-or-unique-constraint-no-conflict');
+	SELECT injection_points_wakeup('check-exclusion-or-unique-constraint-no-conflict');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s1_start_upsert: <... completed>
+step s2_start_upsert: <... completed>
+step s3_start_reindex: <... completed>
+
+starting permutation: s3_setup_wait_before_set_dead s3_start_reindex s1_start_upsert s2_start_upsert s4_wakeup_s1 s4_wakeup_to_set_dead s4_wakeup_s2
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+injection_points_set_local
+--------------------------
+                          
+(1 row)
+
+step s3_setup_wait_before_set_dead: 
+	SELECT injection_points_attach('reindex-relation-concurrently-before-set-dead', 'wait');
+
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step s3_start_reindex: 
+	REINDEX INDEX CONCURRENTLY test.tbl_pkey;
+ <waiting ...>
+step s1_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s2_start_upsert: 
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+ <waiting ...>
+step s4_wakeup_s1: 
+	SELECT injection_points_detach('check-exclusion-or-unique-constraint-no-conflict');
+	SELECT injection_points_wakeup('check-exclusion-or-unique-constraint-no-conflict');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s1_start_upsert: <... completed>
+step s4_wakeup_to_set_dead: 
+	SELECT injection_points_detach('reindex-relation-concurrently-before-set-dead');
+	SELECT injection_points_wakeup('reindex-relation-concurrently-before-set-dead');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s4_wakeup_s2: 
+	SELECT injection_points_detach('exec-insert-before-insert-speculative');
+	SELECT injection_points_wakeup('exec-insert-before-insert-speculative');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s2_start_upsert: <... completed>
+step s3_start_reindex: <... completed>
diff --git a/src/test/modules/injection_points/meson.build b/src/test/modules/injection_points/meson.build
index 485b483e3ca..3bbbc0bf7b3 100644
--- a/src/test/modules/injection_points/meson.build
+++ b/src/test/modules/injection_points/meson.build
@@ -50,6 +50,7 @@ tests += {
       'syscache-update-pruned',
       'index-concurrently-upsert',
       'reindex-concurrently-upsert',
+      'reindex-concurrently-upsert-on-constraint',
       'index-concurrently-upsert-predicate',
     ],
     'runningcheck': false, # see syscache-update-pruned
diff --git a/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec
new file mode 100644
index 00000000000..8126256460c
--- /dev/null
+++ b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec
@@ -0,0 +1,110 @@
+# Test race conditions involving:
+#
+# - s1: UPSERT a tuple
+# - s2: UPSERT the same tuple
+# - s3: concurrently REINDEX the primary key
+#
+# - s4: operations with injection points
+
+setup
+{
+	CREATE EXTENSION injection_points;
+	CREATE SCHEMA test;
+	CREATE UNLOGGED TABLE test.tbl(i int primary key, updated_at timestamp);
+	ALTER TABLE test.tbl SET (parallel_workers=0);
+}
+
+teardown
+{
+	DROP SCHEMA test CASCADE;
+	DROP EXTENSION injection_points;
+}
+
+session s1
+setup
+{
+	SELECT injection_points_set_local();
+	SELECT injection_points_attach('check-exclusion-or-unique-constraint-no-conflict', 'wait');
+}
+step s1_start_upsert
+{
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+}
+
+session s2
+setup
+{
+	SELECT injection_points_set_local();
+	SELECT injection_points_attach('exec-insert-before-insert-speculative', 'wait');
+}
+step s2_start_upsert
+{
+	INSERT INTO test.tbl VALUES (13, now()) ON CONFLICT ON CONSTRAINT tbl_pkey DO UPDATE SET updated_at = now();
+}
+
+session s3
+setup
+{
+	SELECT injection_points_set_local();
+}
+step s3_setup_wait_before_set_dead
+{
+	SELECT injection_points_attach('reindex-relation-concurrently-before-set-dead', 'wait');
+}
+step s3_setup_wait_before_swap
+{
+	SELECT injection_points_attach('reindex-relation-concurrently-before-swap', 'wait');
+}
+step s3_start_reindex
+{
+	REINDEX INDEX CONCURRENTLY test.tbl_pkey;
+}
+
+session s4
+step s4_wakeup_to_swap
+{
+	SELECT injection_points_detach('reindex-relation-concurrently-before-swap');
+	SELECT injection_points_wakeup('reindex-relation-concurrently-before-swap');
+}
+step s4_wakeup_s1
+{
+	SELECT injection_points_detach('check-exclusion-or-unique-constraint-no-conflict');
+	SELECT injection_points_wakeup('check-exclusion-or-unique-constraint-no-conflict');
+}
+step s4_wakeup_s2
+{
+	SELECT injection_points_detach('exec-insert-before-insert-speculative');
+	SELECT injection_points_wakeup('exec-insert-before-insert-speculative');
+}
+step s4_wakeup_to_set_dead
+{
+	SELECT injection_points_detach('reindex-relation-concurrently-before-set-dead');
+	SELECT injection_points_wakeup('reindex-relation-concurrently-before-set-dead');
+}
+
+permutation
+	s3_setup_wait_before_set_dead
+	s3_start_reindex(s1_start_upsert, s2_start_upsert)
+	s1_start_upsert
+	s4_wakeup_to_set_dead
+	s2_start_upsert(s1_start_upsert)
+	s4_wakeup_s1
+	s4_wakeup_s2
+
+permutation
+	s3_setup_wait_before_swap
+	s3_start_reindex(s1_start_upsert, s2_start_upsert)
+	s1_start_upsert
+	s4_wakeup_to_swap
+	s2_start_upsert(s1_start_upsert)
+	s4_wakeup_s2
+	s4_wakeup_s1
+
+permutation
+	s3_setup_wait_before_set_dead
+	s3_start_reindex(s1_start_upsert, s2_start_upsert)
+	s1_start_upsert
+	s2_start_upsert(s1_start_upsert)
+	s4_wakeup_s1
+	s4_wakeup_to_set_dead
+	s4_wakeup_s2