From 1fcfa1d1a7ee03ae8591d04117bd8f13842c67e0 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 20 Aug 2025 15:25:35 +0800 Subject: [PATCH v52 1/1] misc minor fix * misc regress tests fix * check persistence before call heap_create_with_catalog in createPartitionTable --- src/backend/commands/tablecmds.c | 42 +++---- .../test_ddl_deparse/expected/alter_table.out | 5 + .../test_ddl_deparse/sql/alter_table.sql | 3 + src/test/regress/expected/partition_merge.out | 107 +++++------------- src/test/regress/sql/partition_merge.sql | 12 +- 5 files changed, 57 insertions(+), 112 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 079b1725cf1..1d35acf4e6f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -22498,6 +22498,27 @@ createPartitionTable(List **wqueue, RangeVar *newPartName, errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", newPartName->relname)); + /* + * 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 (parent_rel->rd_rel->relpersistence != RELPERSISTENCE_TEMP && + newPartName->relpersistence == RELPERSISTENCE_TEMP) + ereport(ERROR, + errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot create a temporary relation as partition of permanent relation \"%s\"", + RelationGetRelationName(parent_rel))); + + /* Permanent rels cannot be partitions belonging to temporary parent */ + if (newPartName->relpersistence != RELPERSISTENCE_TEMP && + parent_rel->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(parent_rel))); + /* Create the relation. */ newRelId = heap_create_with_catalog(newPartName->relname, namespaceId, @@ -22536,27 +22557,6 @@ createPartitionTable(List **wqueue, RangeVar *newPartName, /* Find or create work queue entry for newly created table. */ new_partrel_tab = ATGetQueueEntry(wqueue, newRel); - /* - * 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 (parent_rel->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(parent_rel))); - - /* Permanent rels cannot be partitions belonging to temporary parent */ - if (newRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP && - parent_rel->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(parent_rel))); - /* Create constraints, default values and generated values */ createTableConstraints(wqueue, new_partrel_tab, parent_rel, newRel); diff --git a/src/test/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out index 50d0354a341..c403c2f569f 100644 --- a/src/test/modules/test_ddl_deparse/expected/alter_table.out +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -56,6 +56,11 @@ ALTER TABLE part DETACH PARTITION part2; NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type DETACH PARTITION desc table part2 DROP TABLE part2; +CREATE TABLE part2 PARTITION OF part FOR VALUES FROM (100) to (200); +NOTICE: DDL test: type simple, tag CREATE TABLE +ALTER TABLE part MERGE PARTITIONS (part1, part2) INTO part1; +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type MERGE PARTITIONS desc ALTER TABLE part ADD PRIMARY KEY (a); NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint part_a_not_null on table part diff --git a/src/test/modules/test_ddl_deparse/sql/alter_table.sql b/src/test/modules/test_ddl_deparse/sql/alter_table.sql index 9ad1cf908d4..31e69100a30 100644 --- a/src/test/modules/test_ddl_deparse/sql/alter_table.sql +++ b/src/test/modules/test_ddl_deparse/sql/alter_table.sql @@ -35,6 +35,9 @@ ALTER TABLE part ATTACH PARTITION part2 FOR VALUES FROM (101) to (200); ALTER TABLE part DETACH PARTITION part2; DROP TABLE part2; +CREATE TABLE part2 PARTITION OF part FOR VALUES FROM (100) to (200); +ALTER TABLE part MERGE PARTITIONS (part1, part2) INTO part1; + ALTER TABLE part ADD PRIMARY KEY (a); ALTER TABLE parent ALTER COLUMN a SET NOT NULL; diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index 5159fd37d82..21a078f138e 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -254,95 +254,40 @@ INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VAL ('Manager1', 2022, 3, 3), ('Manager2', 2022, 3, 4), ('Manager3', 2022, 5, 1); -SELECT * FROM sales_date; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department - Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department - Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department - Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department - Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department - Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department - Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department - Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department - Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department +SELECT tableoid::regclass, * FROM sales_date; + tableoid | salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department +---------------+------------------+------------+-------------+-----------+------------+------------------ + sales_dec2022 | Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department + sales_dec2022 | Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department + sales_jan2022 | Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department + sales_jan2022 | Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department + sales_feb2022 | Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department + sales_feb2022 | Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department + sales_other | Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department + sales_other | Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department + sales_other | Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department (9 rows) -SELECT * FROM sales_dec2022; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department - Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department -(2 rows) - -SELECT * FROM sales_jan2022; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department - Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department -(2 rows) - -SELECT * FROM sales_feb2022; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department - Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department -(2 rows) - -SELECT * FROM sales_other; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department - Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department - Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department -(3 rows) - ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022; INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10), ('Manager2', 2022, 2, 10); -SELECT * FROM sales_date; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department - Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department - Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department - Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department - Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department - Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department - Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department - Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department - Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department - Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department - Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department +SELECT tableoid::regclass, * FROM sales_date; + tableoid | salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department +-------------------+------------------+------------+-------------+-----------+------------+------------------ + sales_dec2022 | Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department + sales_dec2022 | Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department + sales_jan_feb2022 | Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department + sales_jan_feb2022 | Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department + sales_jan_feb2022 | Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department + sales_jan_feb2022 | Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department + sales_jan_feb2022 | Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department + sales_jan_feb2022 | Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department + sales_other | Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department + sales_other | Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department + sales_other | Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department (11 rows) -SELECT * FROM sales_dec2022; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department - Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department -(2 rows) - -SELECT * FROM sales_jan_feb2022; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department - Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department - Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department - Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department - Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department - Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department -(6 rows) - -SELECT * FROM sales_other; - salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department -------------------+------------+-------------+-----------+------------+------------------ - Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department - Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department - Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department -(3 rows) - DROP TABLE sales_date; -- -- Test: merge partitions of partitioned table with triggers diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index bb5386fe381..fef59fbc5d5 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -187,11 +187,7 @@ INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VAL ('Manager2', 2022, 3, 4), ('Manager3', 2022, 5, 1); -SELECT * FROM sales_date; -SELECT * FROM sales_dec2022; -SELECT * FROM sales_jan2022; -SELECT * FROM sales_feb2022; -SELECT * FROM sales_other; +SELECT tableoid::regclass, * FROM sales_date; ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022; @@ -199,11 +195,7 @@ INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VAL ('Manager1', 2022, 1, 10), ('Manager2', 2022, 2, 10); -SELECT * FROM sales_date; -SELECT * FROM sales_dec2022; -SELECT * FROM sales_jan_feb2022; -SELECT * FROM sales_other; - +SELECT tableoid::regclass, * FROM sales_date; DROP TABLE sales_date; -- -- 2.34.1