Make new partitions with parent's persistence during MERGE/SPLIT

The createPartitionTable() function is responsible for creating new partitions
for ALTER TABLE ... MERGE PARTITIONS, and ALTER TABLE ... SPLIT PARTITION
commands.  It emulates the behaviour of CREATE TABLE ... (LIKE ...), where
new table persistence should be specified by the user.  In the table
partitioning persistent of the partition and its parent must match.  So, this
commit makes createPartitionTable() copy the persistence of the parent
partition.

Also, this commit makes createPartitionTable() recheck the persistence after
the new table creation.  This is needed because persistence might be affected
by pg_temp in search_path.

This commit also changes the signature of createPartitionTable() making it
take the parent's Relation itself instead of the name of the parent relation,
and return the Relation of new partition.  That doesn't lead to
complications, because both callers have the parent table open and need to
open the new partition.

Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/dbc8b96c-3cf0-d1ee-860d-0e491da20485%40gmail.com
Author: Dmitry Koval
Reviewed-by: Alexander Korotkov, Robert Haas, Justin Pryzby, Pavel Borisov

5 files changed, 358 insertions, 57 deletions

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8fa09afdc59..c312d9d1975 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21209,18 +21209,30 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
 
 /*
  * createPartitionTable: create table for a new partition with given name
- * (newPartName) like table (modelRelName)
+ * (newPartName) like table (modelRel)
  *
- * Emulates command: CREATE TABLE <newPartName> (LIKE <modelRelName>
+ * Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
  * INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ * Function returns the created relation (locked in AccessExclusiveLock mode).
  */
-static void
-createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
+static Relation
+createPartitionTable(RangeVar *newPartName, Relation modelRel,
 					 AlterTableUtilityContext *context)
 {
 	CreateStmt *createStmt;
 	TableLikeClause *tlc;
 	PlannedStmt *wrapper;
+	Relation	newRel;
+
+	/* If existing rel is temp, it must belong to this session */
+	if (modelRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
+		!modelRel->rd_islocaltemp)
+		ereport(ERROR,
+				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				 errmsg("cannot create as partition of temporary relation of another session")));
+
+	/* New partition should have the same persistence as modelRel */
+	newPartName->relpersistence = modelRel->rd_rel->relpersistence;
 
 	createStmt = makeNode(CreateStmt);
 	createStmt->relation = newPartName;
@@ -21233,7 +21245,8 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
 	createStmt->if_not_exists = false;
 
 	tlc = makeNode(TableLikeClause);
-	tlc->relation = modelRelName;
+	tlc->relation = makeRangeVar(get_namespace_name(RelationGetNamespace(modelRel)),
+								 RelationGetRelationName(modelRel), -1);
 
 	/*
 	 * Indexes will be inherited on "attach new partitions" stage, after data
@@ -21259,6 +21272,35 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
 				   NULL,
 				   None_Receiver,
 				   NULL);
+
+	/*
+	 * Open the new partition with no lock, because we already have
+	 * AccessExclusiveLock placed there after creation.
+	 */
+	newRel = table_openrv(newPartName, NoLock);
+
+	/*
+	 * We intended to create the partition with the same persistence as the
+	 * parent table, but we still need to recheck because that might be
+	 * affected by the search_path.  If the parent is permanent, so must be
+	 * all of its partitions.
+	 */
+	if (modelRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+		newRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
+		ereport(ERROR,
+				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				 errmsg("cannot create a temporary relation as partition of permanent relation \"%s\"",
+						RelationGetRelationName(modelRel))));
+
+	/* Permanent rels cannot be partitions belonging to temporary parent */
+	if (newRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+		modelRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
+		ereport(ERROR,
+				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				 errmsg("cannot create a permanent relation as partition of temporary relation \"%s\"",
+						RelationGetRelationName(modelRel))));
+
+	return newRel;
 }
 
 /*
@@ -21278,7 +21320,6 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	char		tmpRelName[NAMEDATALEN];
 	List	   *newPartRels = NIL;
 	ObjectAddress object;
-	RangeVar   *parentName;
 	Oid			defaultPartOid;
 
 	defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
@@ -21350,18 +21391,12 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	}
 
 	/* Create new partitions (like split partition), without indexes. */
-	parentName = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
-							  RelationGetRelationName(rel), -1);
 	foreach(listptr, cmd->partlist)
 	{
 		SinglePartitionSpec *sps = (SinglePartitionSpec *) lfirst(listptr);
 		Relation	newPartRel;
 
-		createPartitionTable(sps->name, parentName, context);
-
-		/* Open the new partition and acquire exclusive lock on it. */
-		newPartRel = table_openrv(sps->name, AccessExclusiveLock);
-
+		newPartRel = createPartitionTable(sps->name, rel, context);
 		newPartRels = lappend(newPartRels, newPartRel);
 	}
 
@@ -21565,18 +21600,8 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
 	}
 
-	createPartitionTable(cmd->name,
-						 makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
-									  RelationGetRelationName(rel), -1),
-						 context);
-
-	/*
-	 * Open the new partition and acquire exclusive lock on it.  This will
-	 * stop all the operations with partitioned table.  This might seem
-	 * excessive, but this is the way we make sure nobody is planning queries
-	 * involving merging partitions.
-	 */
-	newPartRel = table_openrv(cmd->name, AccessExclusiveLock);
+	/* Create table for new partition, use partitioned table as model. */
+	newPartRel = createPartitionTable(cmd->name, rel, context);
 
 	/* Copy data from merged partitions to new partition. */
 	moveMergedTablesRows(rel, mergingPartitionsList, newPartRel);
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 2e0bfdc705d..3aae5f89e80 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -3,6 +3,7 @@
 -- Tests for "ALTER TABLE ... MERGE PARTITIONS ..." command
 --
 CREATE SCHEMA partitions_merge_schema;
+CREATE SCHEMA partitions_merge_schema2;
 SET search_path = partitions_merge_schema, public;
 --
 -- BY RANGE partitioning
@@ -36,18 +37,23 @@ ERROR:  lower bound of partition "sales_mar2022" conflicts with upper bound of p
 -- (space between sections sales_dec2021 and sales_jan2022)
 ALTER TABLE sales_range MERGE PARTITIONS (sales_dec2021, sales_jan2022, sales_feb2022) INTO sales_dec_jan_feb2022;
 ERROR:  lower bound of partition "sales_jan2022" conflicts with upper bound of previous partition "sales_dec2021"
--- NO ERROR: test for custom partitions order
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_jan_feb_mar2022;
+-- NO ERROR: test for custom partitions order, source partitions not in the search_path
+SET search_path = partitions_merge_schema2, public;
+ALTER TABLE partitions_merge_schema.sales_range MERGE PARTITIONS (
+  partitions_merge_schema.sales_feb2022,
+  partitions_merge_schema.sales_mar2022,
+  partitions_merge_schema.sales_jan2022) INTO sales_jan_feb_mar2022;
+SET search_path = partitions_merge_schema, public;
 SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
   FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
   WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass
   ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
-          oid          | relkind | inhdetachpending |                   pg_get_expr                    
------------------------+---------+------------------+--------------------------------------------------
- sales_apr2022         | p       | f                | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
- sales_dec2021         | r       | f                | FOR VALUES FROM ('12-01-2021') TO ('12-31-2021')
- sales_jan_feb_mar2022 | r       | f                | FOR VALUES FROM ('01-01-2022') TO ('04-01-2022')
- sales_others          | r       | f                | DEFAULT
+                      oid                       | relkind | inhdetachpending |                   pg_get_expr                    
+------------------------------------------------+---------+------------------+--------------------------------------------------
+ partitions_merge_schema2.sales_jan_feb_mar2022 | r       | f                | FOR VALUES FROM ('01-01-2022') TO ('04-01-2022')
+ sales_apr2022                                  | p       | f                | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
+ sales_dec2021                                  | r       | f                | FOR VALUES FROM ('12-01-2021') TO ('12-31-2021')
+ sales_others                                   | r       | f                | DEFAULT
 (4 rows)
 
 DROP TABLE sales_range;
@@ -95,23 +101,24 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
  sales_others  | r       | f                | DEFAULT
 (5 rows)
 
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022;
+-- check schema-qualified name of the new partition
+ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO partitions_merge_schema2.sales_feb_mar_apr2022;
 -- show partitions with conditions:
 SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
   FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
   WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass
   ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
-          oid          | relkind | inhdetachpending |                   pg_get_expr                    
------------------------+---------+------------------+--------------------------------------------------
- sales_feb_mar_apr2022 | r       | f                | FOR VALUES FROM ('02-01-2022') TO ('05-01-2022')
- sales_jan2022         | r       | f                | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
- sales_others          | r       | f                | DEFAULT
+                      oid                       | relkind | inhdetachpending |                   pg_get_expr                    
+------------------------------------------------+---------+------------------+--------------------------------------------------
+ partitions_merge_schema2.sales_feb_mar_apr2022 | r       | f                | FOR VALUES FROM ('02-01-2022') TO ('05-01-2022')
+ sales_jan2022                                  | r       | f                | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
+ sales_others                                   | r       | f                | DEFAULT
 (3 rows)
 
-SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema';
-       schemaname        |       tablename       |              indexname               | tablespace |                                                          indexdef                                                           
--------------------------+-----------------------+--------------------------------------+------------+-----------------------------------------------------------------------------------------------------------------------------
- partitions_merge_schema | sales_feb_mar_apr2022 | sales_feb_mar_apr2022_sales_date_idx |            | CREATE INDEX sales_feb_mar_apr2022_sales_date_idx ON partitions_merge_schema.sales_feb_mar_apr2022 USING btree (sales_date)
+SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema2';
+        schemaname        |       tablename       |              indexname               | tablespace |                                                           indexdef                                                           
+--------------------------+-----------------------+--------------------------------------+------------+------------------------------------------------------------------------------------------------------------------------------
+ partitions_merge_schema2 | sales_feb_mar_apr2022 | sales_feb_mar_apr2022_sales_date_idx |            | CREATE INDEX sales_feb_mar_apr2022_sales_date_idx ON partitions_merge_schema2.sales_feb_mar_apr2022 USING btree (sales_date)
 (1 row)
 
 SELECT * FROM sales_range;
@@ -141,7 +148,7 @@ SELECT * FROM sales_jan2022;
           13 | Gandi         |          377 | 01-09-2022
 (3 rows)
 
-SELECT * FROM sales_feb_mar_apr2022;
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022;
  salesman_id | salesman_name | sales_amount | sales_date 
 -------------+---------------+--------------+------------
            2 | Smirnoff      |          500 | 02-10-2022
@@ -164,7 +171,7 @@ SELECT * FROM sales_others;
 
 -- Use indexscan for testing indexes
 SET enable_seqscan = OFF;
-SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022 where sales_date > '2022-01-01';
  salesman_id | salesman_name | sales_amount | sales_date 
 -------------+---------------+--------------+------------
            2 | Smirnoff      |          500 | 02-10-2022
@@ -746,6 +753,34 @@ DROP TABLE t3;
 DROP TABLE t2;
 DROP TABLE t1;
 --
+-- Try to MERGE partitions of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TEMP TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+-- Partition should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_2 | FOR VALUES FROM (0) TO (2) | t
+(1 row)
+
+DROP TABLE t;
+--
 -- Check the partition index name if the partition name is the same as one
 -- of the merged partitions.
 --
@@ -771,4 +806,63 @@ Not-null constraints:
 
 DROP TABLE t;
 --
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = partitions_merge_schema, pg_temp, public;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+ oid | relpersistence 
+-----+----------------
+ t   | p
+(1 row)
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | p
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | p
+(2 rows)
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ERROR:  cannot create a temporary relation as partition of permanent relation "t"
+SET search_path = partitions_merge_schema, public;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+ERROR:  cannot create a temporary relation as partition of permanent relation "t"
+DROP TABLE t;
+SET search_path = pg_temp, partitions_merge_schema, public;
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+ oid | relpersistence 
+-----+----------------
+ t   | t
+(1 row)
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+SET search_path = partitions_merge_schema, pg_temp, public;
+-- Can't merge temporary partitions into a persistent partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ROLLBACK;
+RESET search_path;
+--
 DROP SCHEMA partitions_merge_schema;
+DROP SCHEMA partitions_merge_schema2;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 419d169f036..e2a362ffafd 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -3,6 +3,7 @@
 -- Tests for "ALTER TABLE ... SPLIT PARTITION ..." command
 --
 CREATE SCHEMA partition_split_schema;
+CREATE SCHEMA partition_split_schema2;
 SET search_path = partition_split_schema, public;
 --
 -- BY RANGE partitioning
@@ -104,6 +105,28 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
 ERROR:  lower bound of partition "sales_feb2022" is not equal to lower bound of split partition
 LINE 2:   (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-02') TO...
                                                     ^
+-- Check the source partition not in the search path
+SET search_path = partition_split_schema2, public;
+ALTER TABLE partition_split_schema.sales_range
+SPLIT PARTITION partition_split_schema.sales_feb_mar_apr2022 INTO
+  (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+   PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
+   PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+SET search_path = partition_split_schema, public;
+\d+ sales_range
+                             Partitioned table "partition_split_schema.sales_range"
+    Column     |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
+---------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
+ salesman_id   | integer               |           |          |         | plain    |              | 
+ salesman_name | character varying(30) |           |          |         | extended |              | 
+ sales_amount  | integer               |           |          |         | plain    |              | 
+ sales_date    | date                  |           |          |         | plain    |              | 
+Partition key: RANGE (sales_date)
+Partitions: partition_split_schema2.sales_apr2022 FOR VALUES FROM ('04-01-2022') TO ('05-01-2022'),
+            partition_split_schema2.sales_feb2022 FOR VALUES FROM ('02-01-2022') TO ('03-01-2022'),
+            partition_split_schema2.sales_mar2022 FOR VALUES FROM ('03-01-2022') TO ('04-01-2022'),
+            sales_jan2022 FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
+
 DROP TABLE sales_range;
 DROP TABLE sales_others;
 --
@@ -197,10 +220,26 @@ CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amou
 CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
 CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
 CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
+-- Split partition, also check schema qualification of new partitions
 ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
-  (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
-   PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
+  (PARTITION partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+   PARTITION partition_split_schema2.sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
    PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+\d+ sales_range
+                             Partitioned table "partition_split_schema.sales_range"
+    Column     |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
+---------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
+ salesman_id   | integer               |           |          |         | plain    |              | 
+ salesman_name | character varying(30) |           |          |         | extended |              | 
+ sales_amount  | integer               |           |          |         | plain    |              | 
+ sales_date    | date                  |           |          |         | plain    |              | 
+Partition key: RANGE (sales_date)
+Partitions: partition_split_schema2.sales_mar2022 FOR VALUES FROM ('03-01-2022') TO ('04-01-2022'),
+            sales_apr2022 FOR VALUES FROM ('04-01-2022') TO ('05-01-2022'),
+            sales_feb2022 FOR VALUES FROM ('02-01-2022') TO ('03-01-2022'),
+            sales_jan2022 FOR VALUES FROM ('01-01-2022') TO ('02-01-2022'),
+            sales_others DEFAULT
+
 INSERT INTO sales_range VALUES (1,  'May',      1000, '2022-01-31');
 INSERT INTO sales_range VALUES (2,  'Smirnoff', 500,  '2022-02-10');
 INSERT INTO sales_range VALUES (3,  'Ford',     2000, '2022-04-30');
@@ -250,7 +289,7 @@ SELECT * FROM sales_feb2022;
            8 | Ericsson      |          185 | 02-23-2022
 (3 rows)
 
-SELECT * FROM sales_mar2022;
+SELECT * FROM partition_split_schema2.sales_mar2022;
  salesman_id | salesman_name | sales_amount | sales_date 
 -------------+---------------+--------------+------------
            7 | Li            |          175 | 03-08-2022
@@ -1427,4 +1466,34 @@ ERROR:  relation "t1pa" is not a partition of relation "t2"
 DROP TABLE t2;
 DROP TABLE t1;
 --
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_2 | FOR VALUES FROM (0) TO (2) | t
+(1 row)
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+-- Partitions should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+  oid   |        pg_get_expr         | relpersistence 
+--------+----------------------------+----------------
+ tp_0_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+DROP TABLE t;
+--
 DROP SCHEMA partition_split_schema;
+DROP SCHEMA partition_split_schema2;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 72b1cb0b35e..5a69425d96e 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -4,6 +4,7 @@
 --
 
 CREATE SCHEMA partitions_merge_schema;
+CREATE SCHEMA partitions_merge_schema2;
 SET search_path = partitions_merge_schema, public;
 
 --
@@ -37,8 +38,13 @@ ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022) INTO sal
 -- (space between sections sales_dec2021 and sales_jan2022)
 ALTER TABLE sales_range MERGE PARTITIONS (sales_dec2021, sales_jan2022, sales_feb2022) INTO sales_dec_jan_feb2022;
 
--- NO ERROR: test for custom partitions order
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_jan_feb_mar2022;
+-- NO ERROR: test for custom partitions order, source partitions not in the search_path
+SET search_path = partitions_merge_schema2, public;
+ALTER TABLE partitions_merge_schema.sales_range MERGE PARTITIONS (
+  partitions_merge_schema.sales_feb2022,
+  partitions_merge_schema.sales_mar2022,
+  partitions_merge_schema.sales_jan2022) INTO sales_jan_feb_mar2022;
+SET search_path = partitions_merge_schema, public;
 
 SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
   FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
@@ -81,7 +87,8 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
   WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass
   ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
 
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022;
+-- check schema-qualified name of the new partition
+ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO partitions_merge_schema2.sales_feb_mar_apr2022;
 
 -- show partitions with conditions:
 SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
@@ -89,17 +96,17 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
   WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass
   ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
 
-SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema';
+SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema2';
 
 SELECT * FROM sales_range;
 SELECT * FROM sales_jan2022;
-SELECT * FROM sales_feb_mar_apr2022;
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022;
 SELECT * FROM sales_others;
 
 -- Use indexscan for testing indexes
 SET enable_seqscan = OFF;
 
-SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022 where sales_date > '2022-01-01';
 
 RESET enable_seqscan;
 
@@ -445,6 +452,28 @@ DROP TABLE t2;
 DROP TABLE t1;
 
 --
+-- Try to MERGE partitions of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TEMP TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+
+-- Partition should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+DROP TABLE t;
+
+--
 -- Check the partition index name if the partition name is the same as one
 -- of the merged partitions.
 --
@@ -463,4 +492,51 @@ ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
 DROP TABLE t;
 
 --
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = partitions_merge_schema, pg_temp, public;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+
+SET search_path = partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+DROP TABLE t;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
+CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c WHERE c.oid = 't'::regclass;
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+SET search_path = partitions_merge_schema, pg_temp, public;
+
+-- Can't merge temporary partitions into a persistent partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ROLLBACK;
+
+RESET search_path;
+
+--
 DROP SCHEMA partitions_merge_schema;
+DROP SCHEMA partitions_merge_schema2;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index b63532ee562..b4430133522 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -4,6 +4,7 @@
 --
 
 CREATE SCHEMA partition_split_schema;
+CREATE SCHEMA partition_split_schema2;
 SET search_path = partition_split_schema, public;
 
 --
@@ -92,6 +93,16 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
    PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
    PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
 
+-- Check the source partition not in the search path
+SET search_path = partition_split_schema2, public;
+ALTER TABLE partition_split_schema.sales_range
+SPLIT PARTITION partition_split_schema.sales_feb_mar_apr2022 INTO
+  (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+   PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
+   PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+SET search_path = partition_split_schema, public;
+\d+ sales_range
+
 DROP TABLE sales_range;
 DROP TABLE sales_others;
 
@@ -140,10 +151,12 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01
 CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
 CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
 
+-- Split partition, also check schema qualification of new partitions
 ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
-  (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
-   PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
+  (PARTITION partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+   PARTITION partition_split_schema2.sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
    PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+\d+ sales_range
 
 INSERT INTO sales_range VALUES (1,  'May',      1000, '2022-01-31');
 INSERT INTO sales_range VALUES (2,  'Smirnoff', 500,  '2022-02-10');
@@ -163,7 +176,7 @@ INSERT INTO sales_range VALUES (14, 'Smith',    510,  '2022-05-04');
 SELECT * FROM sales_range;
 SELECT * FROM sales_jan2022;
 SELECT * FROM sales_feb2022;
-SELECT * FROM sales_mar2022;
+SELECT * FROM partition_split_schema2.sales_mar2022;
 SELECT * FROM sales_apr2022;
 SELECT * FROM sales_others;
 
@@ -845,4 +858,28 @@ DROP TABLE t2;
 DROP TABLE t1;
 
 --
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+  (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+   PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+
+-- Partitions should be temporary.
+SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relpersistence
+  FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
+  WHERE c.oid = i.inhrelid AND i.inhparent = 't'::regclass
+  ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+
+DROP TABLE t;
+
+--
 DROP SCHEMA partition_split_schema;
+DROP SCHEMA partition_split_schema2;