public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Michael Paquier <[email protected]>
Cc: Haibo Yan <[email protected]>
Cc: Mohamed ALi <[email protected]>
Cc: [email protected]
Subject: Re: [PATCH] Fix: Partitioned parent index remains invalid after child indexes are repaired
Date: Sat, 18 Apr 2026 09:37:48 -0500
Message-ID: <CAA5RZ0uM7_YPdSahGh1sGeewMb9SzKswyLaZty8aR+a7XpQydQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGnOmWqi1D9ycBgUeOGf6mOCd2Dcf=6sKhbf4sHLs5xAcKVCMQ@mail.gmail.com>
<CABXr29GbJe7-vbm0Fn=HWx3vQonwFtv_zO1pSc5FZAvjLuUDVA@mail.gmail.com>
<CABXr29Epj=23eYAWg_mTdiXYQQzJSVN22uk6tK8ei0ts5wXvSQ@mail.gmail.com>
<CAA5RZ0s16eHrzcDupipAxC++e-p_n_M7Fpg-MG-V59fB=wykLw@mail.gmail.com>
<[email protected]>
<[email protected]>
Hi,
> Could you write a patch? It would be better to have tests with
> multiple levels, at least, with a partitioned table being a leaf of
> another partitioned table. I am sure you get the picture, the point
> being to recurse across multiple levels.
Here is the patch with tests. It adds a test for this case using
multi-level partitions and ensures that the parent indexes are
validated once a child index is set to valid. Also, I added the
negative case where only one child index is validated to ensure
that the parent indexes remain invalid.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0001-Allow-ALTER-INDEX-ATTACH-PARTITION-to-validate-th.patch (8.8K, 2-v1-0001-Allow-ALTER-INDEX-ATTACH-PARTITION-to-validate-th.patch)
download | inline diff:
From ba7aa4dc0708b8cc4300264501df059709535164 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Sat, 18 Apr 2026 05:36:38 +0000
Subject: [PATCH v1 1/1] Allow ALTER INDEX ATTACH PARTITION to validate the
parent index
This causes ALTER INDEX ATTACH PARTITION to validate a parent
index in the case an index is already attached but the parent
is not yet valid. This occurs in cases where a parent index
was created invalid such as with CREATE INDEX ONLY, but was
left invalid after an invalid child index was attached. This
left a situation in which a user cannot bring the parent index
back to valid after fixing the child index.
An invalid parent index is more than just a passive issue, it
causes ON CONFLICT on a partitioned table if the invalid parent
index is used to enforce a unique constraint.
Discussion: http://postgr.es/m/CAGnOmWqi1D9ycBgUeOGf6mOCd2Dcf%3D6sKhbf4sHLs5xAcKVCMQ%40mail.gmail.com
---
src/backend/commands/tablecmds.c | 12 ++++-
src/test/regress/expected/indexing.out | 71 ++++++++++++++++++++++++++
src/test/regress/sql/indexing.sql | 49 ++++++++++++++++++
3 files changed, 131 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..8b46d9556e8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21921,7 +21921,9 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
ObjectAddressSet(address, RelationRelationId, RelationGetRelid(partIdx));
- /* Silently do nothing if already in the right state */
+ /*
+ * Check if the index is already attached to the correct parent.
+ */
currParent = partIdx->rd_rel->relispartition ?
get_partition_parent(partIdxId, false) : InvalidOid;
if (currParent != RelationGetRelid(parentIdx))
@@ -22030,6 +22032,14 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
validatePartitionedIndex(parentIdx, parentTbl);
}
+ else if (!parentIdx->rd_index->indisvalid)
+ {
+ /*
+ * The index is attached, but the parent is still invalid; see if it
+ * can be validated now.
+ */
+ validatePartitionedIndex(parentIdx, parentTbl);
+ }
relation_close(parentTbl, AccessShareLock);
/* keep these locks till commit */
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index f50868ca6a6..476bcc4878f 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -540,6 +540,77 @@ select relname, indisvalid from pg_class join pg_index on indexrelid = oid
idxpart_a_idx | t
(3 rows)
+drop table idxpart;
+-- Verify that re-attaching an already-attached partition index can
+-- validate the parent index if it was still invalid, including
+-- indirect ancestors in subpartitions.
+create table idxpart (a int, b int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (0) to (1000) partition by range (a);
+create table idxpart11 partition of idxpart1 for values from (0) to (500);
+-- create parent indexes
+create index on only idxpart ((a/b));
+create index on only idxpart1 ((a/b));
+-- fail, leaves behind an invalid index on the leaf partition
+insert into idxpart11 values (1, 0);
+create index concurrently on idxpart11 ((a/b));
+ERROR: division by zero
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+ relname | indisvalid
+--------------------+------------
+ idxpart11_expr_idx | f
+ idxpart1_expr_idx | f
+ idxpart_expr_idx | f
+(3 rows)
+
+-- attach the indexes; parents stay invalid
+alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+-- fix the index on the leaf partition
+delete from idxpart11 where b = 0;
+reindex index concurrently idxpart11_expr_idx;
+-- reattach the leaf partition index; parents should now be valid
+alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+ relname | indisvalid
+--------------------+------------
+ idxpart11_expr_idx | t
+ idxpart1_expr_idx | t
+ idxpart_expr_idx | t
+(3 rows)
+
+drop table idxpart;
+-- Verify that re-attaching does not validate the parent when another
+-- child index is still invalid.
+create table idxpart (a int, b int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (0) to (500);
+create table idxpart2 partition of idxpart for values from (500) to (1000);
+create index on only idxpart ((a/b));
+-- create invalid indexes on both children
+insert into idxpart1 values (1, 0);
+insert into idxpart2 values (501, 0);
+create index concurrently on idxpart1 ((a/b));
+ERROR: division by zero
+create index concurrently on idxpart2 ((a/b));
+ERROR: division by zero
+-- attach both; parent stays invalid
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+alter index idxpart_expr_idx attach partition idxpart2_expr_idx;
+-- fix only idxpart1's index, leave idxpart2's still invalid
+delete from idxpart1 where b = 0;
+reindex index concurrently idxpart1_expr_idx;
+-- re-attach the fixed child; parent should stay invalid
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+ relname | indisvalid
+-------------------+------------
+ idxpart1_expr_idx | t
+ idxpart2_expr_idx | f
+ idxpart_expr_idx | f
+(3 rows)
+
drop table idxpart;
-- verify dependency handling during ALTER TABLE DETACH PARTITION
create table idxpart (a int) partition by range (a);
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 129130d04d4..ba53c56fd9c 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -246,6 +246,55 @@ select relname, indisvalid from pg_class join pg_index on indexrelid = oid
where relname like 'idxpart%' order by relname;
drop table idxpart;
+-- Verify that re-attaching an already-attached partition index can
+-- validate the parent index if it was still invalid, including
+-- indirect ancestors in subpartitions.
+create table idxpart (a int, b int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (0) to (1000) partition by range (a);
+create table idxpart11 partition of idxpart1 for values from (0) to (500);
+-- create parent indexes
+create index on only idxpart ((a/b));
+create index on only idxpart1 ((a/b));
+-- fail, leaves behind an invalid index on the leaf partition
+insert into idxpart11 values (1, 0);
+create index concurrently on idxpart11 ((a/b));
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+-- attach the indexes; parents stay invalid
+alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+-- fix the index on the leaf partition
+delete from idxpart11 where b = 0;
+reindex index concurrently idxpart11_expr_idx;
+-- reattach the leaf partition index; parents should now be valid
+alter index idxpart1_expr_idx attach partition idxpart11_expr_idx;
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+drop table idxpart;
+
+-- Verify that re-attaching does not validate the parent when another
+-- child index is still invalid.
+create table idxpart (a int, b int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (0) to (500);
+create table idxpart2 partition of idxpart for values from (500) to (1000);
+create index on only idxpart ((a/b));
+-- create invalid indexes on both children
+insert into idxpart1 values (1, 0);
+insert into idxpart2 values (501, 0);
+create index concurrently on idxpart1 ((a/b));
+create index concurrently on idxpart2 ((a/b));
+-- attach both; parent stays invalid
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+alter index idxpart_expr_idx attach partition idxpart2_expr_idx;
+-- fix only idxpart1's index, leave idxpart2's still invalid
+delete from idxpart1 where b = 0;
+reindex index concurrently idxpart1_expr_idx;
+-- re-attach the fixed child; parent should stay invalid
+alter index idxpart_expr_idx attach partition idxpart1_expr_idx;
+select relname, indisvalid from pg_class join pg_index on indexrelid = oid
+ where relname like 'idxpart%' order by relname;
+drop table idxpart;
+
-- verify dependency handling during ALTER TABLE DETACH PARTITION
create table idxpart (a int) partition by range (a);
create table idxpart1 (like idxpart);
--
2.50.1 (Apple Git-155)
view thread (12+ messages) latest in thread
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Fix: Partitioned parent index remains invalid after child indexes are repaired
In-Reply-To: <CAA5RZ0uM7_YPdSahGh1sGeewMb9SzKswyLaZty8aR+a7XpQydQ@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox