pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joao Rito <joaovictor.rito@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: shihao zhong <zhong950419@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: 303677365@qq.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19817: CREATE UNIQUE INDEX on a partitioned table can silently attach an invalid, not-ready child index, le
Date: Mon, 16 Nov 2026 11:02:39 -0300
Message-ID: <20261116140239.1145-1-joaovictor.rito@gmail.com> (raw)
In-Reply-To: <CAGRkXqSZ6nBK=y-NPjKvHS51BOquLUh1w9FoRmp-kOiimApwGQ@mail.gmail.com>
References: <19817-4e72209979d7bdd5@postgresql.org>
	<CAFiTN-vfNSWcGpO7tx5QpPNPd-KVohz01JVxk+GRCCPoa3j0pg@mail.gmail.com>
	<CALdSSPiK469Kzgp0j2OGW9Wi4DdEpUA9_=vD+p1GQU_1qciHgA@mail.gmail.com>
	<CAFiTN-usrrPD7JV7vsyW7rwtmu5nN_W7SnrsaVc-8WawWSNZ3w@mail.gmail.com>
	<CAGRkXqTJeih2Dh8X3tniW6v_u3drDwJLczbOHQ1h23jFWNAsLQ@mail.gmail.com>
	<CAFiTN-ujxmh89Y-Y6cufj-Dm_VgKKR9cvEBPPJ6-N1RTgfvd7A@mail.gmail.com>
	<CAGRkXqSZ6nBK=y-NPjKvHS51BOquLUh1w9FoRmp-kOiimApwGQ@mail.gmail.com>

Hi Dilip,

Thank you for the patch. I tested v3-0001 on current master. It
fixes the report for me. These are my results.

Setup: master at 7fc2f0423320 (2026-11-16), Debian, gcc 14,
configure with --enable-cassert --enable-debug
--enable-tap-tests --without-icu. I ran the cases on plain
master, then applied v3-0001, rebuilt, and ran them again.

Reproduction from the report (b2_root / b2_p1):

 - Without the patch, CREATE UNIQUE INDEX b2_root_ui returns
   CREATE INDEX. b2_p1_bad is attached to b2_root_ui with
   indisvalid = f and indisready = f, and b2_root_ui has
   indisvalid = f. The insert of (1,'x'),(1,'x') succeeds (3 rows
   with a = 1), and ON CONFLICT (a) fails with "there is no unique
   or exclusion constraint matching the ON CONFLICT
   specification".
 - With the patch, a new leaf index b2_p1_a_idx is built, it is
   attached, and b2_root_ui is valid. The duplicate insert fails
   with "duplicate key value violates unique constraint
   "b2_p1_a_idx"". ON CONFLICT (a) DO NOTHING works. The old
   b2_p1_bad stays behind as an invalid, unattached index, and
   it can be dropped.

Other cases, all with the patch. Shihao already confirmed
the PK case on v3; I list it for completeness:

 - A three-level tree (root, a partitioned mid level, two leaves
   under it, plus one leaf directly under root) with a leftover
   invalid index on one lower leaf. Both partitioned indexes are
   valid, and the leftover is not attached. The unique index
   rejects a duplicate on the two leaves I tried (the one with
   the leftover and its sibling).
 - ALTER TABLE ONLY s ADD PRIMARY KEY (a) on a partitioned
   sub-table, then ALTER TABLE r ADD PRIMARY KEY (a), and then
   ADD PRIMARY KEY on the leaf. No error. This is the case
   Shihao raised on v2 and confirmed on v3. The output is the
   same as on master.
 - ALTER TABLE ... ATTACH PARTITION with a leftover invalid
   index on the table being attached: master and patched give
   the same result (a new valid index is attached, the leftover
   stays out).
 - CREATE UNIQUE INDEX ... ON ONLY, CIC on each leaf, then
   ALTER INDEX ... ATTACH PARTITION: the parent becomes valid.
   CIC directly on the partitioned table still gives the same
   error as before.
 - A failed non-unique CIC (expression 10/a with a row a = 0),
   then CREATE INDEX on the parent: the invalid leaf index is
   skipped as well, and a new valid one is attached.
 - A leftover named s8_p1_a_idx (failed unique CIC), then
   CREATE UNIQUE INDEX on the parent: the new child is named
   s8_p1_a_idx1, so there is no name collision.
 - After removing the duplicate with the leftover still in
   place, REINDEX INDEX CONCURRENTLY on the leftover makes it
   valid, and a later CREATE UNIQUE INDEX on the parent attaches
   it. If the duplicate is still there, CREATE UNIQUE INDEX
   on the parent fails on the leaf and leaves nothing behind.
 - pg_dump of the patched result restores with no errors. The
   restored database has 2 invalid indexes, and the original
   has 7 (5 leftovers and 2 ON ONLY parents). I did not list
   the 2; I expect they are the ON ONLY parents from the
   primary key case. The duplicate insert fails in the
   restored database.
 - amcheck bt_index_check(..., heapallindexed) passes on the
   13 valid btree indexes from these cases.

Test suites with the patch:

 - make check: all 243 tests passed.
 - make -C src/test/isolation check: all 139 tests passed.
 - contrib/amcheck check: all 4 tests passed.
 - src/bin/pg_dump t/002_pg_dump.pl: 13361 tests, all passed.

The patch applies with git apply and "git diff --check" is
clean. The build has no warnings.

On the change: skipping only invalid indexes that are not
partitioned indexes looks right to me. It keeps the ON ONLY
partitioned indexes attachable, and it matches the ATTACH
PARTITION path, which already skips invalid indexes
(e90e9275f56). The new test expects errors in the right places
(the CIC on duplicate keys, the parent CREATE UNIQUE INDEX and
the duplicate insert). It uses a duplicate key, not the
expression index from v1 that Kirill found to be flaky. I ran
make check only once, so I cannot say more about flakiness.

Two small points, both optional:

 - The leftover invalid index stays in place, and the command
   does not say so. A NOTICE or a doc sentence that says to
   drop the leftover would help users who see two indexes on
   the same column. The new doc sentence, "Invalid indexes on
   leaf partitions are skipped.", could say "are skipped, not
   attached and not dropped".
 - The commit message says that the broken child "could no
   longer be dropped directly" once attached. I did not test
   that on the unpatched tree, so I take it from the message.

What I did not test:

 - Other branches (the report says 18.6 is affected too), so I
   cannot say how the patch looks as a backpatch.
 - Concurrent DDL or DML while CREATE INDEX recurses over the
   partitions.
 - Hash and list partitioned tables, non-btree access methods,
   partial indexes, and indexes with INCLUDE columns.
 - Exclusion constraints on partitioned tables.
 - Tables with a large number of partitions, and performance.
 - Standby or logical replication behaviour.
 - Windows and 32-bit builds.
 - A run of make check on plain master without the patch.
 - Builds with ICU (I used --without-icu).

Thanks,
Joao Rito






view thread (8+ messages)

Message-ID: <20261116140239.1145-1-joaovictor.rito@gmail.com>
Permalink:  ../20261116140239.1145-1-joaovictor.rito@gmail.com/
Also on:    postgresql.org/message-id/20261116140239.1145-1-joaovictor.rito@gmail.com

 · 

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: joaovictor.rito@gmail.com, dilipbalaut@gmail.com, zhong950419@gmail.com, reshkekirill@gmail.com, 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: <20261116140239.1145-1-joaovictor.rito@gmail.com>
  References: <19817-4e72209979d7bdd5@postgresql.org> <CAFiTN-vfNSWcGpO7tx5QpPNPd-KVohz01JVxk+GRCCPoa3j0pg@mail.gmail.com> <CALdSSPiK469Kzgp0j2OGW9Wi4DdEpUA9_=vD+p1GQU_1qciHgA@mail.gmail.com> <CAFiTN-usrrPD7JV7vsyW7rwtmu5nN_W7SnrsaVc-8WawWSNZ3w@mail.gmail.com> <CAGRkXqTJeih2Dh8X3tniW6v_u3drDwJLczbOHQ1h23jFWNAsLQ@mail.gmail.com> <CAFiTN-ujxmh89Y-Y6cufj-Dm_VgKKR9cvEBPPJ6-N1RTgfvd7A@mail.gmail.com> <CAGRkXqSZ6nBK=y-NPjKvHS51BOquLUh1w9FoRmp-kOiimApwGQ@mail.gmail.com> <20261116140239.1145-1-joaovictor.rito@gmail.com>

* 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