0001-Fix-ATTACH-PARTITION-to-ignore-invalid-indexes-as-ma.patch

From 3359ecbc304fecca924c637d56c105ef7e5a6497 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Mon, 26 Jun 2023 17:05:02 +0900
Subject: [PATCH] Fix ATTACH PARTITION to ignore invalid indexes as match

---
 src/backend/commands/tablecmds.c             |  8 +++--
 src/test/regress/expected/partition_info.out | 36 ++++++++++++++++++++
 src/test/regress/sql/partition_info.sql      | 20 +++++++++++
 3 files changed, 62 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9b12bc44d7..d985278ac6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18104,8 +18104,8 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
 
 		/*
 		 * Scan the list of existing indexes in the partition-to-be, and mark
-		 * the first matching, unattached one we find, if any, as partition of
-		 * the parent index.  If we find one, we're done.
+		 * the first matching, valid, unattached one we find, if any, as
+		 * partition of the parent index.  If we find one, we're done.
 		 */
 		for (i = 0; i < list_length(attachRelIdxs); i++)
 		{
@@ -18116,6 +18116,10 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
 			if (attachrelIdxRels[i]->rd_rel->relispartition)
 				continue;
 
+			/* If this index is invalid, can't use it */
+			if (!attachrelIdxRels[i]->rd_index->indisvalid)
+				continue;
+
 			if (CompareIndexInfo(attachInfos[i], info,
 								 attachrelIdxRels[i]->rd_indcollation,
 								 idxRel->rd_indcollation,
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index 42b6bc77ca..4ee51a2f73 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -349,3 +349,39 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+-- Check that invalid indexes are not selected when attaching a partition.
+CREATE TABLE ptif_inval_tab (a int) PARTITION BY RANGE (a);
+CREATE INDEX ptif_inval_idx ON ptif_inval_tab (a);
+CREATE TABLE ptif_inval_tab_1 (a int) PARTITION BY RANGE (a);
+CREATE TABLE ptif_inval_tab_1_1 PARTITION OF ptif_inval_tab_1
+  FOR VALUES FROM (0) TO (10);
+CREATE TABLE ptif_inval_tab_1_2 PARTITION OF ptif_inval_tab_1
+  FOR VALUES FROM (10) TO (20);
+-- This creates an invalid index.
+CREATE INDEX ptif_inval_ixd_1 ON ONLY ptif_inval_tab_1 (a);
+-- This creates new indexes for all the partitions of ptif_inval_tab_1,
+-- discarding the invalid index created previously as something to choose.
+ALTER TABLE ptif_inval_tab ATTACH PARTITION ptif_inval_tab_1
+  FOR VALUES FROM (1) to (100);
+SELECT indexrelid::regclass, indisvalid FROM pg_index
+  WHERE indexrelid::regclass::text ~ 'ptif_inval_'
+  ORDER BY indexrelid::regclass::text;
+        indexrelid        | indisvalid 
+--------------------------+------------
+ ptif_inval_idx           | t
+ ptif_inval_ixd_1         | f
+ ptif_inval_tab_1_1_a_idx | t
+ ptif_inval_tab_1_2_a_idx | t
+ ptif_inval_tab_1_a_idx   | t
+(5 rows)
+
+SELECT * FROM pg_partition_tree('ptif_inval_idx');
+          relid           |      parentrelid       | isleaf | level 
+--------------------------+------------------------+--------+-------
+ ptif_inval_idx           |                        | f      |     0
+ ptif_inval_tab_1_a_idx   | ptif_inval_idx         | f      |     1
+ ptif_inval_tab_1_1_a_idx | ptif_inval_tab_1_a_idx | t      |     2
+ ptif_inval_tab_1_2_a_idx | ptif_inval_tab_1_a_idx | t      |     2
+(4 rows)
+
+DROP TABLE ptif_inval_tab;
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index b5060bec7f..5d5f142728 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -127,3 +127,23 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+
+-- Check that invalid indexes are not selected when attaching a partition.
+CREATE TABLE ptif_inval_tab (a int) PARTITION BY RANGE (a);
+CREATE INDEX ptif_inval_idx ON ptif_inval_tab (a);
+CREATE TABLE ptif_inval_tab_1 (a int) PARTITION BY RANGE (a);
+CREATE TABLE ptif_inval_tab_1_1 PARTITION OF ptif_inval_tab_1
+  FOR VALUES FROM (0) TO (10);
+CREATE TABLE ptif_inval_tab_1_2 PARTITION OF ptif_inval_tab_1
+  FOR VALUES FROM (10) TO (20);
+-- This creates an invalid index.
+CREATE INDEX ptif_inval_ixd_1 ON ONLY ptif_inval_tab_1 (a);
+-- This creates new indexes for all the partitions of ptif_inval_tab_1,
+-- discarding the invalid index created previously as something to choose.
+ALTER TABLE ptif_inval_tab ATTACH PARTITION ptif_inval_tab_1
+  FOR VALUES FROM (1) to (100);
+SELECT indexrelid::regclass, indisvalid FROM pg_index
+  WHERE indexrelid::regclass::text ~ 'ptif_inval_'
+  ORDER BY indexrelid::regclass::text;
+SELECT * FROM pg_partition_tree('ptif_inval_idx');
+DROP TABLE ptif_inval_tab;
-- 
2.40.1