Hi, It was reported in [1] that Reindex Concurrently for indexes with deferrable constraints doesn't honour the deferrable constraints while the operations is running. And the transactions can fail because the constraint is enforced before commit.
I'm proposing a patch here which correctly copies the flag from the old index to the new one. Please take a look. [1] https://www.postgresql.org/message-id/flat/cc0470801d4ee46bd85f94c2516fd31b%40nimrod.no Regards & Thanks, Nitin Motiani Google
From 9159afc2e91f417fd82aeb8134f8a25cd077bfa3 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> Date: Tue, 7 Jul 2026 15:23:16 +0000 Subject: [PATCH v1] 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. This fix reads the indimmediate flag from the old index and sets the INDEX_CONSTR_CREATE_DEFERRABLE flag in constr_flags if the old index was deferrable. 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 | 7 +- .../reindex-concurrently-deferred.out | 48 ++++++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/reindex-concurrently-deferred.spec | 65 +++++++++++++++++++ 4 files changed, 120 insertions(+), 1 deletion(-) 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..e42f3120e19 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1324,6 +1324,7 @@ index_create_copy(Relation heapRelation, uint16 flags, List *indexColNames = NIL; List *indexExprs = NIL; List *indexPreds = NIL; + uint16 constr_flags = 0; indexRelation = index_open(oldIndexId, RowExclusiveLock); @@ -1343,6 +1344,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) + constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE; + indclassDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple, Anum_pg_index_indclass); indclass = (oidvector *) DatumGetPointer(indclassDatum); @@ -1477,7 +1482,7 @@ index_create_copy(Relation heapRelation, uint16 flags, stattargets, reloptionsDatum, flags, - 0, + constr_flags, true, /* allow table to be a system catalog? */ false, /* is_internal? */ NULL); 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
