v46-0001-regress-test-refactoring-and-others.no-cfbot
application/octet-stream
Filename: v46-0001-regress-test-refactoring-and-others.no-cfbot
Type: application/octet-stream
Part: 0
From 17ed8a55544c0cba0b77ef81f75f14326ebe9a6c Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 17 Jun 2025 15:47:11 +0800
Subject: [PATCH v46 1/1] regress test refactoring and others
main changes:
1. regress test refactoring
2. not export function StoreConstraints, using AddRelationNewConstraints
---
src/backend/catalog/heap.c | 4 +-
src/backend/commands/tablecmds.c | 36 +-
src/include/catalog/heap.h | 3 -
src/test/regress/expected/partition_merge.out | 327 ++++++------------
src/test/regress/sql/partition_merge.sql | 84 ++---
5 files changed, 163 insertions(+), 291 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 571b6b05749..54c95e42598 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -102,6 +102,8 @@ static ObjectAddress AddNewRelationType(const char *typeName,
Oid new_row_type,
Oid new_array_type);
static void RelationRemoveInheritance(Oid relid);
+static void StoreConstraints(Relation rel, List *cooked_constraints,
+ bool is_internal);
static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_enforced, bool is_validated, bool is_local,
int16 inhcount, bool is_no_inherit, bool is_internal);
@@ -2294,7 +2296,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
* expressions can be added later, by direct calls to StoreAttrDefault
* and StoreRelCheck (see AddRelationNewConstraints()).
*/
-void
+static void
StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
{
int numchecks = 0;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9a9053d3252..b724e2d7150 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22227,6 +22227,7 @@ createTableConstraints(List **wqueue, AlteredTableInfo *tab,
AttrMap *attmap;
AttrNumber parent_attno;
int ccnum;
+ List *Constraints = NIL;
List *cookedConstraints = NIL;
tupleDesc = RelationGetDescr(modelRel);
@@ -22319,7 +22320,7 @@ createTableConstraints(List **wqueue, AlteredTableInfo *tab,
bool ccvalid = constr->check[ccnum].ccvalid;
Node *ccbin_node;
bool found_whole_row;
- CookedConstraint *cooked;
+ Constraint *constr;
/*
* Partitioned table can not have NO INHERIT check constraint (see
@@ -22346,22 +22347,27 @@ createTableConstraints(List **wqueue, AlteredTableInfo *tab,
ccname,
RelationGetRelationName(modelRel)));
- cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
- cooked->contype = CONSTR_CHECK;
- cooked->conoid = InvalidOid;
- cooked->name = ccname;
- cooked->attnum = 0;
- cooked->expr = ccbin_node;
- cooked->is_enforced = ccenforced;
- cooked->skip_validation = !ccvalid;
- cooked->is_local = true;
- cooked->inhcount = 0;
- cooked->is_no_inherit = ccnoinherit;
- cookedConstraints = lappend(cookedConstraints, cooked);
+ constr = makeNode(Constraint);
+ constr->contype = CONSTR_CHECK;
+ constr->conname = pstrdup(ccname);
+ constr->deferrable = false;
+ constr->initdeferred = false;
+ constr->is_enforced = ccenforced;
+ constr->skip_validation = !ccvalid;
+ constr->initially_valid = ccvalid;
+ constr->is_no_inherit = ccnoinherit;
+ constr->raw_expr = NULL;
+ constr->cooked_expr = nodeToString(ccbin_node);
+ constr->location = -1;
+ Constraints = lappend(Constraints, constr);
}
- /* Store CHECK constraints. */
- StoreConstraints(newRel, cookedConstraints, true);
+ /* install all CHECK constraints. */
+ cookedConstraints = AddRelationNewConstraints(newRel, NIL, Constraints,
+ false, true, true, NULL);
+
+ /* Make the additional catalog changes visible */
+ CommandCounterIncrement();
/*
* modelRel check constraint expresssion may reference tableoid, so later in
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index c130bc0c38d..dbd339e9df4 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -119,9 +119,6 @@ extern List *AddRelationNotNullConstraints(Relation rel,
List *constraints,
List *old_notnulls);
-extern void StoreConstraints(Relation rel, List *cooked_constraints,
- bool is_internal);
-
extern void RelationClearMissing(Relation rel);
extern void StoreAttrMissingVal(Relation rel, AttrNumber attnum,
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 1cd34fae2d5..f1a6b4b1b25 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -45,6 +45,9 @@ ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, partitio
ERROR: partition with name "sales_feb2022" is already used
LINE 1: ...e MERGE PARTITIONS (sales_feb2022, sales_mar2022, partitions...
^
+--ERROR, sales_apr_2 already exists
+ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_apr_2;
+ERROR: relation "sales_apr_2" already exists
-- 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 (
@@ -52,16 +55,23 @@ ALTER TABLE partitions_merge_schema.sales_range MERGE PARTITIONS (
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
-------------------------------------------------+---------+------------------+--------------------------------------------------
- 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
+PREPARE get_partition_info(regclass[]) AS
+SELECT c.oid::pg_catalog.regclass,
+ c.relpersistence,
+ c.relkind,
+ i.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 = ANY($1)
+ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',
+ c.oid::regclass::text COLLATE "C";
+EXECUTE get_partition_info('{sales_range}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+------------------------------------------------+----------------+---------+------------------+--------------------------------------------------
+ partitions_merge_schema2.sales_jan_feb_mar2022 | p | r | f | FOR VALUES FROM ('01-01-2022') TO ('04-01-2022')
+ sales_apr2022 | p | p | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
+ sales_dec2021 | p | r | f | FOR VALUES FROM ('12-01-2021') TO ('12-31-2021')
+ sales_others | p | r | f | DEFAULT
(4 rows)
DROP TABLE sales_range;
@@ -97,31 +107,25 @@ SELECT pg_catalog.pg_get_partkeydef('sales_range'::regclass);
(1 row)
-- 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_apr2022 | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
- sales_feb2022 | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022')
- sales_jan2022 | r | f | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
- sales_mar2022 | r | f | FOR VALUES FROM ('03-01-2022') TO ('04-01-2022')
- sales_others | r | f | DEFAULT
+EXECUTE get_partition_info('{sales_range}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+---------------+----------------+---------+------------------+--------------------------------------------------
+ sales_apr2022 | p | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
+ sales_feb2022 | p | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022')
+ sales_jan2022 | p | r | f | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
+ sales_mar2022 | p | r | f | FOR VALUES FROM ('03-01-2022') TO ('04-01-2022')
+ sales_others | p | r | f | DEFAULT
(5 rows)
-- 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
-------------------------------------------------+---------+------------------+--------------------------------------------------
- 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
+EXECUTE get_partition_info('{sales_range}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+------------------------------------------------+----------------+---------+------------------+--------------------------------------------------
+ partitions_merge_schema2.sales_feb_mar_apr2022 | p | r | f | FOR VALUES FROM ('02-01-2022') TO ('05-01-2022')
+ sales_jan2022 | p | r | f | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
+ sales_others | p | r | f | DEFAULT
(3 rows)
SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema2';
@@ -130,54 +134,25 @@ SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemanam
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;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid, salesperson_id;
+ tableoid | salesperson_id | salesperson_name | sales_amount | sales_date
+------------------------------------------------+----------------+------------------+--------------+------------
+ sales_jan2022 | 1 | May | 1000 | 01-31-2022
+ sales_jan2022 | 10 | Halder | 350 | 01-28-2022
+ sales_jan2022 | 13 | Gandi | 377 | 01-09-2022
+ sales_others | 14 | Smith | 510 | 05-04-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 2 | Smirnoff | 500 | 02-10-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 3 | Ford | 2000 | 04-30-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 4 | Ivanov | 750 | 04-13-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 5 | Deev | 250 | 04-07-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 6 | Poirot | 150 | 02-11-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 7 | Li | 175 | 03-08-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 8 | Ericsson | 185 | 02-23-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 9 | Muller | 250 | 03-11-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 11 | Trump | 380 | 04-06-2022
+ partitions_merge_schema2.sales_feb_mar_apr2022 | 12 | Plato | 350 | 03-19-2022
(14 rows)
-SELECT * FROM sales_jan2022;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
-(3 rows)
-
-SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
-(10 rows)
-
-SELECT * FROM sales_others;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
-(1 row)
-
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022 where sales_date > '2022-01-01';
@@ -226,28 +201,25 @@ INSERT INTO sales_range VALUES
-- name
ALTER TABLE sales_range MERGE PARTITIONS
(sales_jan2022, sales_mar2022, partitions_merge_schema.sales_others) INTO sales_others;
-select * from sales_others;
+SELECT * FROM sales_others ORDER BY salesperson_id;
salesperson_id | salesperson_name | sales_amount | sales_date
----------------+------------------+--------------+------------
1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
7 | Li | 175 | 03-08-2022
9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
12 | Plato | 350 | 03-19-2022
+ 13 | Gandi | 377 | 01-09-2022
14 | Smith | 510 | 05-04-2022
(7 rows)
-- 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_apr2022 | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
- sales_feb2022 | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022')
- sales_others | r | f | DEFAULT
+EXECUTE get_partition_info('{sales_range}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+---------------+----------------+---------+------------------+--------------------------------------------------
+ sales_apr2022 | p | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022')
+ sales_feb2022 | p | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022')
+ sales_others | p | r | f | DEFAULT
(3 rows)
DROP TABLE sales_range;
@@ -503,89 +475,23 @@ INSERT INTO sales_range VALUES
(12, 'Plato', 350, '2022-03-19'),
(13, 'Gandi', 377, '2022-01-09'),
(14, 'Smith', 510, '2022-05-04');
-SELECT * FROM sales_range;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
-(14 rows)
-
-SELECT * FROM sales_apr2022;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+SELECT tableoid::regclass, * FROM sales_apr2022;
+ tableoid | salesperson_id | salesperson_name | sales_amount | sales_date
+---------------------+----------------+------------------+--------------+------------
+ sales_apr2022_01_10 | 5 | Deev | 250 | 04-07-2022
+ sales_apr2022_01_10 | 11 | Trump | 380 | 04-06-2022
+ sales_apr2022_10_20 | 4 | Ivanov | 750 | 04-13-2022
+ sales_apr2022_20_30 | 3 | Ford | 2000 | 04-30-2022
(4 rows)
-SELECT * FROM sales_apr2022_01_10;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
-(2 rows)
-
-SELECT * FROM sales_apr2022_10_20;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
-(1 row)
-
-SELECT * FROM sales_apr2022_20_30;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
-(1 row)
-
ALTER TABLE sales_apr2022 MERGE PARTITIONS (sales_apr2022_01_10, sales_apr2022_10_20, sales_apr2022_20_30) INTO sales_apr_all;
-SELECT * FROM sales_range;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
-(14 rows)
-
-SELECT * FROM sales_apr2022;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
-(4 rows)
-
-SELECT * FROM sales_apr_all;
- salesperson_id | salesperson_name | sales_amount | sales_date
-----------------+------------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+SELECT tableoid::regclass, * FROM sales_apr2022 ORDER BY tableoid, salesperson_id;
+ tableoid | salesperson_id | salesperson_name | sales_amount | sales_date
+---------------+----------------+------------------+--------------+------------
+ sales_apr_all | 3 | Ford | 2000 | 04-30-2022
+ sales_apr_all | 4 | Ivanov | 750 | 04-13-2022
+ sales_apr_all | 5 | Deev | 250 | 04-07-2022
+ sales_apr_all | 11 | Trump | 380 | 04-06-2022
(4 rows)
DROP TABLE sales_range;
@@ -661,30 +567,24 @@ INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date)
('Gandi', 'Warsaw', 150, '2022-03-08'),
('Plato', 'Lisbon', 950, '2022-03-05');
-- 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_list'::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_central | r | f | FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv')
- sales_east | r | f | FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok')
- sales_nord | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki')
- sales_west | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid')
- sales_others | r | f | DEFAULT
+EXECUTE get_partition_info('{sales_list}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+---------------+----------------+---------+------------------+------------------------------------------------------
+ sales_central | p | r | f | FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv')
+ sales_east | p | r | f | FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok')
+ sales_nord | p | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki')
+ sales_west | p | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid')
+ sales_others | p | r | f | DEFAULT
(5 rows)
ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) INTO sales_all;
-- 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_list'::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_all | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid', 'Bejing', 'Delhi', 'Vladivostok', 'Warsaw', 'Berlin', 'Kyiv')
- sales_nord | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki')
- sales_others | r | f | DEFAULT
+EXECUTE get_partition_info('{sales_list}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+--------------+----------------+---------+------------------+--------------------------------------------------------------------------------------------------------------
+ sales_all | p | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid', 'Bejing', 'Delhi', 'Vladivostok', 'Warsaw', 'Berlin', 'Kyiv')
+ sales_nord | p | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki')
+ sales_others | p | r | f | DEFAULT
(3 rows)
SELECT * FROM sales_list;
@@ -779,25 +679,19 @@ DROP TABLE t1;
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
+EXECUTE get_partition_info('{t}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+--------+----------------+---------+------------------+----------------------------
+ tp_0_1 | t | r | f | FOR VALUES FROM (0) TO (1)
+ tp_1_2 | t | r | f | FOR VALUES FROM (1) TO (2)
(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
+EXECUTE get_partition_info('{t}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+--------+----------------+---------+------------------+----------------------------
+ tp_0_2 | t | r | f | FOR VALUES FROM (0) TO (2)
(1 row)
DROP TABLE t;
@@ -839,14 +733,11 @@ SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c W
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
+EXECUTE get_partition_info('{t}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+--------+----------------+---------+------------------+----------------------------
+ tp_0_1 | p | r | f | FOR VALUES FROM (0) TO (1)
+ tp_1_2 | p | r | f | FOR VALUES FROM (1) TO (2)
(2 rows)
SET search_path = pg_temp, partitions_merge_schema, public;
@@ -869,16 +760,14 @@ SELECT c.oid::pg_catalog.regclass, c.relpersistence FROM pg_catalog.pg_class c W
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
+EXECUTE get_partition_info('{t}');
+ oid | relpersistence | relkind | inhdetachpending | pg_get_expr
+--------+----------------+---------+------------------+----------------------------
+ tp_0_1 | t | r | f | FOR VALUES FROM (0) TO (1)
+ tp_1_2 | t | r | f | FOR VALUES FROM (1) TO (2)
(2 rows)
+DEALLOCATE get_partition_info;
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;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 5baba56392f..320e1c3e8df 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -39,6 +39,8 @@ ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022) INTO sal
ALTER TABLE sales_range MERGE PARTITIONS (sales_dec2021, sales_jan2022, sales_feb2022) INTO sales_dec_jan_feb2022;
-- ERROR: partition with name "sales_feb2022" is already used
ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, partitions_merge_schema.sales_feb2022) INTO sales_feb_mar_apr2022;
+--ERROR, sales_apr_2 already exists
+ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_apr_2;
-- NO ERROR: test for custom partitions order, source partitions not in the search_path
SET search_path = partitions_merge_schema2, public;
@@ -48,10 +50,18 @@ ALTER TABLE partitions_merge_schema.sales_range MERGE PARTITIONS (
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;
+PREPARE get_partition_info(regclass[]) AS
+SELECT c.oid::pg_catalog.regclass,
+ c.relpersistence,
+ c.relkind,
+ i.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 = ANY($1)
+ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',
+ c.oid::regclass::text COLLATE "C";
+
+EXECUTE get_partition_info('{sales_range}');
DROP TABLE sales_range;
@@ -85,26 +95,17 @@ INSERT INTO sales_range VALUES
SELECT pg_catalog.pg_get_partkeydef('sales_range'::regclass);
-- 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;
+EXECUTE get_partition_info('{sales_range}');
-- 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;
+EXECUTE get_partition_info('{sales_range}');
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 partitions_merge_schema2.sales_feb_mar_apr2022;
-SELECT * FROM sales_others;
+SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid, salesperson_id;
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
@@ -147,13 +148,10 @@ INSERT INTO sales_range VALUES
ALTER TABLE sales_range MERGE PARTITIONS
(sales_jan2022, sales_mar2022, partitions_merge_schema.sales_others) INTO sales_others;
-select * from sales_others;
+SELECT * FROM sales_others ORDER BY salesperson_id;
-- 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;
+EXECUTE get_partition_info('{sales_range}');
DROP TABLE sales_range;
@@ -323,17 +321,11 @@ INSERT INTO sales_range VALUES
(13, 'Gandi', 377, '2022-01-09'),
(14, 'Smith', 510, '2022-05-04');
-SELECT * FROM sales_range;
-SELECT * FROM sales_apr2022;
-SELECT * FROM sales_apr2022_01_10;
-SELECT * FROM sales_apr2022_10_20;
-SELECT * FROM sales_apr2022_20_30;
+SELECT tableoid::regclass, * FROM sales_apr2022;
ALTER TABLE sales_apr2022 MERGE PARTITIONS (sales_apr2022_01_10, sales_apr2022_10_20, sales_apr2022_20_30) INTO sales_apr_all;
-SELECT * FROM sales_range;
-SELECT * FROM sales_apr2022;
-SELECT * FROM sales_apr_all;
+SELECT tableoid::regclass, * FROM sales_apr2022 ORDER BY tableoid, salesperson_id;
DROP TABLE sales_range;
@@ -415,18 +407,12 @@ INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date)
('Plato', 'Lisbon', 950, '2022-03-05');
-- 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_list'::regclass
- ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+EXECUTE get_partition_info('{sales_list}');
ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) INTO sales_all;
-- 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_list'::regclass
- ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
+EXECUTE get_partition_info('{sales_list}');
SELECT * FROM sales_list;
SELECT * FROM sales_nord;
@@ -468,18 +454,12 @@ 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;
+EXECUTE get_partition_info('{t}');
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;
+EXECUTE get_partition_info('{t}');
DROP TABLE t;
@@ -510,10 +490,8 @@ 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;
+
+EXECUTE get_partition_info('{t}');
SET search_path = pg_temp, partitions_merge_schema, public;
@@ -534,10 +512,10 @@ 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;
+
+EXECUTE get_partition_info('{t}');
+
+DEALLOCATE get_partition_info;
SET search_path = partitions_merge_schema, pg_temp, public;
--
2.34.1