From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
Date: Fri, 30 Oct 2026 06:40:13 +0000
Message-ID: <19817-4e72209979d7bdd5@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 303677365@qq.com
Subject: Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
In-Reply-To: <19817-4e72209979d7bdd5@postgresql.org>
References: <19817-4e72209979d7bdd5@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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