pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
8+ messages / 5 participants
[nested] [flat]

* BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
@ 2026-10-30 06:40 PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: PG Bug reporting form @ 2026-10-30 06:40 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19817
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux (TencentOS 4.4, x86_64)
Description:        

Hello,

When CREATE UNIQUE INDEX is run on a partitioned table, DefineIndex()
recurses to each partition and reuses an already-existing index it considers
"equivalent" (src/backend/commands/indexcmds.c, the recursion loop around
lines 1450-1495).  That equivalence check only compares the index
definition;
it does not check indisvalid or indisready.  As a result, an index left
invalid (and not ready) by a previously failed CREATE INDEX CONCURRENTLY is
silently attached as a child index, the parent index is created with
indisvalid=false, and the whole chain reports success -- while in fact the
UNIQUE constraint is not enforced at all.

Steps to reproduce (self-contained, no data needed):

    -- a partition with a leftover invalid index from a failed CIC
    CREATE TABLE b2_root (a int, b text) PARTITION BY RANGE (a);
    CREATE TABLE b2_p1 PARTITION OF b2_root FOR VALUES FROM (0) TO (1000);

    INSERT INTO b2_p1 VALUES (1,'x'), (1,'dup');           -- duplicate key
    CREATE UNIQUE INDEX CONCURRENTLY b2_p1_bad ON b2_p1 (a); -- fails,
leaves
    -- b2_p1_bad behind with indisvalid=f, indisready=f
    DELETE FROM b2_p1 WHERE b = 'dup';                       -- table is
clean now

    -- the silent failure:
    CREATE UNIQUE INDEX b2_root_ui ON b2_root (a);           -- returns
    -- CREATE INDEX, no warning

    -- state after: the invalid leftover is attached as a child index
    SELECT c.relname, i.indisvalid, i.indisready
      FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
     WHERE c.relname IN ('b2_p1_bad','b2_root_ui');
    --  b2_p1_bad   | f | f     (parent: b2_root_ui)
    --  b2_root_ui  | f | t

    -- consequence a: uniqueness is not enforced, duplicates are accepted
    INSERT INTO b2_p1 VALUES (1,'x'), (1,'x');               -- succeeds

    -- consequence b: ON CONFLICT can no longer be used on this table
    INSERT INTO b2_p1 VALUES (1,'x') ON CONFLICT (a) DO NOTHING;
    -- ERROR: there is no unique or exclusion constraint matching
    --        the ON CONFLICT specification

Expected: the recursion loop should skip (or hard-fail on) child indexes
that are not indisvalid/indisready, at minimum emit a WARNING.  For
comparison, the ATTACH path already does a full check:
AttachPartitionEnsureIndexes() (src/backend/commands/tablecmds.c) refuses
in that situation, and the project has recently applied the same standard
to REPACK ("Fail REPACK in presence of invalid indexes", commits 0c5d6269614
and 78142821dfd).

Actual: CREATE UNIQUE INDEX reports success and silently attaches the
invalid leftover index; the unique constraint enforces nothing; no warning
of any kind is emitted.

Reproduced on:
  - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu)
  - PostgreSQL 18.6 (REL_18_STABLE, self-built)

The behavior looks the same on both branches, so this appears to be
long-standing rather than a recent regression.

A draft fix location would be the recursion loop in DefineIndex()
(src/backend/commands/indexcmds.c:1450-1495): before reusing an existing
partition index, require indisvalid && indisready, mirroring
AttachPartitionEnsureIndexes().

Regards,
Chunling Qin








^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-01 06:23 ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dilip Kumar @ 2026-11-01 06:23 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19817
> Logged by:          chunling qin
> Email address:      303677365@qq.com
> PostgreSQL version: 18.6
> Operating system:   Linux (TencentOS 4.4, x86_64)
> Description:
>
> Hello,
>
> When CREATE UNIQUE INDEX is run on a partitioned table, DefineIndex()
> recurses to each partition and reuses an already-existing index it considers
> "equivalent" (src/backend/commands/indexcmds.c, the recursion loop around
> lines 1450-1495).  That equivalence check only compares the index
> definition;
> it does not check indisvalid or indisready.  As a result, an index left
> invalid (and not ready) by a previously failed CREATE INDEX CONCURRENTLY is
> silently attached as a child index, the parent index is created with
> indisvalid=false, and the whole chain reports success -- while in fact the
> UNIQUE constraint is not enforced at all.
>
> Steps to reproduce (self-contained, no data needed):
>
>     -- a partition with a leftover invalid index from a failed CIC
>     CREATE TABLE b2_root (a int, b text) PARTITION BY RANGE (a);
>     CREATE TABLE b2_p1 PARTITION OF b2_root FOR VALUES FROM (0) TO (1000);
>
>     INSERT INTO b2_p1 VALUES (1,'x'), (1,'dup');           -- duplicate key
>     CREATE UNIQUE INDEX CONCURRENTLY b2_p1_bad ON b2_p1 (a); -- fails,
> leaves
>     -- b2_p1_bad behind with indisvalid=f, indisready=f
>     DELETE FROM b2_p1 WHERE b = 'dup';                       -- table is
> clean now
>
>     -- the silent failure:
>     CREATE UNIQUE INDEX b2_root_ui ON b2_root (a);           -- returns
>     -- CREATE INDEX, no warning
>
>     -- state after: the invalid leftover is attached as a child index
>     SELECT c.relname, i.indisvalid, i.indisready
>       FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
>      WHERE c.relname IN ('b2_p1_bad','b2_root_ui');
>     --  b2_p1_bad   | f | f     (parent: b2_root_ui)
>     --  b2_root_ui  | f | t
>
>     -- consequence a: uniqueness is not enforced, duplicates are accepted
>     INSERT INTO b2_p1 VALUES (1,'x'), (1,'x');               -- succeeds
>
>     -- consequence b: ON CONFLICT can no longer be used on this table
>     INSERT INTO b2_p1 VALUES (1,'x') ON CONFLICT (a) DO NOTHING;
>     -- ERROR: there is no unique or exclusion constraint matching
>     --        the ON CONFLICT specification
>
> Expected: the recursion loop should skip (or hard-fail on) child indexes
> that are not indisvalid/indisready, at minimum emit a WARNING.  For
> comparison, the ATTACH path already does a full check:
> AttachPartitionEnsureIndexes() (src/backend/commands/tablecmds.c) refuses
> in that situation, and the project has recently applied the same standard
> to REPACK ("Fail REPACK in presence of invalid indexes", commits 0c5d6269614
> and 78142821dfd).
>
> Actual: CREATE UNIQUE INDEX reports success and silently attaches the
> invalid leftover index; the unique constraint enforces nothing; no warning
> of any kind is emitted.
>
> Reproduced on:
>   - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu)
>   - PostgreSQL 18.6 (REL_18_STABLE, self-built)
>
> The behavior looks the same on both branches, so this appears to be
> long-standing rather than a recent regression.
>
> A draft fix location would be the recursion loop in DefineIndex()
> (src/backend/commands/indexcmds.c:1450-1495): before reusing an existing
> partition index, require indisvalid && indisready, mirroring
> AttachPartitionEnsureIndexes().

Thanks for the report. I agree that the DefineIndex() should not reuse
invalid indexes on partitions when creating an index on a partitioned
table, consistent with how AttachPartitionEnsureIndexes() already
skips !indisvalid indexes during ALTER TABLE ... ATTACH PARTITION
(commit e90e9275f56). Please find attached a patch that skips invalid
partition indexes in DefineIndex() and removes the now-unnecessary
parent invalidation logic.

-- 
Regards,
Dilip Kumar
Google

Attachments:

v1-0001-Ignore-invalid-indexes-on-partitions-during-CREAT.patch (application/octet-stream, 18.8K) download From 09d3b18c5f6aa8c44276f2e9f18e0af19bae5c97 Mon Sep 17 00:00:00 2001 From: Dilip Kumar <dilipkumarb@google.com> Date: Sun, 1 Nov 2026 06:11:09 +0000 Subject: [PATCH v1] Ignore invalid indexes on partitions during CREATE INDEX When DefineIndex() creates an index on a partitioned table (without ONLY), it recurses to each partition and scans existing indexes on the partition to see if a matching, unattached index can be reused and attached to the newly created parent index. Previously, this check only compared the structural index definition via CompareIndexInfo() without checking pg_index.indisvalid. As a result, an invalid index left behind on a partition by a previously failed CREATE [UNIQUE] INDEX CONCURRENTLY (with indisvalid = false and possibly indisready = false), or an incomplete partitioned index on a sub-partitioned table created with CREATE [UNIQUE] INDEX ON ONLY, would be attached as a child index, marking the parent partitioned index indisvalid = false and skipping index creation on the partition (or its sub-partitions). In particular, for CREATE UNIQUE INDEX (or ALTER TABLE ADD UNIQUE / PRIMARY KEY), the command would report success even when the partition contained duplicate values, and when indisready = false on the reused leaf index, subsequent inserts and updates would not enforce the unique constraint either (since ExecInsertIndexTuples() skips !indisready indexes). Furthermore, once attached to the parent index, the broken child index could no longer be dropped directly with DROP INDEX due to its partition dependency. Commit e90e9275f56 fixed the analogous issue in AttachPartitionEnsureIndexes() for ALTER TABLE ... ATTACH PARTITION by skipping invalid indexes on the partition being attached. Apply the same rule in DefineIndex(): skip existing partition indexes where !indisvalid, so that a new valid index is built on the partition instead. Reported-by: chunling qin Authored-by: Dilip Kumar <dilipbalaut@gmail.com> --- doc/src/sgml/ref/create_index.sgml | 6 +- src/backend/commands/indexcmds.c | 76 ++++++---------------- src/test/regress/expected/indexing.out | 89 +++++++++++++++++--------- src/test/regress/sql/indexing.sql | 34 +++++++--- 4 files changed, 104 insertions(+), 101 deletions(-) diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bb7505d171b..1aca4add5dc 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -752,14 +752,14 @@ Indexes: When <literal>CREATE INDEX</literal> is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. - Each partition is first checked to determine whether an equivalent + Each partition is first checked to determine whether a valid equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. - If no matching index exists, a new index will be created and + If no matching valid index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the - command. + command. Invalid indexes on the partitions are skipped. If the <literal>ONLY</literal> option is specified, no recursion is done, and the index is marked invalid. (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 5a0312fe772..7dec7f66c1e 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1339,7 +1339,6 @@ DefineIndex(ParseState *pstate, { int nparts = partdesc->nparts; Oid *part_oids = palloc_array(Oid, nparts); - bool invalidate_parent = false; Relation parentIndex; TupleDesc parentDesc; @@ -1391,9 +1390,9 @@ DefineIndex(ParseState *pstate, parentDesc = RelationGetDescr(rel); /* - * For each partition, scan all existing indexes; if one matches - * our index definition and is not already attached to some other - * parent index, attach it to the one we just created. + * For each partition, scan all existing indexes; if one is valid, + * matches our index definition, and is not already attached to + * some other parent index, attach it to the one we just created. * * If none matches, build a new index by calling ourselves * recursively with the same options (except for the index name). @@ -1458,6 +1457,14 @@ DefineIndex(ParseState *pstate, continue; cldidx = index_open(cldidxid, lockmode); + + /* If this index is invalid, can't use it */ + if (!cldidx->rd_index->indisvalid) + { + index_close(cldidx, lockmode); + continue; + } + cldIdxInfo = BuildIndexInfo(cldidx); if (CompareIndexInfo(cldIdxInfo, indexInfo, cldidx->rd_indcollation, @@ -1496,9 +1503,6 @@ DefineIndex(ParseState *pstate, createdConstraintId, childRelid); - if (!cldidx->rd_index->indisvalid) - invalidate_parent = true; - found = true; /* @@ -1532,7 +1536,6 @@ DefineIndex(ParseState *pstate, if (!found) { IndexStmt *childStmt; - ObjectAddress childAddr; /* * Build an IndexStmt describing the desired child index @@ -1553,62 +1556,23 @@ DefineIndex(ParseState *pstate, Assert(GetUserId() == child_save_userid); SetUserIdAndSecContext(root_save_userid, root_save_sec_context); - childAddr = - DefineIndex(NULL, /* original pstate not applicable */ - childRelid, childStmt, - InvalidOid, /* no predefined OID */ - indexRelationId, /* this is our child */ - createdConstraintId, - -1, - is_alter_table, check_rights, - check_not_in_use, - skip_build, quiet); + DefineIndex(NULL, /* original pstate not applicable */ + childRelid, childStmt, + InvalidOid, /* no predefined OID */ + indexRelationId, /* this is our child */ + createdConstraintId, + -1, + is_alter_table, check_rights, + check_not_in_use, + skip_build, quiet); SetUserIdAndSecContext(child_save_userid, child_save_sec_context); - - /* - * Check if the index just created is valid or not, as it - * could be possible that it has been switched as invalid - * when recursing across multiple partition levels. - */ - if (!get_index_isvalid(childAddr.objectId)) - invalidate_parent = true; } free_attrmap(attmap); } index_close(parentIndex, lockmode); - - /* - * The pg_index row we inserted for this index was marked - * indisvalid=true. But if we attached an existing index that is - * invalid, this is incorrect, so update our row to invalid too. - */ - if (invalidate_parent) - { - Relation pg_index = table_open(IndexRelationId, RowExclusiveLock); - HeapTuple tup, - newtup; - - tup = SearchSysCache1(INDEXRELID, - ObjectIdGetDatum(indexRelationId)); - if (!HeapTupleIsValid(tup)) - elog(ERROR, "cache lookup failed for index %u", - indexRelationId); - newtup = heap_copytuple(tup); - ((Form_pg_index) GETSTRUCT(newtup))->indisvalid = false; - CatalogTupleUpdate(pg_index, &tup->t_self, newtup); - ReleaseSysCache(tup); - table_close(pg_index, RowExclusiveLock); - heap_freetuple(newtup); - - /* - * CCI here to make this update visible, in case this recurses - * across multiple partition levels. - */ - CommandCounterIncrement(); - } } /* diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 4a0a652e9f6..0728281bb52 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -292,18 +292,8 @@ create table idxpart21 partition of idxpart2 for values from (100) to (200); create table idxpart22 partition of idxpart2 for values from (200) to (300); create index on idxpart22 (a); create index on only idxpart2 (a); -create index on idxpart (a); --- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21 --- does not; also, idxpart22 is not attached. -\d idxpart1 - Table "public.idxpart1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | -Partition of: idxpart FOR VALUES FROM (0) TO (100) -Indexes: - "idxpart1_a_idx" btree (a) - +-- Here we expect that idxpart2 has a new index, but idxpart21 does not; +-- also, idxpart22 is not attached. \d idxpart2 Partitioned table "public.idxpart2" Column | Type | Collation | Nullable | Default @@ -326,13 +316,11 @@ select indexrelid::regclass, indrelid::regclass, inhparent::regclass from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) where indexrelid::regclass::text like 'idxpart%' order by indexrelid::regclass::text collate "C"; - indexrelid | indrelid | inhparent ------------------+-----------+--------------- - idxpart1_a_idx | idxpart1 | idxpart_a_idx + indexrelid | indrelid | inhparent +-----------------+-----------+----------- idxpart22_a_idx | idxpart22 | - idxpart2_a_idx | idxpart2 | idxpart_a_idx - idxpart_a_idx | idxpart | -(4 rows) + idxpart2_a_idx | idxpart2 | +(2 rows) alter index idxpart2_a_idx attach partition idxpart22_a_idx; select indexrelid::regclass, indrelid::regclass, inhparent::regclass @@ -341,11 +329,9 @@ where indexrelid::regclass::text like 'idxpart%' order by indexrelid::regclass::text collate "C"; indexrelid | indrelid | inhparent -----------------+-----------+---------------- - idxpart1_a_idx | idxpart1 | idxpart_a_idx idxpart22_a_idx | idxpart22 | idxpart2_a_idx - idxpart2_a_idx | idxpart2 | idxpart_a_idx - idxpart_a_idx | idxpart | -(4 rows) + idxpart2_a_idx | idxpart2 | +(2 rows) -- attaching idxpart22 is not enough to set idxpart22_a_idx valid ... alter index idxpart2_a_idx attach partition idxpart22_a_idx; @@ -374,6 +360,20 @@ Indexes: "idxpart2_a_idx" btree (a) Number of partitions: 2 (Use \d+ to list them.) +create index on idxpart (a); +select indexrelid::regclass, indrelid::regclass, inhparent::regclass + from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) +where indexrelid::regclass::text like 'idxpart%' + order by indexrelid::regclass::text collate "C"; + indexrelid | indrelid | inhparent +-----------------+-----------+---------------- + idxpart1_a_idx | idxpart1 | idxpart_a_idx + idxpart21_a_idx | idxpart21 | idxpart2_a_idx + idxpart22_a_idx | idxpart22 | idxpart2_a_idx + idxpart2_a_idx | idxpart2 | idxpart_a_idx + idxpart_a_idx | idxpart | +(5 rows) + drop table idxpart; -- When a table is attached a partition and it already has an index, a -- duplicate index should not get created, but rather the index becomes @@ -1661,9 +1661,8 @@ select indexrelid::regclass, indisvalid, (5 rows) drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. +-- Check that invalid indexes are not selected when creating an index on a +-- partitioned table. create table parted_isvalid_tab (a int, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); @@ -1673,13 +1672,32 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 for values from (1) to (5); create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); --- create an invalid index on one of the partitions. +-- create an invalid expression index on one of the partitions. insert into parted_isvalid_tab_11 values (1, 0); create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); ERROR: division by zero --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the same offending row. create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +ERROR: division by zero +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. +create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +-- Likewise for a failed concurrent unique index build on a partition: +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_uniq_11 on parted_isvalid_tab_11 (a); +ERROR: could not create unique index "parted_isvalid_uniq_11" +DETAIL: Key (a)=(1) is duplicated. +create unique index parted_isvalid_uniq on parted_isvalid_tab (a); -- fails +ERROR: could not create unique index "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) is duplicated. +delete from parted_isvalid_tab_11; +create unique index parted_isvalid_uniq on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); +ERROR: duplicate key value violates unique constraint "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) already exists. select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join @@ -1688,12 +1706,19 @@ select indexrelid::regclass, indisvalid, order by indexrelid::regclass::text collate "C"; indexrelid | indisvalid | indrelid | inhparent -------------------------------+------------+-----------------------+------------------------------ - parted_isvalid_idx | f | parted_isvalid_tab | - parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_b_idx + parted_isvalid_idx | t | parted_isvalid_tab | + parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | + parted_isvalid_tab_11_a_b_idx | t | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_b_idx + parted_isvalid_tab_11_a_idx | t | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_idx parted_isvalid_tab_12_a_b_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_b_idx - parted_isvalid_tab_1_a_b_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx + parted_isvalid_tab_12_a_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_idx + parted_isvalid_tab_1_a_b_idx | t | parted_isvalid_tab_1 | parted_isvalid_idx + parted_isvalid_tab_1_a_idx | t | parted_isvalid_tab_1 | parted_isvalid_uniq parted_isvalid_tab_2_a_b_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx -(5 rows) + parted_isvalid_tab_2_a_idx | t | parted_isvalid_tab_2 | parted_isvalid_uniq + parted_isvalid_uniq | t | parted_isvalid_tab | + parted_isvalid_uniq_11 | f | parted_isvalid_tab_11 | +(12 rows) drop table parted_isvalid_tab; -- Check state of replica indexes when attaching a partition. diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index bbcfb365281..5a2fe1336af 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -165,10 +165,8 @@ create table idxpart21 partition of idxpart2 for values from (100) to (200); create table idxpart22 partition of idxpart2 for values from (200) to (300); create index on idxpart22 (a); create index on only idxpart2 (a); -create index on idxpart (a); --- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21 --- does not; also, idxpart22 is not attached. -\d idxpart1 +-- Here we expect that idxpart2 has a new index, but idxpart21 does not; +-- also, idxpart22 is not attached. \d idxpart2 \d idxpart21 select indexrelid::regclass, indrelid::regclass, inhparent::regclass @@ -187,6 +185,11 @@ alter index idxpart2_a_idx attach partition idxpart22_a_idx; create index on idxpart21 (a); alter index idxpart2_a_idx attach partition idxpart21_a_idx; \d idxpart2 +create index on idxpart (a); +select indexrelid::regclass, indrelid::regclass, inhparent::regclass + from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) +where indexrelid::regclass::text like 'idxpart%' + order by indexrelid::regclass::text collate "C"; drop table idxpart; -- When a table is attached a partition and it already has an index, a @@ -909,9 +912,8 @@ select indexrelid::regclass, indisvalid, order by indexrelid::regclass::text collate "C"; drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. +-- Check that invalid indexes are not selected when creating an index on a +-- partitioned table. create table parted_isvalid_tab (a int, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); @@ -921,12 +923,24 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 for values from (1) to (5); create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); --- create an invalid index on one of the partitions. +-- create an invalid expression index on one of the partitions. insert into parted_isvalid_tab_11 values (1, 0); create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the same offending row. +create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +-- Likewise for a failed concurrent unique index build on a partition: +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_uniq_11 on parted_isvalid_tab_11 (a); +create unique index parted_isvalid_uniq on parted_isvalid_tab (a); -- fails +delete from parted_isvalid_tab_11; +create unique index parted_isvalid_uniq on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join -- 2.56.0.317.g454f8302e1-goog
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-11-02 02:40   ` Kirill Reshke <reshkekirill@gmail.com>
  2026-11-02 12:46     ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Kirill Reshke @ 2026-11-02 02:40 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Sun, 1 Nov 2026 at 11:23, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference:      19817
> > Logged by:          chunling qin
> > Email address:      303677365@qq.com
> > PostgreSQL version: 18.6
> > Operating system:   Linux (TencentOS 4.4, x86_64)
> > Description:
> >
> > Hello,
> >
> > When CREATE UNIQUE INDEX is run on a partitioned table, DefineIndex()
> > recurses to each partition and reuses an already-existing index it considers
> > "equivalent" (src/backend/commands/indexcmds.c, the recursion loop around
> > lines 1450-1495).  That equivalence check only compares the index
> > definition;
> > it does not check indisvalid or indisready.  As a result, an index left
> > invalid (and not ready) by a previously failed CREATE INDEX CONCURRENTLY is
> > silently attached as a child index, the parent index is created with
> > indisvalid=false, and the whole chain reports success -- while in fact the
> > UNIQUE constraint is not enforced at all.
> >
> > Steps to reproduce (self-contained, no data needed):
> >
> >     -- a partition with a leftover invalid index from a failed CIC
> >     CREATE TABLE b2_root (a int, b text) PARTITION BY RANGE (a);
> >     CREATE TABLE b2_p1 PARTITION OF b2_root FOR VALUES FROM (0) TO (1000);
> >
> >     INSERT INTO b2_p1 VALUES (1,'x'), (1,'dup');           -- duplicate key
> >     CREATE UNIQUE INDEX CONCURRENTLY b2_p1_bad ON b2_p1 (a); -- fails,
> > leaves
> >     -- b2_p1_bad behind with indisvalid=f, indisready=f
> >     DELETE FROM b2_p1 WHERE b = 'dup';                       -- table is
> > clean now
> >
> >     -- the silent failure:
> >     CREATE UNIQUE INDEX b2_root_ui ON b2_root (a);           -- returns
> >     -- CREATE INDEX, no warning
> >
> >     -- state after: the invalid leftover is attached as a child index
> >     SELECT c.relname, i.indisvalid, i.indisready
> >       FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
> >      WHERE c.relname IN ('b2_p1_bad','b2_root_ui');
> >     --  b2_p1_bad   | f | f     (parent: b2_root_ui)
> >     --  b2_root_ui  | f | t
> >
> >     -- consequence a: uniqueness is not enforced, duplicates are accepted
> >     INSERT INTO b2_p1 VALUES (1,'x'), (1,'x');               -- succeeds
> >
> >     -- consequence b: ON CONFLICT can no longer be used on this table
> >     INSERT INTO b2_p1 VALUES (1,'x') ON CONFLICT (a) DO NOTHING;
> >     -- ERROR: there is no unique or exclusion constraint matching
> >     --        the ON CONFLICT specification
> >
> > Expected: the recursion loop should skip (or hard-fail on) child indexes
> > that are not indisvalid/indisready, at minimum emit a WARNING.  For
> > comparison, the ATTACH path already does a full check:
> > AttachPartitionEnsureIndexes() (src/backend/commands/tablecmds.c) refuses
> > in that situation, and the project has recently applied the same standard
> > to REPACK ("Fail REPACK in presence of invalid indexes", commits 0c5d6269614
> > and 78142821dfd).
> >
> > Actual: CREATE UNIQUE INDEX reports success and silently attaches the
> > invalid leftover index; the unique constraint enforces nothing; no warning
> > of any kind is emitted.
> >
> > Reproduced on:
> >   - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu)
> >   - PostgreSQL 18.6 (REL_18_STABLE, self-built)
> >
> > The behavior looks the same on both branches, so this appears to be
> > long-standing rather than a recent regression.
> >
> > A draft fix location would be the recursion loop in DefineIndex()
> > (src/backend/commands/indexcmds.c:1450-1495): before reusing an existing
> > partition index, require indisvalid && indisready, mirroring
> > AttachPartitionEnsureIndexes().
>
> Thanks for the report. I agree that the DefineIndex() should not reuse
> invalid indexes on partitions when creating an index on a partitioned
> table, consistent with how AttachPartitionEnsureIndexes() already
> skips !indisvalid indexes during ALTER TABLE ... ATTACH PARTITION
> (commit e90e9275f56). Please find attached a patch that skips invalid
> partition indexes in DefineIndex() and removes the now-unnecessary
> parent invalidation logic.
>
> --
> Regards,
> Dilip Kumar
> Google

Hi!
I caught a test failure with v1.

# diff -U3 /home/reshke/pgr/postgres/src/test/regress/expected/indexing.out
/home/reshke/pgr/postgres/src/test/regress/results/indexing.out
# --- /home/reshke/pgr/postgres/src/test/regress/expected/indexing.out
2026-11-02 02:36:57.687946852 +0000
# +++ /home/reshke/pgr/postgres/src/test/regress/results/indexing.out
2026-11-02 02:38:05.543186622 +0000
# @@ -1684,6 +1684,7 @@
#  -- Now creating the index on the partitioned table succeeds and creates new
#  -- valid indexes, discarding the invalid index created previously.
#  create index parted_isvalid_idx on parted_isvalid_tab ((a/b));
# +ERROR:  division by zero
#  -- Likewise for a failed concurrent unique index build on a partition:
#  insert into parted_isvalid_tab_11 values (1, 1), (1, 1);
#  create unique index concurrently parted_isvalid_uniq_11 on
parted_isvalid_tab_11 (a);
# @@ -1704,21 +1705,16 @@
#         pg_inherits inh on (idx.indexrelid = inh.inhrelid)
#    where indexrelid::regclass::text like 'parted_isvalid%'
#    order by indexrelid::regclass::text collate "C";
# -          indexrelid           | indisvalid |       indrelid
|          inhparent
# --------------------------------+------------+-----------------------+------------------------------
# - parted_isvalid_idx            | t          | parted_isvalid_tab    |
# - parted_isvalid_idx_11         | f          | parted_isvalid_tab_11 |
# - parted_isvalid_tab_11_a_b_idx | t          | parted_isvalid_tab_11
| parted_isvalid_tab_1_a_b_idx
# - parted_isvalid_tab_11_a_idx   | t          | parted_isvalid_tab_11
| parted_isvalid_tab_1_a_idx
# - parted_isvalid_tab_12_a_b_idx | t          | parted_isvalid_tab_12
| parted_isvalid_tab_1_a_b_idx
# - parted_isvalid_tab_12_a_idx   | t          | parted_isvalid_tab_12
| parted_isvalid_tab_1_a_idx
# - parted_isvalid_tab_1_a_b_idx  | t          | parted_isvalid_tab_1
| parted_isvalid_idx
# - parted_isvalid_tab_1_a_idx    | t          | parted_isvalid_tab_1
| parted_isvalid_uniq
# - parted_isvalid_tab_2_a_b_idx  | t          | parted_isvalid_tab_2
| parted_isvalid_idx
# - parted_isvalid_tab_2_a_idx    | t          | parted_isvalid_tab_2
| parted_isvalid_uniq
# - parted_isvalid_uniq           | t          | parted_isvalid_tab    |
# - parted_isvalid_uniq_11        | f          | parted_isvalid_tab_11 |
# -(12 rows)
# +         indexrelid          | indisvalid |       indrelid        |
        inhparent
# +-----------------------------+------------+-----------------------+----------------------------
# + parted_isvalid_idx_11       | f          | parted_isvalid_tab_11 |
# + parted_isvalid_tab_11_a_idx | t          | parted_isvalid_tab_11 |
parted_isvalid_tab_1_a_idx
# + parted_isvalid_tab_12_a_idx | t          | parted_isvalid_tab_12 |
parted_isvalid_tab_1_a_idx
# + parted_isvalid_tab_1_a_idx  | t          | parted_isvalid_tab_1  |
parted_isvalid_uniq
# + parted_isvalid_tab_2_a_idx  | t          | parted_isvalid_tab_2  |
parted_isvalid_uniq
# + parted_isvalid_uniq         | t          | parted_isvalid_tab    |
# + parted_isvalid_uniq_11      | f          | parted_isvalid_tab_11 |
# +(7 rows)
#
#  drop table parted_isvalid_tab;
#  -- Check state of replica indexes when attaching a partition.
not ok 220   + indexing                                  605 ms


fails about 1 in 3 runs for me.

(ERROR after delete from index).
The problem is, for parallel make checks, index creation fails because
of recently-dead tuples. Index build should always include those.
Example:

-- psql: CREATE TABLE t (a int, b int)
-- psql: INSERT INTO t VALUES (1, 0)

-- session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t;
-- (old snapshot held open)

-- psql: CREATE INDEX CONCURRENTLY t_expr_idx ON t ((a/b))
ERROR:  division by zero

-- psql: DELETE FROM t
-- psql: CREATE INDEX t_expr_idx2 ON t ((a/b))
ERROR:  division by zero



-- 
Best regards,
Kirill Reshke






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
@ 2026-11-02 12:46     ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-04 03:34       ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dilip Kumar @ 2026-11-02 12:46 UTC (permalink / raw)
  To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Mon, Nov 2, 2026 at 8:10 AM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Sun, 1 Nov 2026 at 11:23, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
> > <noreply@postgresql.org> wrote:
> > >
> > > The following bug has been logged on the website:
> > >
> > > Bug reference:      19817
> > > Logged by:          chunling qin
> > > Email address:      303677365@qq.com
> > > PostgreSQL version: 18.6
> > > Operating system:   Linux (TencentOS 4.4, x86_64)
> > > Description:
> > >
> > > Hello,
> > >
> > > When CREATE UNIQUE INDEX is run on a partitioned table, DefineIndex()
> > > recurses to each partition and reuses an already-existing index it considers
> > > "equivalent" (src/backend/commands/indexcmds.c, the recursion loop around
> > > lines 1450-1495).  That equivalence check only compares the index
> > > definition;
> > > it does not check indisvalid or indisready.  As a result, an index left
> > > invalid (and not ready) by a previously failed CREATE INDEX CONCURRENTLY is
> > > silently attached as a child index, the parent index is created with
> > > indisvalid=false, and the whole chain reports success -- while in fact the
> > > UNIQUE constraint is not enforced at all.
> > >
> > > Steps to reproduce (self-contained, no data needed):
> > >
> > >     -- a partition with a leftover invalid index from a failed CIC
> > >     CREATE TABLE b2_root (a int, b text) PARTITION BY RANGE (a);
> > >     CREATE TABLE b2_p1 PARTITION OF b2_root FOR VALUES FROM (0) TO (1000);
> > >
> > >     INSERT INTO b2_p1 VALUES (1,'x'), (1,'dup');           -- duplicate key
> > >     CREATE UNIQUE INDEX CONCURRENTLY b2_p1_bad ON b2_p1 (a); -- fails,
> > > leaves
> > >     -- b2_p1_bad behind with indisvalid=f, indisready=f
> > >     DELETE FROM b2_p1 WHERE b = 'dup';                       -- table is
> > > clean now
> > >
> > >     -- the silent failure:
> > >     CREATE UNIQUE INDEX b2_root_ui ON b2_root (a);           -- returns
> > >     -- CREATE INDEX, no warning
> > >
> > >     -- state after: the invalid leftover is attached as a child index
> > >     SELECT c.relname, i.indisvalid, i.indisready
> > >       FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
> > >      WHERE c.relname IN ('b2_p1_bad','b2_root_ui');
> > >     --  b2_p1_bad   | f | f     (parent: b2_root_ui)
> > >     --  b2_root_ui  | f | t
> > >
> > >     -- consequence a: uniqueness is not enforced, duplicates are accepted
> > >     INSERT INTO b2_p1 VALUES (1,'x'), (1,'x');               -- succeeds
> > >
> > >     -- consequence b: ON CONFLICT can no longer be used on this table
> > >     INSERT INTO b2_p1 VALUES (1,'x') ON CONFLICT (a) DO NOTHING;
> > >     -- ERROR: there is no unique or exclusion constraint matching
> > >     --        the ON CONFLICT specification
> > >
> > > Expected: the recursion loop should skip (or hard-fail on) child indexes
> > > that are not indisvalid/indisready, at minimum emit a WARNING.  For
> > > comparison, the ATTACH path already does a full check:
> > > AttachPartitionEnsureIndexes() (src/backend/commands/tablecmds.c) refuses
> > > in that situation, and the project has recently applied the same standard
> > > to REPACK ("Fail REPACK in presence of invalid indexes", commits 0c5d6269614
> > > and 78142821dfd).
> > >
> > > Actual: CREATE UNIQUE INDEX reports success and silently attaches the
> > > invalid leftover index; the unique constraint enforces nothing; no warning
> > > of any kind is emitted.
> > >
> > > Reproduced on:
> > >   - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu)
> > >   - PostgreSQL 18.6 (REL_18_STABLE, self-built)
> > >
> > > The behavior looks the same on both branches, so this appears to be
> > > long-standing rather than a recent regression.
> > >
> > > A draft fix location would be the recursion loop in DefineIndex()
> > > (src/backend/commands/indexcmds.c:1450-1495): before reusing an existing
> > > partition index, require indisvalid && indisready, mirroring
> > > AttachPartitionEnsureIndexes().
> >
> > Thanks for the report. I agree that the DefineIndex() should not reuse
> > invalid indexes on partitions when creating an index on a partitioned
> > table, consistent with how AttachPartitionEnsureIndexes() already
> > skips !indisvalid indexes during ALTER TABLE ... ATTACH PARTITION
> > (commit e90e9275f56). Please find attached a patch that skips invalid
> > partition indexes in DefineIndex() and removes the now-unnecessary
> > parent invalidation logic.
> >
> > --
> > Regards,
> > Dilip Kumar
> > Google
>
> Hi!
> I caught a test failure with v1.
>
> # diff -U3 /home/reshke/pgr/postgres/src/test/regress/expected/indexing.out
> /home/reshke/pgr/postgres/src/test/regress/results/indexing.out
> # --- /home/reshke/pgr/postgres/src/test/regress/expected/indexing.out
> 2026-11-02 02:36:57.687946852 +0000
> # +++ /home/reshke/pgr/postgres/src/test/regress/results/indexing.out
> 2026-11-02 02:38:05.543186622 +0000
> # @@ -1684,6 +1684,7 @@
> #  -- Now creating the index on the partitioned table succeeds and creates new
> #  -- valid indexes, discarding the invalid index created previously.
> #  create index parted_isvalid_idx on parted_isvalid_tab ((a/b));
> # +ERROR:  division by zero
> #  -- Likewise for a failed concurrent unique index build on a partition:
> #  insert into parted_isvalid_tab_11 values (1, 1), (1, 1);
> #  create unique index concurrently parted_isvalid_uniq_11 on
> parted_isvalid_tab_11 (a);
> # @@ -1704,21 +1705,16 @@
> #         pg_inherits inh on (idx.indexrelid = inh.inhrelid)
> #    where indexrelid::regclass::text like 'parted_isvalid%'
> #    order by indexrelid::regclass::text collate "C";
> # -          indexrelid           | indisvalid |       indrelid
> |          inhparent
> # --------------------------------+------------+-----------------------+------------------------------
> # - parted_isvalid_idx            | t          | parted_isvalid_tab    |
> # - parted_isvalid_idx_11         | f          | parted_isvalid_tab_11 |
> # - parted_isvalid_tab_11_a_b_idx | t          | parted_isvalid_tab_11
> | parted_isvalid_tab_1_a_b_idx
> # - parted_isvalid_tab_11_a_idx   | t          | parted_isvalid_tab_11
> | parted_isvalid_tab_1_a_idx
> # - parted_isvalid_tab_12_a_b_idx | t          | parted_isvalid_tab_12
> | parted_isvalid_tab_1_a_b_idx
> # - parted_isvalid_tab_12_a_idx   | t          | parted_isvalid_tab_12
> | parted_isvalid_tab_1_a_idx
> # - parted_isvalid_tab_1_a_b_idx  | t          | parted_isvalid_tab_1
> | parted_isvalid_idx
> # - parted_isvalid_tab_1_a_idx    | t          | parted_isvalid_tab_1
> | parted_isvalid_uniq
> # - parted_isvalid_tab_2_a_b_idx  | t          | parted_isvalid_tab_2
> | parted_isvalid_idx
> # - parted_isvalid_tab_2_a_idx    | t          | parted_isvalid_tab_2
> | parted_isvalid_uniq
> # - parted_isvalid_uniq           | t          | parted_isvalid_tab    |
> # - parted_isvalid_uniq_11        | f          | parted_isvalid_tab_11 |
> # -(12 rows)
> # +         indexrelid          | indisvalid |       indrelid        |
>         inhparent
> # +-----------------------------+------------+-----------------------+----------------------------
> # + parted_isvalid_idx_11       | f          | parted_isvalid_tab_11 |
> # + parted_isvalid_tab_11_a_idx | t          | parted_isvalid_tab_11 |
> parted_isvalid_tab_1_a_idx
> # + parted_isvalid_tab_12_a_idx | t          | parted_isvalid_tab_12 |
> parted_isvalid_tab_1_a_idx
> # + parted_isvalid_tab_1_a_idx  | t          | parted_isvalid_tab_1  |
> parted_isvalid_uniq
> # + parted_isvalid_tab_2_a_idx  | t          | parted_isvalid_tab_2  |
> parted_isvalid_uniq
> # + parted_isvalid_uniq         | t          | parted_isvalid_tab    |
> # + parted_isvalid_uniq_11      | f          | parted_isvalid_tab_11 |
> # +(7 rows)
> #
> #  drop table parted_isvalid_tab;
> #  -- Check state of replica indexes when attaching a partition.
> not ok 220   + indexing                                  605 ms
>
>
> fails about 1 in 3 runs for me.
>
> (ERROR after delete from index).
> The problem is, for parallel make checks, index creation fails because
> of recently-dead tuples. Index build should always include those.
> Example:
>
> -- psql: CREATE TABLE t (a int, b int)
> -- psql: INSERT INTO t VALUES (1, 0)
>
> -- session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM t;
> -- (old snapshot held open)
>
> -- psql: CREATE INDEX CONCURRENTLY t_expr_idx ON t ((a/b))
> ERROR:  division by zero
>
> -- psql: DELETE FROM t
> -- psql: CREATE INDEX t_expr_idx2 ON t ((a/b))
> ERROR:  division by zero

Thanks for catching this.  In parallel check, a concurrent session
holding an older snapshot causes heapam_index_build_range_scan() to
see the deleted (1, 0) row as HEAPTUPLE_RECENTLY_DEAD, so
FormIndexDatum() still evaluates (a/b) on it, whereas unique-checking
is skipped for RECENTLY_DEAD tuples, So IMHO we can keep unique
checking test only as that will also test what we intend to test here
and remove the divide by zero test from here?

Attached is v2, which uses the failed concurrent unique index build
(duplicate keys) for that test instead of the division-by-zero
expression index so that RECENTLY_DEAD tuples do not cause spurious
failures.

-- 
Regards,
Dilip Kumar
Google

Attachments:

v2-0001-Ignore-invalid-indexes-on-partitions-during-CREAT.patch (application/octet-stream, 18.2K) download From a66c346e2a6ac9e36dbde5d6e89cd0dcf5d3b27b Mon Sep 17 00:00:00 2001 From: Dilip Kumar <dilipkumarb@google.com> Date: Sun, 1 Nov 2026 06:11:09 +0000 Subject: [PATCH v2] Ignore invalid indexes on partitions during CREATE INDEX When DefineIndex() creates an index on a partitioned table (without ONLY), it recurses to each partition and scans existing indexes on the partition to see if a matching, unattached index can be reused and attached to the newly created parent index. Previously, this check only compared the structural index definition via CompareIndexInfo() without checking pg_index.indisvalid. As a result, an invalid index left behind on a partition by a previously failed CREATE [UNIQUE] INDEX CONCURRENTLY (with indisvalid = false and possibly indisready = false), or an incomplete partitioned index on a sub-partitioned table created with CREATE [UNIQUE] INDEX ON ONLY, would be attached as a child index, marking the parent partitioned index indisvalid = false and skipping index creation on the partition (or its sub-partitions). In particular, for CREATE UNIQUE INDEX (or ALTER TABLE ADD UNIQUE / PRIMARY KEY), the command would report success even when the partition contained duplicate values, and when indisready = false on the reused leaf index, subsequent inserts and updates would not enforce the unique constraint either (since ExecInsertIndexTuples() skips !indisready indexes). Furthermore, once attached to the parent index, the broken child index could no longer be dropped directly with DROP INDEX due to its partition dependency. Commit e90e9275f56 fixed the analogous issue in AttachPartitionEnsureIndexes() for ALTER TABLE ... ATTACH PARTITION by skipping invalid indexes on the partition being attached. Apply the same rule in DefineIndex(): skip existing partition indexes where !indisvalid, so that a new valid index is built on the partition instead. Reported-by: chunling qin Authored-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> --- doc/src/sgml/ref/create_index.sgml | 6 +- src/backend/commands/indexcmds.c | 76 ++++++---------------- src/test/regress/expected/indexing.out | 89 +++++++++++++++----------- src/test/regress/sql/indexing.sql | 32 +++++---- 4 files changed, 93 insertions(+), 110 deletions(-) diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bb7505d171b..1aca4add5dc 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -752,14 +752,14 @@ Indexes: When <literal>CREATE INDEX</literal> is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. - Each partition is first checked to determine whether an equivalent + Each partition is first checked to determine whether a valid equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. - If no matching index exists, a new index will be created and + If no matching valid index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the - command. + command. Invalid indexes on the partitions are skipped. If the <literal>ONLY</literal> option is specified, no recursion is done, and the index is marked invalid. (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 5a0312fe772..7dec7f66c1e 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1339,7 +1339,6 @@ DefineIndex(ParseState *pstate, { int nparts = partdesc->nparts; Oid *part_oids = palloc_array(Oid, nparts); - bool invalidate_parent = false; Relation parentIndex; TupleDesc parentDesc; @@ -1391,9 +1390,9 @@ DefineIndex(ParseState *pstate, parentDesc = RelationGetDescr(rel); /* - * For each partition, scan all existing indexes; if one matches - * our index definition and is not already attached to some other - * parent index, attach it to the one we just created. + * For each partition, scan all existing indexes; if one is valid, + * matches our index definition, and is not already attached to + * some other parent index, attach it to the one we just created. * * If none matches, build a new index by calling ourselves * recursively with the same options (except for the index name). @@ -1458,6 +1457,14 @@ DefineIndex(ParseState *pstate, continue; cldidx = index_open(cldidxid, lockmode); + + /* If this index is invalid, can't use it */ + if (!cldidx->rd_index->indisvalid) + { + index_close(cldidx, lockmode); + continue; + } + cldIdxInfo = BuildIndexInfo(cldidx); if (CompareIndexInfo(cldIdxInfo, indexInfo, cldidx->rd_indcollation, @@ -1496,9 +1503,6 @@ DefineIndex(ParseState *pstate, createdConstraintId, childRelid); - if (!cldidx->rd_index->indisvalid) - invalidate_parent = true; - found = true; /* @@ -1532,7 +1536,6 @@ DefineIndex(ParseState *pstate, if (!found) { IndexStmt *childStmt; - ObjectAddress childAddr; /* * Build an IndexStmt describing the desired child index @@ -1553,62 +1556,23 @@ DefineIndex(ParseState *pstate, Assert(GetUserId() == child_save_userid); SetUserIdAndSecContext(root_save_userid, root_save_sec_context); - childAddr = - DefineIndex(NULL, /* original pstate not applicable */ - childRelid, childStmt, - InvalidOid, /* no predefined OID */ - indexRelationId, /* this is our child */ - createdConstraintId, - -1, - is_alter_table, check_rights, - check_not_in_use, - skip_build, quiet); + DefineIndex(NULL, /* original pstate not applicable */ + childRelid, childStmt, + InvalidOid, /* no predefined OID */ + indexRelationId, /* this is our child */ + createdConstraintId, + -1, + is_alter_table, check_rights, + check_not_in_use, + skip_build, quiet); SetUserIdAndSecContext(child_save_userid, child_save_sec_context); - - /* - * Check if the index just created is valid or not, as it - * could be possible that it has been switched as invalid - * when recursing across multiple partition levels. - */ - if (!get_index_isvalid(childAddr.objectId)) - invalidate_parent = true; } free_attrmap(attmap); } index_close(parentIndex, lockmode); - - /* - * The pg_index row we inserted for this index was marked - * indisvalid=true. But if we attached an existing index that is - * invalid, this is incorrect, so update our row to invalid too. - */ - if (invalidate_parent) - { - Relation pg_index = table_open(IndexRelationId, RowExclusiveLock); - HeapTuple tup, - newtup; - - tup = SearchSysCache1(INDEXRELID, - ObjectIdGetDatum(indexRelationId)); - if (!HeapTupleIsValid(tup)) - elog(ERROR, "cache lookup failed for index %u", - indexRelationId); - newtup = heap_copytuple(tup); - ((Form_pg_index) GETSTRUCT(newtup))->indisvalid = false; - CatalogTupleUpdate(pg_index, &tup->t_self, newtup); - ReleaseSysCache(tup); - table_close(pg_index, RowExclusiveLock); - heap_freetuple(newtup); - - /* - * CCI here to make this update visible, in case this recurses - * across multiple partition levels. - */ - CommandCounterIncrement(); - } } /* diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 4a0a652e9f6..734f3801733 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -292,18 +292,8 @@ create table idxpart21 partition of idxpart2 for values from (100) to (200); create table idxpart22 partition of idxpart2 for values from (200) to (300); create index on idxpart22 (a); create index on only idxpart2 (a); -create index on idxpart (a); --- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21 --- does not; also, idxpart22 is not attached. -\d idxpart1 - Table "public.idxpart1" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - a | integer | | | -Partition of: idxpart FOR VALUES FROM (0) TO (100) -Indexes: - "idxpart1_a_idx" btree (a) - +-- Here we expect that idxpart2 has a new index, but idxpart21 does not; +-- also, idxpart22 is not attached. \d idxpart2 Partitioned table "public.idxpart2" Column | Type | Collation | Nullable | Default @@ -326,13 +316,11 @@ select indexrelid::regclass, indrelid::regclass, inhparent::regclass from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) where indexrelid::regclass::text like 'idxpart%' order by indexrelid::regclass::text collate "C"; - indexrelid | indrelid | inhparent ------------------+-----------+--------------- - idxpart1_a_idx | idxpart1 | idxpart_a_idx + indexrelid | indrelid | inhparent +-----------------+-----------+----------- idxpart22_a_idx | idxpart22 | - idxpart2_a_idx | idxpart2 | idxpart_a_idx - idxpart_a_idx | idxpart | -(4 rows) + idxpart2_a_idx | idxpart2 | +(2 rows) alter index idxpart2_a_idx attach partition idxpart22_a_idx; select indexrelid::regclass, indrelid::regclass, inhparent::regclass @@ -341,11 +329,9 @@ where indexrelid::regclass::text like 'idxpart%' order by indexrelid::regclass::text collate "C"; indexrelid | indrelid | inhparent -----------------+-----------+---------------- - idxpart1_a_idx | idxpart1 | idxpart_a_idx idxpart22_a_idx | idxpart22 | idxpart2_a_idx - idxpart2_a_idx | idxpart2 | idxpart_a_idx - idxpart_a_idx | idxpart | -(4 rows) + idxpart2_a_idx | idxpart2 | +(2 rows) -- attaching idxpart22 is not enough to set idxpart22_a_idx valid ... alter index idxpart2_a_idx attach partition idxpart22_a_idx; @@ -374,6 +360,20 @@ Indexes: "idxpart2_a_idx" btree (a) Number of partitions: 2 (Use \d+ to list them.) +create index on idxpart (a); +select indexrelid::regclass, indrelid::regclass, inhparent::regclass + from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) +where indexrelid::regclass::text like 'idxpart%' + order by indexrelid::regclass::text collate "C"; + indexrelid | indrelid | inhparent +-----------------+-----------+---------------- + idxpart1_a_idx | idxpart1 | idxpart_a_idx + idxpart21_a_idx | idxpart21 | idxpart2_a_idx + idxpart22_a_idx | idxpart22 | idxpart2_a_idx + idxpart2_a_idx | idxpart2 | idxpart_a_idx + idxpart_a_idx | idxpart | +(5 rows) + drop table idxpart; -- When a table is attached a partition and it already has an index, a -- duplicate index should not get created, but rather the index becomes @@ -1661,9 +1661,8 @@ select indexrelid::regclass, indisvalid, (5 rows) drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. +-- Check that invalid indexes are not selected when creating an index on a +-- partitioned table. create table parted_isvalid_tab (a int, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); @@ -1674,26 +1673,38 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); -- create an invalid index on one of the partitions. -insert into parted_isvalid_tab_11 values (1, 0); -create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); -ERROR: division by zero --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. -create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 (a); +ERROR: could not create unique index "parted_isvalid_idx_11" +DETAIL: Key (a)=(1) is duplicated. +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the duplicate rows. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +ERROR: could not create unique index "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) is duplicated. +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); +ERROR: duplicate key value violates unique constraint "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) already exists. select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) where indexrelid::regclass::text like 'parted_isvalid%' order by indexrelid::regclass::text collate "C"; - indexrelid | indisvalid | indrelid | inhparent --------------------------------+------------+-----------------------+------------------------------ - parted_isvalid_idx | f | parted_isvalid_tab | - parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_b_idx - parted_isvalid_tab_12_a_b_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_b_idx - parted_isvalid_tab_1_a_b_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx - parted_isvalid_tab_2_a_b_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx -(5 rows) + indexrelid | indisvalid | indrelid | inhparent +-----------------------------+------------+-----------------------+---------------------------- + parted_isvalid_idx | t | parted_isvalid_tab | + parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | + parted_isvalid_tab_11_a_idx | t | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_idx + parted_isvalid_tab_12_a_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_idx + parted_isvalid_tab_1_a_idx | t | parted_isvalid_tab_1 | parted_isvalid_idx + parted_isvalid_tab_2_a_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx +(6 rows) drop table parted_isvalid_tab; -- Check state of replica indexes when attaching a partition. diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index bbcfb365281..7c56b2f0aa2 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -165,10 +165,8 @@ create table idxpart21 partition of idxpart2 for values from (100) to (200); create table idxpart22 partition of idxpart2 for values from (200) to (300); create index on idxpart22 (a); create index on only idxpart2 (a); -create index on idxpart (a); --- Here we expect that idxpart1 and idxpart2 have a new index, but idxpart21 --- does not; also, idxpart22 is not attached. -\d idxpart1 +-- Here we expect that idxpart2 has a new index, but idxpart21 does not; +-- also, idxpart22 is not attached. \d idxpart2 \d idxpart21 select indexrelid::regclass, indrelid::regclass, inhparent::regclass @@ -187,6 +185,11 @@ alter index idxpart2_a_idx attach partition idxpart22_a_idx; create index on idxpart21 (a); alter index idxpart2_a_idx attach partition idxpart21_a_idx; \d idxpart2 +create index on idxpart (a); +select indexrelid::regclass, indrelid::regclass, inhparent::regclass + from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) +where indexrelid::regclass::text like 'idxpart%' + order by indexrelid::regclass::text collate "C"; drop table idxpart; -- When a table is attached a partition and it already has an index, a @@ -909,9 +912,8 @@ select indexrelid::regclass, indisvalid, order by indexrelid::regclass::text collate "C"; drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. +-- Check that invalid indexes are not selected when creating an index on a +-- partitioned table. create table parted_isvalid_tab (a int, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); @@ -922,11 +924,17 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); -- create an invalid index on one of the partitions. -insert into parted_isvalid_tab_11 values (1, 0); -create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. -create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 (a); +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the duplicate rows. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join -- 2.56.0.317.g454f8302e1-goog
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
  2026-11-02 12:46     ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-11-04 03:34       ` shihao zhong <zhong950419@gmail.com>
  2026-11-05 09:28         ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: shihao zhong @ 2026-11-04 03:34 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi Dilip,

Thanks for working on this! v2 looks great!

I have one concern here:

This patch skips partitioned indexes made with ON ONLY, which are invalid
until their partitions are attached. This breaks a case that works on
master:

 create table r (a int not null) partition by range (a);
  create table s partition of r for values from (0) to (100)
    partition by range (a);
  create table s1 partition of s for values from (0) to (100);
  alter table only s add primary key (a);
  alter table r add primary key (a);

With v2 the last command fails:

  ERROR:  multiple primary keys for table "s" are not allowed

Maybe skip only invalid indexes that are not partitioned?

Thanks,
Shihao

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
  2026-11-02 12:46     ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-04 03:34       ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
@ 2026-11-05 09:28         ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-08 01:15           ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dilip Kumar @ 2026-11-05 09:28 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: Kirill Reshke <reshkekirill@gmail.com>; 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Wed, Nov 4, 2026 at 9:04 AM shihao zhong <zhong950419@gmail.com> wrote:
>
> Hi Dilip,
>
> Thanks for working on this! v2 looks great!
>
> I have one concern here:
>
> This patch skips partitioned indexes made with ON ONLY, which are invalid
> until their partitions are attached. This breaks a case that works on
> master:
>
>  create table r (a int not null) partition by range (a);
>   create table s partition of r for values from (0) to (100)
>     partition by range (a);
>   create table s1 partition of s for values from (0) to (100);
>   alter table only s add primary key (a);
>   alter table r add primary key (a);
>
> With v2 the last command fails:
>
>   ERROR:  multiple primary keys for table "s" are not allowed

Hmm, thats doesn't look good

> Maybe skip only invalid indexes that are not partitioned?

Yeah that seems like a right way to fix this.  How about this?


-- 
Regards,
Dilip Kumar
Google

Attachments:

v3-0001-Ignore-invalid-leaf-indexes-on-partitions-during-.patch (application/octet-stream, 11.5K) download From 5b4baa1446a451b033a14496971aab56ef75a0b7 Mon Sep 17 00:00:00 2001 From: Dilip Kumar <dilipkumarb@google.com> Date: Sun, 1 Nov 2026 06:11:09 +0000 Subject: [PATCH v3] Ignore invalid leaf indexes on partitions during CREATE INDEX When DefineIndex() creates an index on a partitioned table (without ONLY), it recurses to each partition and scans existing indexes on the partition to see if a matching, unattached index can be reused and attached to the newly created parent index. Previously, this check only compared the structural index definition via CompareIndexInfo() without checking pg_index.indisvalid. As a result, an invalid index left behind on a leaf partition by a previously failed CREATE [UNIQUE] INDEX CONCURRENTLY (with indisvalid = false and possibly indisready = false) would be attached as a child index, marking the parent partitioned index indisvalid = false and skipping index creation on the partition. In particular, for CREATE UNIQUE INDEX (or ALTER TABLE ADD UNIQUE / PRIMARY KEY), the command would report success even when the partition contained duplicate values, and when indisready = false on the reused leaf index, subsequent inserts and updates would not enforce the unique constraint either (since ExecInsertIndexTuples() skips !indisready indexes). Furthermore, once attached to the parent index, the broken child index could no longer be dropped directly with DROP INDEX due to its partition dependency. Fix this in DefineIndex() by skipping existing non-partitioned (RELKIND_INDEX) indexes on partitions where !indisvalid, so that a new valid index is built on the leaf partition instead. Partitioned indexes (RELKIND_PARTITIONED_INDEX) on sub-partitioned tables created with ON ONLY remain invalid until their own partitions are attached, so continue to allow attaching those and marking the parent index invalid. Reported-by: chunling qin Authored-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Kirill Reshke <reshkekirill@gmail.com> Reviewed-by: shihao zhong <zhong950419@gmail.com> --- doc/src/sgml/ref/create_index.sgml | 2 +- src/backend/commands/indexcmds.c | 14 +++++++ src/test/regress/expected/indexing.out | 57 +++++++++++++++++--------- src/test/regress/sql/indexing.sql | 30 +++++++++----- 4 files changed, 73 insertions(+), 30 deletions(-) diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bb7505d171b..290aecfde48 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -759,7 +759,7 @@ Indexes: If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the - command. + command. Invalid indexes on leaf partitions are skipped. If the <literal>ONLY</literal> option is specified, no recursion is done, and the index is marked invalid. (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 5a0312fe772..f9a46fadea1 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1458,6 +1458,20 @@ DefineIndex(ParseState *pstate, continue; cldidx = index_open(cldidxid, lockmode); + + /* + * Can't use an invalid regular index (e.g., left behind + * by a failed CREATE INDEX CONCURRENTLY). Partitioned + * indexes made with ON ONLY are invalid until their + * partitions are attached, so we still allow those. + */ + if (cldidx->rd_rel->relkind != RELKIND_PARTITIONED_INDEX && + !cldidx->rd_index->indisvalid) + { + index_close(cldidx, lockmode); + continue; + } + cldIdxInfo = BuildIndexInfo(cldidx); if (CompareIndexInfo(cldIdxInfo, indexInfo, cldidx->rd_indcollation, diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 4a0a652e9f6..87c08e24546 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -1661,10 +1661,10 @@ select indexrelid::regclass, indisvalid, (5 rows) drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. -create table parted_isvalid_tab (a int, b int) partition by range (a); +-- Check that invalid leaf indexes are not selected when creating an index on +-- a partitioned table, while partitioned indexes created with ON ONLY on +-- sub-partitioned tables are still attached (marking the parent invalid). +create table parted_isvalid_tab (a int not null, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); create table parted_isvalid_tab_2 partition of parted_isvalid_tab @@ -1673,27 +1673,46 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 for values from (1) to (5); create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); --- create an invalid index on one of the partitions. -insert into parted_isvalid_tab_11 values (1, 0); -create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); -ERROR: division by zero --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. -create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +-- create an invalid index on one of the leaf partitions. +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 (a); +ERROR: could not create unique index "parted_isvalid_idx_11" +DETAIL: Key (a)=(1) is duplicated. +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the duplicate rows. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +ERROR: could not create unique index "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) is duplicated. +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); +ERROR: duplicate key value violates unique constraint "parted_isvalid_tab_11_a_idx" +DETAIL: Key (a)=(1) already exists. +-- A partitioned index created with ON ONLY on a sub-partitioned table is +-- attached and invalidates the parent index until its partitions are attached. +alter table only parted_isvalid_tab_1 add primary key (a); +alter table parted_isvalid_tab add primary key (a); select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid) where indexrelid::regclass::text like 'parted_isvalid%' order by indexrelid::regclass::text collate "C"; - indexrelid | indisvalid | indrelid | inhparent --------------------------------+------------+-----------------------+------------------------------ - parted_isvalid_idx | f | parted_isvalid_tab | - parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_b_idx - parted_isvalid_tab_12_a_b_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_b_idx - parted_isvalid_tab_1_a_b_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx - parted_isvalid_tab_2_a_b_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx -(5 rows) + indexrelid | indisvalid | indrelid | inhparent +-----------------------------+------------+-----------------------+---------------------------- + parted_isvalid_idx | t | parted_isvalid_tab | + parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | + parted_isvalid_tab_11_a_idx | t | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_idx + parted_isvalid_tab_12_a_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_idx + parted_isvalid_tab_1_a_idx | t | parted_isvalid_tab_1 | parted_isvalid_idx + parted_isvalid_tab_1_pkey | f | parted_isvalid_tab_1 | parted_isvalid_tab_pkey + parted_isvalid_tab_2_a_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx + parted_isvalid_tab_2_pkey | t | parted_isvalid_tab_2 | parted_isvalid_tab_pkey + parted_isvalid_tab_pkey | f | parted_isvalid_tab | +(9 rows) drop table parted_isvalid_tab; -- Check state of replica indexes when attaching a partition. diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index bbcfb365281..24ce5516493 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -909,10 +909,10 @@ select indexrelid::regclass, indisvalid, order by indexrelid::regclass::text collate "C"; drop table parted_inval_tab; --- Check setup of indisvalid across a complex partition tree on index --- creation. If one index in a partition index is invalid, so should its --- partitioned index. -create table parted_isvalid_tab (a int, b int) partition by range (a); +-- Check that invalid leaf indexes are not selected when creating an index on +-- a partitioned table, while partitioned indexes created with ON ONLY on +-- sub-partitioned tables are still attached (marking the parent invalid). +create table parted_isvalid_tab (a int not null, b int) partition by range (a); create table parted_isvalid_tab_1 partition of parted_isvalid_tab for values from (1) to (10) partition by range (a); create table parted_isvalid_tab_2 partition of parted_isvalid_tab @@ -921,12 +921,22 @@ create table parted_isvalid_tab_11 partition of parted_isvalid_tab_1 for values from (1) to (5); create table parted_isvalid_tab_12 partition of parted_isvalid_tab_1 for values from (5) to (10); --- create an invalid index on one of the partitions. -insert into parted_isvalid_tab_11 values (1, 0); -create index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 ((a/b)); --- The previous invalid index is selected, invalidating all the indexes up to --- the top-most parent. -create index parted_isvalid_idx on parted_isvalid_tab ((a/b)); +-- create an invalid index on one of the leaf partitions. +insert into parted_isvalid_tab_11 values (1, 1), (1, 1); +create unique index concurrently parted_isvalid_idx_11 on parted_isvalid_tab_11 (a); +-- The previous invalid index is skipped, so attempting to build a new index +-- on the partitioned table fails on the duplicate rows. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +delete from parted_isvalid_tab_11; +-- Now creating the index on the partitioned table succeeds and creates new +-- valid indexes, discarding the invalid index created previously. +create unique index parted_isvalid_idx on parted_isvalid_tab (a); +-- Uniqueness is enforced on the partition: +insert into parted_isvalid_tab values (1, 1), (1, 2); +-- A partitioned index created with ON ONLY on a sub-partitioned table is +-- attached and invalidates the parent index until its partitions are attached. +alter table only parted_isvalid_tab_1 add primary key (a); +alter table parted_isvalid_tab add primary key (a); select indexrelid::regclass, indisvalid, indrelid::regclass, inhparent::regclass from pg_index idx left join -- 2.56.0.385.gac671c819a-goog
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
  2026-11-02 12:46     ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-04 03:34       ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
  2026-11-05 09:28         ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-11-08 01:15           ` shihao zhong <zhong950419@gmail.com>
  2026-11-16 14:02             ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Joao Rito <joaovictor.rito@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: shihao zhong @ 2026-11-08 01:15 UTC (permalink / raw)
  To: dilipbalaut@gmail.com; +Cc: reshkekirill@gmail.com; 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi Dilip,

Thanks for v3. I tested it on current master.

The ON ONLY cases from my last mail work now, and the original
bug is fixed. A duplicate row is caught after a failed CREATE
UNIQUE INDEX CONCURRENTLY, and a good leaf index is built once the
duplicates are gone.

everything is good! +1 from me.

Thanks,
Shihao

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
  2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
  2026-11-01 06:23 ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-02 02:40   ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Kirill Reshke <reshkekirill@gmail.com>
  2026-11-02 12:46     ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-04 03:34       ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
  2026-11-05 09:28         ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-08 01:15           ` Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le shihao zhong <zhong950419@gmail.com>
@ 2026-11-16 14:02             ` Joao Rito <joaovictor.rito@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Joao Rito @ 2026-11-16 14:02 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Kirill Reshke <reshkekirill@gmail.com>; 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi Dilip,

Thank you for the patch. I tested v3-0001 on current master. It
fixes the report for me. These are my results.

Setup: master at 7fc2f0423320 (2026-11-16), Debian, gcc 14,
configure with --enable-cassert --enable-debug
--enable-tap-tests --without-icu. I ran the cases on plain
master, then applied v3-0001, rebuilt, and ran them again.

Reproduction from the report (b2_root / b2_p1):

 - Without the patch, CREATE UNIQUE INDEX b2_root_ui returns
   CREATE INDEX. b2_p1_bad is attached to b2_root_ui with
   indisvalid = f and indisready = f, and b2_root_ui has
   indisvalid = f. The insert of (1,'x'),(1,'x') succeeds (3 rows
   with a = 1), and ON CONFLICT (a) fails with "there is no unique
   or exclusion constraint matching the ON CONFLICT
   specification".
 - With the patch, a new leaf index b2_p1_a_idx is built, it is
   attached, and b2_root_ui is valid. The duplicate insert fails
   with "duplicate key value violates unique constraint
   "b2_p1_a_idx"". ON CONFLICT (a) DO NOTHING works. The old
   b2_p1_bad stays behind as an invalid, unattached index, and
   it can be dropped.

Other cases, all with the patch. Shihao already confirmed
the PK case on v3; I list it for completeness:

 - A three-level tree (root, a partitioned mid level, two leaves
   under it, plus one leaf directly under root) with a leftover
   invalid index on one lower leaf. Both partitioned indexes are
   valid, and the leftover is not attached. The unique index
   rejects a duplicate on the two leaves I tried (the one with
   the leftover and its sibling).
 - ALTER TABLE ONLY s ADD PRIMARY KEY (a) on a partitioned
   sub-table, then ALTER TABLE r ADD PRIMARY KEY (a), and then
   ADD PRIMARY KEY on the leaf. No error. This is the case
   Shihao raised on v2 and confirmed on v3. The output is the
   same as on master.
 - ALTER TABLE ... ATTACH PARTITION with a leftover invalid
   index on the table being attached: master and patched give
   the same result (a new valid index is attached, the leftover
   stays out).
 - CREATE UNIQUE INDEX ... ON ONLY, CIC on each leaf, then
   ALTER INDEX ... ATTACH PARTITION: the parent becomes valid.
   CIC directly on the partitioned table still gives the same
   error as before.
 - A failed non-unique CIC (expression 10/a with a row a = 0),
   then CREATE INDEX on the parent: the invalid leaf index is
   skipped as well, and a new valid one is attached.
 - A leftover named s8_p1_a_idx (failed unique CIC), then
   CREATE UNIQUE INDEX on the parent: the new child is named
   s8_p1_a_idx1, so there is no name collision.
 - After removing the duplicate with the leftover still in
   place, REINDEX INDEX CONCURRENTLY on the leftover makes it
   valid, and a later CREATE UNIQUE INDEX on the parent attaches
   it. If the duplicate is still there, CREATE UNIQUE INDEX
   on the parent fails on the leaf and leaves nothing behind.
 - pg_dump of the patched result restores with no errors. The
   restored database has 2 invalid indexes, and the original
   has 7 (5 leftovers and 2 ON ONLY parents). I did not list
   the 2; I expect they are the ON ONLY parents from the
   primary key case. The duplicate insert fails in the
   restored database.
 - amcheck bt_index_check(..., heapallindexed) passes on the
   13 valid btree indexes from these cases.

Test suites with the patch:

 - make check: all 243 tests passed.
 - make -C src/test/isolation check: all 139 tests passed.
 - contrib/amcheck check: all 4 tests passed.
 - src/bin/pg_dump t/002_pg_dump.pl: 13361 tests, all passed.

The patch applies with git apply and "git diff --check" is
clean. The build has no warnings.

On the change: skipping only invalid indexes that are not
partitioned indexes looks right to me. It keeps the ON ONLY
partitioned indexes attachable, and it matches the ATTACH
PARTITION path, which already skips invalid indexes
(e90e9275f56). The new test expects errors in the right places
(the CIC on duplicate keys, the parent CREATE UNIQUE INDEX and
the duplicate insert). It uses a duplicate key, not the
expression index from v1 that Kirill found to be flaky. I ran
make check only once, so I cannot say more about flakiness.

Two small points, both optional:

 - The leftover invalid index stays in place, and the command
   does not say so. A NOTICE or a doc sentence that says to
   drop the leftover would help users who see two indexes on
   the same column. The new doc sentence, "Invalid indexes on
   leaf partitions are skipped.", could say "are skipped, not
   attached and not dropped".
 - The commit message says that the broken child "could no
   longer be dropped directly" once attached. I did not test
   that on the unpatched tree, so I take it from the message.

What I did not test:

 - Other branches (the report says 18.6 is affected too), so I
   cannot say how the patch looks as a backpatch.
 - Concurrent DDL or DML while CREATE INDEX recurses over the
   partitions.
 - Hash and list partitioned tables, non-btree access methods,
   partial indexes, and indexes with INCLUDE columns.
 - Exclusion constraints on partitioned tables.
 - Tables with a large number of partitions, and performance.
 - Standby or logical replication behaviour.
 - Windows and 32-bit builds.
 - A run of make check on plain master without the patch.
 - Builds with ICU (I used --without-icu).

Thanks,
Joao Rito






^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2026-11-16 14:02 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-30 06:40 BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le PG Bug reporting form <noreply@postgresql.org>
2026-11-01 06:23 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-02 02:40   ` Kirill Reshke <reshkekirill@gmail.com>
2026-11-02 12:46     ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-04 03:34       ` shihao zhong <zhong950419@gmail.com>
2026-11-05 09:28         ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-08 01:15           ` shihao zhong <zhong950419@gmail.com>
2026-11-16 14:02             ` Joao Rito <joaovictor.rito@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox