From 17ed8a55544c0cba0b77ef81f75f14326ebe9a6c Mon Sep 17 00:00:00 2001 From: jian he 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