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

On Sun, 1 Nov 2026 at 11:23, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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

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