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@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
On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
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 specificationExpected: 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
On Sun, 1 Nov 2026 at 11:23, Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote: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 specificationExpected: 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
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
On Mon, Nov 2, 2026 at 8:10 AM Kirill Reshke <reshkekirill@gmail.com> wrote:
On Sun, 1 Nov 2026 at 11:23, Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Fri, Oct 30, 2026 at 12:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote: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 specificationExpected: 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 KumarHi!
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 msfails 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
Thanks for catching this. In parallel check, a concurrent session
holding an older snapshot causes heapam_index_build_range_scan() to
see the deleted (1, 0) row as HEAPTUPLE_RECENTLY_DEAD, so
FormIndexDatum() still evaluates (a/b) on it, whereas unique-checking
is skipped for RECENTLY_DEAD tuples, So IMHO we can keep unique
checking test only as that will also test what we intend to test here
and remove the divide by zero test from here?
Attached is v2, which uses the failed concurrent unique index build
(duplicate keys) for that test instead of the division-by-zero
expression index so that RECENTLY_DEAD tuples do not cause spurious
failures.
--
Regards,
Dilip Kumar
Google
Hi Dilip,
Thanks for working on this! v2 looks great!
I have one concern here:
This patch skips partitioned indexes made with ON ONLY, which are invalid
until their partitions are attached. This breaks a case that works on
master:
create table r (a int not null) partition by range (a);
create table s partition of r for values from (0) to (100)
partition by range (a);
create table s1 partition of s for values from (0) to (100);
alter table only s add primary key (a);
alter table r add primary key (a);
With v2 the last command fails:
ERROR: multiple primary keys for table "s" are not allowed
Maybe skip only invalid indexes that are not partitioned?
Thanks,
Shihao
On Wed, Nov 4, 2026 at 9:04 AM shihao zhong <zhong950419@gmail.com> wrote:
Hi Dilip,
Thanks for working on this! v2 looks great!
I have one concern here:
This patch skips partitioned indexes made with ON ONLY, which are invalid
until their partitions are attached. This breaks a case that works on
master:create table r (a int not null) partition by range (a);
create table s partition of r for values from (0) to (100)
partition by range (a);
create table s1 partition of s for values from (0) to (100);
alter table only s add primary key (a);
alter table r add primary key (a);With v2 the last command fails:
ERROR: multiple primary keys for table "s" are not allowed
Hmm, thats doesn't look good
Maybe skip only invalid indexes that are not partitioned?
Yeah that seems like a right way to fix this. How about this?
--
Regards,
Dilip Kumar
Google
Hi Dilip,
Thanks for v3. I tested it on current master.
The ON ONLY cases from my last mail work now, and the original
bug is fixed. A duplicate row is caught after a failed CREATE
UNIQUE INDEX CONCURRENTLY, and a good leaf index is built once the
duplicates are gone.
everything is good! +1 from me.
Thanks,
Shihao
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