From 8698b9777a55bbce9516b00c2558c584bada96ef Mon Sep 17 00:00:00 2001
From: Nitin Motiani <nitinmotiani@google.com>
Date: Tue, 7 Jul 2026 15:23:16 +0000
Subject: [PATCH v2] Fix propagation of indimmediate flag in index_create_copy

index_create_copy is used to create copy definitions of existing indexes.
Currently, it passes 0 as constr_flags to index_create, which results
in the copied index being created as immediate (indimmediate = true).
For deferrable unique constraints, this means the transient index used
during REINDEX CONCURRENTLY forces immediate constraint checks on
concurrent inserts, causing duplicate key violations.

To fix this without violating the contract of constr_flags (which should
only be used when creating constraints) and without relaxing the strict assertion
in index_create, we introduce a new index creation flag: INDEX_CREATE_IS_DEFERRABLE_COPY.

During index_create_copy, if the old index is deferrable, we pass
INDEX_CREATE_IS_DEFERRABLE_COPY in flags to index_create.

In index_create, we check this flag when determining the indimmediate
status of the new index, marking it as deferrable (indimmediate = false) if
either INDEX_CONSTR_CREATE_DEFERRABLE is in constr_flags or
INDEX_CREATE_IS_DEFERRABLE_COPY is in flags.

We also add an isolation test case to verify that REINDEX CONCURRENTLY
does not break concurrent transactions utilizing deferred uniqueness.
---
 src/backend/catalog/index.c                   | 11 +++-
 src/include/catalog/index.h                   |  3 +
 .../reindex-concurrently-deferred.out         | 48 ++++++++++++++
 src/test/isolation/isolation_schedule         |  1 +
 .../specs/reindex-concurrently-deferred.spec  | 65 +++++++++++++++++++
 5 files changed, 126 insertions(+), 2 deletions(-)
 create mode 100644 src/test/isolation/expected/reindex-concurrently-deferred.out
 create mode 100644 src/test/isolation/specs/reindex-concurrently-deferred.spec

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 31ef84d0a16..0c008a647d5 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -717,6 +717,8 @@ UpdateIndexRelation(Oid indexoid,
  *			create a partitioned index (table must be partitioned)
  *		INDEX_CREATE_SUPPRESS_PROGRESS:
  *			don't report progress during the index build.
+ *		INDEX_CREATE_IS_DEFERRABLE_COPY:
+ *			set indimmediate to false.
  *
  * constr_flags: flags passed to index_constraint_create
  *		(only if INDEX_CREATE_ADD_CONSTRAINT is set)
@@ -1051,7 +1053,8 @@ index_create(Relation heapRelation,
 						indexInfo,
 						collationIds, opclassIds, coloptions,
 						isprimary, is_exclusion,
-						(constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0,
+						(constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0 &&
+						(flags & INDEX_CREATE_IS_DEFERRABLE_COPY) == 0,
 						!concurrent && !invalid,
 						!concurrent);
 
@@ -1343,6 +1346,10 @@ index_create_copy(Relation heapRelation, uint16 flags,
 	indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldIndexId));
 	if (!HeapTupleIsValid(indexTuple))
 		elog(ERROR, "cache lookup failed for index %u", oldIndexId);
+
+	if (!((Form_pg_index) GETSTRUCT(indexTuple))->indimmediate)
+		flags |= INDEX_CREATE_IS_DEFERRABLE_COPY;
+
 	indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
 										   Anum_pg_index_indclass);
 	indclass = (oidvector *) DatumGetPointer(indclassDatum);
@@ -1477,7 +1484,7 @@ index_create_copy(Relation heapRelation, uint16 flags,
 							  stattargets,
 							  reloptionsDatum,
 							  flags,
-							  0,
+							  0,	/* constr_flags */
 							  true, /* allow table to be a system catalog? */
 							  false,	/* is_internal? */
 							  NULL);
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 9aee8226347..f29ae462f9a 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -72,6 +72,9 @@ extern void index_check_primary_key(Relation heapRel,
 #define	INDEX_CREATE_PARTITIONED			(1 << 5)
 #define INDEX_CREATE_INVALID				(1 << 6)
 #define INDEX_CREATE_SUPPRESS_PROGRESS		(1 << 7)
+#define INDEX_CREATE_IS_DEFERRABLE_COPY	(1 << 8)
+
+
 
 extern Oid	index_create(Relation heapRelation,
 						 const char *indexRelationName,
diff --git a/src/test/isolation/expected/reindex-concurrently-deferred.out b/src/test/isolation/expected/reindex-concurrently-deferred.out
new file mode 100644
index 00000000000..d8a9b54baa4
--- /dev/null
+++ b/src/test/isolation/expected/reindex-concurrently-deferred.out
@@ -0,0 +1,48 @@
+Parsed test spec with 4 sessions
+
+starting permutation: begin1 write1 reindex begin3 write3 commit1 wait_for_reindex begin4 check_catalog write4 write_dup4 resolve_dup4 commit4 commit3
+step begin1: BEGIN;
+step write1: UPDATE reind_deferred SET val = 1 WHERE id = 1;
+step reindex: REINDEX TABLE CONCURRENTLY reind_deferred; <waiting ...>
+step begin3: BEGIN;
+step write3: UPDATE reind_deferred SET val = 2 WHERE id = 2;
+step commit1: COMMIT;
+step wait_for_reindex: 
+    DO $$
+    DECLARE
+        s2_pid int;
+        s3_pid int;
+        blocked bool := false;
+    BEGIN
+        SELECT pid INTO s2_pid FROM pg_stat_activity WHERE application_name LIKE '%/s2';
+        SELECT pid INTO s3_pid FROM pg_stat_activity WHERE application_name LIKE '%/s3';
+        
+        FOR i IN 1..100 LOOP
+            SELECT pg_catalog.pg_isolation_test_session_is_blocked(s2_pid, ARRAY[s3_pid]) INTO blocked;
+            IF blocked THEN
+                RETURN;
+            END IF;
+            PERFORM pg_sleep(0.1);
+        END LOOP;
+        RAISE EXCEPTION 'reindex did not block on s3';
+    END;
+    $$;
+
+step begin4: BEGIN;
+step check_catalog: 
+    SELECT c.relname, i.indisunique, i.indimmediate, i.indisready, i.indisvalid
+    FROM pg_class c
+    JOIN pg_index i ON i.indexrelid = c.oid
+    WHERE c.relname LIKE '%ccnew';
+
+relname     |indisunique|indimmediate|indisready|indisvalid
+------------+-----------+------------+----------+----------
+uq_val_ccnew|t          |f           |t         |f         
+(1 row)
+
+step write4: INSERT INTO reind_deferred VALUES (3, 9);
+step write_dup4: INSERT INTO reind_deferred VALUES (4, 9);
+step resolve_dup4: UPDATE reind_deferred SET val = 10 WHERE id = 4;
+step commit4: COMMIT;
+step commit3: COMMIT;
+step reindex: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index b8ebe92553c..a4ac5c50e10 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -71,6 +71,7 @@ test: lock-committed-update
 test: lock-committed-keyupdate
 test: update-locked-tuple
 test: reindex-concurrently
+test: reindex-concurrently-deferred
 test: reindex-concurrently-toast
 test: reindex-schema
 test: propagate-lock-delete
diff --git a/src/test/isolation/specs/reindex-concurrently-deferred.spec b/src/test/isolation/specs/reindex-concurrently-deferred.spec
new file mode 100644
index 00000000000..f8e8207a539
--- /dev/null
+++ b/src/test/isolation/specs/reindex-concurrently-deferred.spec
@@ -0,0 +1,65 @@
+# REINDEX CONCURRENTLY with DEFERRED constraints
+#
+# Verify that concurrent writes that temporarily violate a deferred unique
+# constraint do not fail while REINDEX CONCURRENTLY is running.
+# We also check the catalog state of the temporary index.
+
+setup
+{
+    CREATE TABLE reind_deferred (id int, val int, CONSTRAINT uq_val UNIQUE(val) DEFERRABLE INITIALLY DEFERRED);
+    INSERT INTO reind_deferred VALUES (1, 1), (2, 2);
+}
+
+teardown
+{
+    DROP TABLE reind_deferred;
+}
+
+session s1
+step begin1 { BEGIN; }
+step write1 { UPDATE reind_deferred SET val = 1 WHERE id = 1; }
+step commit1 { COMMIT; }
+step wait_for_reindex {
+    DO $$
+    DECLARE
+        s2_pid int;
+        s3_pid int;
+        blocked bool := false;
+    BEGIN
+        SELECT pid INTO s2_pid FROM pg_stat_activity WHERE application_name LIKE '%/s2';
+        SELECT pid INTO s3_pid FROM pg_stat_activity WHERE application_name LIKE '%/s3';
+        
+        FOR i IN 1..100 LOOP
+            SELECT pg_catalog.pg_isolation_test_session_is_blocked(s2_pid, ARRAY[s3_pid]) INTO blocked;
+            IF blocked THEN
+                RETURN;
+            END IF;
+            PERFORM pg_sleep(0.1);
+        END LOOP;
+        RAISE EXCEPTION 'reindex did not block on s3';
+    END;
+    $$;
+}
+
+session s2
+step reindex { REINDEX TABLE CONCURRENTLY reind_deferred; }
+
+session s3
+step begin3 { BEGIN; }
+step write3 { UPDATE reind_deferred SET val = 2 WHERE id = 2; }
+step commit3 { COMMIT; }
+
+session s4
+step begin4 { BEGIN; }
+step check_catalog {
+    SELECT c.relname, i.indisunique, i.indimmediate, i.indisready, i.indisvalid
+    FROM pg_class c
+    JOIN pg_index i ON i.indexrelid = c.oid
+    WHERE c.relname LIKE '%ccnew';
+}
+step write4 { INSERT INTO reind_deferred VALUES (3, 9); }
+step write_dup4 { INSERT INTO reind_deferred VALUES (4, 9); }
+step resolve_dup4 { UPDATE reind_deferred SET val = 10 WHERE id = 4; }
+step commit4 { COMMIT; }
+
+permutation begin1 write1 reindex begin3 write3 commit1 wait_for_reindex begin4 check_catalog write4 write_dup4 resolve_dup4 commit4 commit3
-- 
2.55.0.229.g6434b31f56-goog

