Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le

On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19817
> Logged by: chunling qin
> Email address: 303677365(at)qq(dot)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