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

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().

Regards,
Chunling Qin