> > > #4 0x00005618d76672d6 in ExceptionalCondition ( > > conditionName=conditionName@entry=0x5618d77741a8 "(constr_flags == 0) > > || ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)", > > fileName=fileName@entry=0x5618d7773e58 > > "../../pgsql/source/master/src/backend/catalog/index.c", > > lineNumber=lineNumber@entry=773) > > at ../../pgsql/source/master/src/backend/utils/error/assert.c:65 > > #5 0x00005618d7271fd5 in index_create > > (heapRelation=heapRelation@entry=0x7f13e9ce85d0, > > indexRelationName=indexRelationName@entry=0x5618fe816e58 > > "uq_val_ccnew", indexRelationId=indexRelationId@entry=0, > > parentIndexRelid=parentIndexRelid@entry=0, > > parentConstraintId=parentConstraintId@entry=0, > > relFileNumber=relFileNumber@entry=0, > > indexInfo=0x5618fe816aa8, indexColNames=0x5618fe6c8d40, > > accessMethodId=403, tableSpaceId=0, collationIds=0x5618fe73e2f8, > > opclassIds=0x7f13e9cead80, opclassOptions=0x5618fe6c8ea0, > > coloptions=0x7f13e9cead9c, stattargets=0x5618fe80d748, reloptions=0, > > flags=140, > > constr_flags=2, allow_system_table_mods=true, is_internal=false, > > constraintId=0x0) at > > ../../pgsql/source/master/src/backend/catalog/index.c:773 > > #6 0x00005618d727248d in index_create_copy > > (heapRelation=heapRelation@entry=0x7f13e9ce85d0, flags=flags@entry=140, > > oldIndexId=33725, > > tablespaceOid=0, newName=0x5618fe816e58 "uq_val_ccnew") at > > ../../pgsql/source/master/src/backend/catalog/index.c:1468 > > #7 0x00005618d72cbd46 in ReindexRelationConcurrently > > (stmt=stmt@entry=0x5618fe712228, relationOid=relationOid@entry=33722, > > params=params@entry=0x7ffcf8ab62f8) at > > ../../pgsql/source/master/src/backend/commands/indexcmds.c:4117 > > #8 0x00005618d72cd892 in ReindexTable (stmt=0x5618fe712228, > > params=0x7ffcf8ab62f8, isTopLevel=true) > > at ../../pgsql/source/master/src/backend/commands/indexcmds.c:3227 > > #9 ExecReindex (pstate=pstate@entry=0x5618fe6c8c30, > > stmt=stmt@entry=0x5618fe712228, isTopLevel=isTopLevel@entry=true) > > at ../../pgsql/source/master/src/backend/commands/indexcmds.c:3042 > > > > > > This is during the > > REINDEX TABLE CONCURRENTLY reind_deferred; > > execution. > > >
I missed this as I had not tried with asserts enabled. I considered the following approaches to fix this : 1. By changing the check to allow constr_flag & INDEX_CONSTR_CREATE_DEFERRABLE to be non-zero even if INDEX_CREATE_ADD_CONSTRAINT was not set. This seemed unsafe. 2. Adding a new flag to the function to specially allow INDEX_CONSTR_CREATE_DEFERRABLE for the case when the index was being copied. 3. Adding a new flag but using that to determine whether to set indimmediate to true or not. This way constr_flags still follows the contract that it will only be used when a constraint is being created. I ended up picking the 3rd approach and created a new index creation flag INDEX_CREATE_IS_DEFERRABLE_COPY. Now indimmediate is set if either INDEX_CONSTR_CREATE_DEFERRABLE is set in constr_flags or INDEX_CREATE_IS_DEFERRABLE_COPY is set in flags. This looked a little extra code but I think it's cleaner as it allows constr_flags to be only used when a new constraint is created. And a reader can see that there are two possible reasons for setting indimmediate to false. I'm attaching the v2 patch with this change. Please let me know what you think. Thanks Nitin Motiani Google
From 8698b9777a55bbce9516b00c2558c584bada96ef Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> 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
