pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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








view thread (8+ messages)  latest in thread

Message-ID: <19817-4e72209979d7bdd5@postgresql.org>
Permalink:  ../19817-4e72209979d7bdd5@postgresql.org/
Also on:    postgresql.org/message-id/19817-4e72209979d7bdd5@postgresql.org

 · 

reply

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