From e0b1f82bb0ce10e07db4316d1d81cdaeac6b29f2 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 22 Sep 2025 15:44:22 +0800 Subject: [PATCH v60 1/1] refactor v60 mainly refactor: partitions_lists_intersection check_parent_values_in_new_partitions regress test --- src/backend/partitioning/partbounds.c | 131 ++++++------ src/backend/utils/adt/ruleutils.c | 18 -- src/include/utils/ruleutils.h | 2 - src/test/regress/expected/partition_split.out | 188 +++++++----------- src/test/regress/sql/partition_split.sql | 104 ++++------ 5 files changed, 183 insertions(+), 260 deletions(-) diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c index 5f2ee6e02a7..8115caacc6a 100644 --- a/src/backend/partitioning/partbounds.c +++ b/src/backend/partitioning/partbounds.c @@ -5199,55 +5199,66 @@ calculate_partition_bound_for_merge(Relation parent, } /* - * partitions_lists_intersection + * partitions_listdatum_intersection * * (function for BY LIST partitioning) * * Function compares lists of values for different partitions. - * Return a list that contains all the cells that are in both list1 and + * Return a list that contains *one* cell that are in both list1 and * list2. The returned list is freshly allocated via palloc(), but the * cells themselves point to the same objects as the cells of the * input lists. + * + * Currently, there is no need to collect all common partition datums from the + * two lists. */ static List * -partitions_lists_intersection(FmgrInfo *partsupfunc, Oid *partcollation, - const List *list1, const List *list2) +partitions_listdatum_intersection(FmgrInfo *partsupfunc, Oid *partcollation, + const List *list1, const List *list2) { - List *result; - const ListCell *cell; + List *result = NIL; + bool isnull1 = false; + bool isnull2 = false; if (list1 == NIL || list2 == NIL) - return false; + return result; - result = NIL; - foreach(cell, list1) + foreach_node(Const, val1, list1) { - Const *val1 = lfirst_node(Const, cell); - - if (!val1->constisnull) + if (val1->constisnull) { - const ListCell *cell2; - - foreach(cell2, list2) + if (isnull2) { - Const *val2 = lfirst_node(Const, cell2); - - if (val2->constisnull) - continue; + result = lappend(result, val1); + return result; + } + isnull1 = true; + continue; + } - /* Compare two datums values. */ - if (DatumGetInt32(FunctionCall2Coll(&partsupfunc[0], - partcollation[0], - val1->constvalue, - val2->constvalue)) == 0) + foreach_node(Const, val2, list2) + { + if (val2->constisnull) + { + if (isnull1) { - result = lappend(result, lfirst(cell)); - break; + result = lappend(result, val1); + return result; } + isnull2 = true; + continue; + } + + /* Compare two datums values. */ + if (DatumGetInt32(FunctionCall2Coll(&partsupfunc[0], + partcollation[0], + val1->constvalue, + val2->constvalue)) == 0) + { + result = lappend(result, val1); + return result; } } - else if (list_member(list2, lfirst(cell))) - result = lappend(result, lfirst(cell)); } return result; @@ -5288,13 +5299,13 @@ check_partitions_not_overlap_list(Relation parent, { sps2 = parts[j]; - overlap = partitions_lists_intersection(&key->partsupfunc[0], - key->partcollation, - sps1->bound->listdatums, - sps2->bound->listdatums); + overlap = partitions_listdatum_intersection(&key->partsupfunc[0], + key->partcollation, + sps1->bound->listdatums, + sps2->bound->listdatums); if (list_length(overlap) > 0) { - Const *val = (Const *) lfirst(list_head(overlap)); + Const *val = (Const *) linitial_node(Const, overlap); ereport(ERROR, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -5410,7 +5421,7 @@ check_partition_bounds_for_split_range(Relation parent, relname, get_rel_name(splitPartOid)), errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", - "ALTER TABLE ... MERGE PARTITIONS"), + "ALTER TABLE ... SPLIT PARTITIONS"), parser_errposition(pstate, exprLocation((Node *) datum))); } else if (cmpval < 0) @@ -5420,7 +5431,7 @@ check_partition_bounds_for_split_range(Relation parent, relname, get_rel_name(splitPartOid)), errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", - "ALTER TABLE ... MERGE PARTITIONS"), + "ALTER TABLE ... SPLIT PARTITIONS"), parser_errposition(pstate, exprLocation((Node *) datum))); } else @@ -5452,7 +5463,7 @@ check_partition_bounds_for_split_range(Relation parent, relname, get_rel_name(splitPartOid)), errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", - "ALTER TABLE ... MERGE PARTITIONS"), + "ALTER TABLE ... SPLIT PARTITIONS"), parser_errposition(pstate, exprLocation((Node *) datum))); } else if (cmpval > 0) @@ -5462,7 +5473,7 @@ check_partition_bounds_for_split_range(Relation parent, relname, get_rel_name(splitPartOid)), errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", - "ALTER TABLE ... MERGE PARTITIONS"), + "ALTER TABLE ... SPLIT PARTITIONS"), parser_errposition(pstate, exprLocation((Node *) datum))); } } @@ -5632,7 +5643,6 @@ check_parent_values_in_new_partitions(Relation parent, PartitionBoundInfo boundinfo = partdesc->boundinfo; int i; bool found = true; - bool searchNull = false; Datum datum = PointerGetDatum(NULL); Assert(key->strategy == PARTITION_STRATEGY_LIST); @@ -5646,12 +5656,18 @@ check_parent_values_in_new_partitions(Relation parent, { if (!find_value_in_new_partitions_list(&key->partsupfunc[0], key->partcollation, parts, nparts, datum, true)) - { found = false; - searchNull = true; - } } + if (!found) + ereport(ERROR, + errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("new partitions combined partition bounds do not contain value (%s) but split partition \"%s\" does", + "NULL", + get_rel_name(partOid)), + errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", + "ALTER TABLE ... SPLIT PARTITIONS")); + /* * Search all values of split partition with partOid in PartitionDesc of * partitioned table. @@ -5675,24 +5691,25 @@ check_parent_values_in_new_partitions(Relation parent, { Const *notFoundVal; - if (!searchNull) - - /* - * Make Const for getting string representation of not found - * value. - */ - notFoundVal = makeConst(key->parttypid[0], - key->parttypmod[0], - key->parttypcoll[0], - key->parttyplen[0], - datum, - false, /* isnull */ - key->parttypbyval[0]); + /* + * Make Const for getting string representation of not found + * value. + */ + notFoundVal = makeConst(key->parttypid[0], + key->parttypmod[0], + key->parttypcoll[0], + key->parttyplen[0], + datum, + false, /* isnull */ + key->parttypbyval[0]); ereport(ERROR, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), - errmsg("new partitions do not have value %s but split partition does", - searchNull ? "NULL" : get_list_partvalue_string(notFoundVal))); + errmsg("new partitions combined partition bounds do not contain value (%s) but split partition \"%s\" does", + deparse_expression((Node *) notFoundVal, NIL, false, false), + get_rel_name(partOid)), + errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition", + "ALTER TABLE ... SPLIT PARTITIONS")); } } @@ -5712,7 +5729,7 @@ check_parent_values_in_new_partitions(Relation parent, * * parent: partitioned table * splitPartOid: split partition Oid - * list: list of new partitions + * partlist: list of new partitions after partition split * pstate: pointer to ParseState struct for determine error position */ void diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index c165fc6012e..0408a95941d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -13709,21 +13709,3 @@ get_range_partbound_string(List *bound_datums) return buf->data; } - -/* - * get_list_partvalue_string - * A C string representation of one list partition value - */ -char * -get_list_partvalue_string(Const *val) -{ - deparse_context context; - StringInfo buf = makeStringInfo(); - - memset(&context, 0, sizeof(deparse_context)); - context.buf = buf; - - get_const_expr(val, &context, -1); - - return buf->data; -} diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h index 726e418193f..7ba7d887914 100644 --- a/src/include/utils/ruleutils.h +++ b/src/include/utils/ruleutils.h @@ -54,6 +54,4 @@ extern char *get_range_partbound_string(List *bound_datums); extern char *pg_get_statisticsobjdef_string(Oid statextid); -extern char *get_list_partvalue_string(Const *val); - #endif /* RULEUTILS_H */ diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index 8c11c6c309c..70294286f38 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -11,7 +11,7 @@ SET search_path = partition_split_schema, public; -- -- Test for error codes -- -CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (salesperson_id int, sales_date date) PARTITION BY RANGE (sales_date); 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; @@ -57,7 +57,7 @@ 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 "sales_feb_mar_apr2022" LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-01-01') TO... ^ -HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -- ERROR: partition with name "sales_feb_mar_apr2022" is already used -- (We can create partition with the same name as split partition, but can't create two partitions with the same name) ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO @@ -100,7 +100,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0... ^ -HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -- ERROR: can not split to partition "sales_mar2022" together with partition "sales_feb2022" ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), @@ -122,7 +122,7 @@ 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 "sales_feb_mar_apr2022" LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-02') TO... ^ -HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -- Check the source partition not in the search path SET search_path = partition_split_schema2, public; ALTER TABLE partition_split_schema.sales_range @@ -132,13 +132,11 @@ SPLIT PARTITION partition_split_schema.sales_feb_mar_apr2022 INTO 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 -------------------+-----------------------+-----------+----------+---------+----------+--------------+------------- - salesperson_id | integer | | | | plain | | - salesperson_name | character varying(30) | | | | extended | | - sales_amount | integer | | | | plain | | - sales_date | date | | | | plain | | + Partitioned table "partition_split_schema.sales_range" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------------+---------+-----------+----------+---------+---------+--------------+------------- + salesperson_id | 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'), @@ -147,6 +145,21 @@ Partitions: partition_split_schema2.sales_apr2022 FOR VALUES FROM ('04-01-2022') DROP TABLE sales_range; DROP TABLE sales_others; +-- Additional tests for error messages, no default partition +CREATE TABLE sales_range (sales_date date) PARTITION BY RANGE (sales_date); +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'); +-- ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" +-- HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +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 sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01')); +ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" +LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0... + ^ +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +DROP TABLE sales_range; -- -- Add rows into partitioned table then split partition -- @@ -200,6 +213,21 @@ CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sale 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; +INSERT INTO sales_range VALUES + (1, 'May', 1000, '2022-01-31'), + (2, 'Smirnoff', 500, '2022-02-10'), + (3, 'Ford', 2000, '2022-04-30'), + (4, 'Ivanov', 750, '2022-04-13'), + (5, 'Deev', 250, '2022-04-07'), + (6, 'Poirot', 150, '2022-02-11'), + (7, 'Li', 175, '2022-03-08'), + (8, 'Ericsson', 185, '2022-02-23'), + (9, 'Muller', 250, '2022-03-11'), + (10, 'Halder', 350, '2022-01-28'), + (11, 'Trump', 380, '2022-04-06'), + (12, 'Plato', 350, '2022-03-19'), + (13, 'Gandi', 377, '2022-01-09'), + (14, 'Smith', 510, '2022-05-04'); -- Split partition, also check schema qualification of new partitions ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO (PARTITION partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), @@ -220,21 +248,6 @@ Partitions: partition_split_schema2.sales_mar2022 FOR VALUES FROM ('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'), - (2, 'Smirnoff', 500, '2022-02-10'), - (3, 'Ford', 2000, '2022-04-30'), - (4, 'Ivanov', 750, '2022-04-13'), - (5, 'Deev', 250, '2022-04-07'), - (6, 'Poirot', 150, '2022-02-11'), - (7, 'Li', 175, '2022-03-08'), - (8, 'Ericsson', 185, '2022-02-23'), - (9, 'Muller', 250, '2022-03-11'), - (10, 'Halder', 350, '2022-01-28'), - (11, 'Trump', 380, '2022-04-06'), - (12, 'Plato', 350, '2022-03-19'), - (13, 'Gandi', 377, '2022-01-09'), - (14, 'Smith', 510, '2022-05-04'); SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; tableoid | salesperson_id | salesperson_name | sales_amount | sales_date ---------------------------------------+----------------+------------------+--------------+------------ @@ -315,11 +328,6 @@ SELECT tableoid::regclass, * FROM sales_date ORDER BY tableoid::regclass::text C sales_other | Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department (11 rows) ---ERROR: relation "sales_jan_feb2022" does not exist -SELECT * FROM sales_jan_feb2022; -ERROR: relation "sales_jan_feb2022" does not exist -LINE 1: SELECT * FROM sales_jan_feb2022; - ^ DROP TABLE sales_date CASCADE; -- -- Test: split DEFAULT partition; use an index on partition key; check index after split @@ -460,7 +468,7 @@ DROP TABLE sales_range CASCADE; -- -- Test: some cases for splitting DEFAULT partition (different bounds) -- -CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (salesperson_id INT, sales_amount INT) PARTITION BY RANGE (sales_amount); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- sales_error intersects with sales_dec2021 (lower bound) -- ERROR: can not split to partition "sales_error" together with partition "sales_dec2021" @@ -532,7 +540,7 @@ CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20210101) TO (20210201), + PARTITION sales_noerror FOR VALUES FROM (20220101) TO (20220201), PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), PARTITION sales_others DEFAULT); DROP TABLE sales_range; @@ -710,28 +718,6 @@ CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2); CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHAR(30)); ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5); INSERT INTO salespeople (salesperson_name) VALUES ('Poirot'), ('Ivanov'); -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - --- Split partition has identity column: -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3), PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4), @@ -746,41 +732,26 @@ SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text salespeople4_5 | 4 | Ford (4 rows) -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - --- New partitions have identity-columns: -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) - -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid ORDER BY attnum; - attname | attidentity | attgenerated -------------------+-------------+-------------- - salesperson_id | a | - salesperson_name | | -(2 rows) +-- check new partitions have identity or not after split partition +SELECT attrelid::regclass, attname, attidentity, attgenerated FROM pg_attribute +WHERE attnum > 0 +AND attrelid::regclass IN ( + 'salespeople2_3'::regclass, 'salespeople', 'salespeople2_3', + 'salespeople1_2', 'salespeople3_4', 'salespeople4_5') +ORDER BY attrelid::regclass::text COLLATE "C", attnum; + attrelid | attname | attidentity | attgenerated +----------------+------------------+-------------+-------------- + salespeople | salesperson_id | a | + salespeople | salesperson_name | | + salespeople1_2 | salesperson_id | a | + salespeople1_2 | salesperson_name | | + salespeople2_3 | salesperson_id | a | + salespeople2_3 | salesperson_name | | + salespeople3_4 | salesperson_id | a | + salespeople3_4 | salesperson_name | | + salespeople4_5 | salesperson_id | a | + salespeople4_5 | salesperson_name | | +(10 rows) DROP TABLE salespeople CASCADE; -- @@ -892,13 +863,7 @@ DROP TABLE sales_range; -- -- Test: specific errors for BY LIST partitioning -- -CREATE TABLE sales_list -(salesperson_id INT, - salesperson_name VARCHAR(30), - sales_state VARCHAR(20), - sales_amount INT, - sales_date DATE) -PARTITION BY LIST (sales_state); +CREATE TABLE sales_list (sales_state VARCHAR(20)) PARTITION BY LIST (sales_state); CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki'); CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok'); CREATE TABLE sales_others PARTITION OF sales_list DEFAULT; @@ -960,13 +925,7 @@ DROP TABLE t; -- * new partitions do not have NULL value, which split partition has. -- * new partitions do not have a value that split partition has. -- -CREATE TABLE sales_list -(salesperson_id INT, - salesperson_name VARCHAR(30), - sales_state VARCHAR(20), - sales_amount INT, - sales_date DATE) -PARTITION BY LIST (sales_state); +CREATE TABLE sales_list(sales_state VARCHAR(20)) PARTITION BY LIST (sales_state); CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo'); CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok', NULL); -- ERROR: new partitions do not have value NULL but split partition does @@ -974,13 +933,15 @@ ALTER TABLE sales_list SPLIT PARTITION sales_all INTO (PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'), PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'), PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv')); -ERROR: new partitions do not have value NULL but split partition does +ERROR: new partitions combined partition bounds do not contain value (NULL) but split partition "sales_all" does +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -- ERROR: new partitions do not have value 'Kyiv' but split partition does ALTER TABLE sales_list SPLIT PARTITION sales_all INTO (PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'), PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'), PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', NULL)); -ERROR: new partitions do not have value 'Kyiv' but split partition does +ERROR: new partitions combined partition bounds do not contain value ('Kyiv'::character varying(20)) but split partition "sales_all" does +HINT: ALTER TABLE ... SPLIT PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -- ERROR DEFAULT partition should be one ALTER TABLE sales_list SPLIT PARTITION sales_all INTO (PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'), @@ -1511,21 +1472,6 @@ ALTER TABLE t SPLIT PARTITION tp1 INTO (PARTITION tp1_1 FOR VALUES WITH (MODULUS 4, REMAINDER 0)); ERROR: list of new partitions should contain at least two partitions DROP TABLE t; --- Additional tests for error messages -CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date); -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'); --- ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" --- HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -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 sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01')); -ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" -LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0... - ^ -HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -DROP TABLE sales_range; -- Test for split partition properties: -- * STATISTICS is empty -- * COMMENT is empty diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index 20342c4541d..8b0f67c5379 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -14,7 +14,7 @@ SET search_path = partition_split_schema, public; -- -- Test for error codes -- -CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (salesperson_id int, sales_date date) PARTITION BY RANGE (sales_date); 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; @@ -116,6 +116,20 @@ SET search_path = partition_split_schema, public; DROP TABLE sales_range; DROP TABLE sales_others; +-- Additional tests for error messages, no default partition +CREATE TABLE sales_range (sales_date date) PARTITION BY RANGE (sales_date); +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'); + +-- ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" +-- HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition +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 sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01')); + +DROP TABLE sales_range; + -- -- Add rows into partitioned table then split partition -- @@ -156,6 +170,22 @@ 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; +INSERT INTO sales_range VALUES + (1, 'May', 1000, '2022-01-31'), + (2, 'Smirnoff', 500, '2022-02-10'), + (3, 'Ford', 2000, '2022-04-30'), + (4, 'Ivanov', 750, '2022-04-13'), + (5, 'Deev', 250, '2022-04-07'), + (6, 'Poirot', 150, '2022-02-11'), + (7, 'Li', 175, '2022-03-08'), + (8, 'Ericsson', 185, '2022-02-23'), + (9, 'Muller', 250, '2022-03-11'), + (10, 'Halder', 350, '2022-01-28'), + (11, 'Trump', 380, '2022-04-06'), + (12, 'Plato', 350, '2022-03-19'), + (13, 'Gandi', 377, '2022-01-09'), + (14, 'Smith', 510, '2022-05-04'); + -- Split partition, also check schema qualification of new partitions ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO (PARTITION partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), @@ -163,22 +193,6 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO 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'), - (2, 'Smirnoff', 500, '2022-02-10'), - (3, 'Ford', 2000, '2022-04-30'), - (4, 'Ivanov', 750, '2022-04-13'), - (5, 'Deev', 250, '2022-04-07'), - (6, 'Poirot', 150, '2022-02-11'), - (7, 'Li', 175, '2022-03-08'), - (8, 'Ericsson', 185, '2022-02-23'), - (9, 'Muller', 250, '2022-03-11'), - (10, 'Halder', 350, '2022-01-28'), - (11, 'Trump', 380, '2022-04-06'), - (12, 'Plato', 350, '2022-03-19'), - (13, 'Gandi', 377, '2022-01-09'), - (14, 'Smith', 510, '2022-05-04'); - SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; DROP TABLE sales_range CASCADE; @@ -222,9 +236,6 @@ INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VAL SELECT tableoid::regclass, * FROM sales_date ORDER BY tableoid::regclass::text COLLATE "C", sales_year, sales_month, sales_day; ---ERROR: relation "sales_jan_feb2022" does not exist -SELECT * FROM sales_jan_feb2022; - DROP TABLE sales_date CASCADE; -- @@ -284,7 +295,7 @@ DROP TABLE sales_range CASCADE; -- -- Test: some cases for splitting DEFAULT partition (different bounds) -- -CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date); +CREATE TABLE sales_range (salesperson_id INT, sales_amount INT) PARTITION BY RANGE (sales_amount); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- sales_error intersects with sales_dec2021 (lower bound) @@ -341,7 +352,7 @@ CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; -- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2021 and sales_feb2022 ALTER TABLE sales_range SPLIT PARTITION sales_others INTO (PARTITION sales_dec2021 FOR VALUES FROM (20211201) TO (20220101), - PARTITION sales_noerror FOR VALUES FROM (20210101) TO (20210201), + PARTITION sales_noerror FOR VALUES FROM (20220101) TO (20220201), PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301), PARTITION sales_others DEFAULT); @@ -489,11 +500,6 @@ ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO ( INSERT INTO salespeople (salesperson_name) VALUES ('Poirot'), ('Ivanov'); -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum; -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum; --- Split partition has identity column: -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid ORDER BY attnum; - ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3), PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4), @@ -503,12 +509,13 @@ INSERT INTO salespeople (salesperson_name) VALUES ('May'), ('Ford'); SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum; -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum; --- New partitions have identity-columns: -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid ORDER BY attnum; -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid ORDER BY attnum; -SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid ORDER BY attnum; +-- check new partitions have identity or not after split partition +SELECT attrelid::regclass, attname, attidentity, attgenerated FROM pg_attribute +WHERE attnum > 0 +AND attrelid::regclass IN ( + 'salespeople2_3'::regclass, 'salespeople', 'salespeople2_3', + 'salespeople1_2', 'salespeople3_4', 'salespeople4_5') +ORDER BY attrelid::regclass::text COLLATE "C", attnum; DROP TABLE salespeople CASCADE; @@ -595,13 +602,7 @@ DROP TABLE sales_range; -- -- Test: specific errors for BY LIST partitioning -- -CREATE TABLE sales_list -(salesperson_id INT, - salesperson_name VARCHAR(30), - sales_state VARCHAR(20), - sales_amount INT, - sales_date DATE) -PARTITION BY LIST (sales_state); +CREATE TABLE sales_list (sales_state VARCHAR(20)) PARTITION BY LIST (sales_state); CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki'); CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok'); @@ -654,13 +655,7 @@ DROP TABLE t; -- * new partitions do not have NULL value, which split partition has. -- * new partitions do not have a value that split partition has. -- -CREATE TABLE sales_list -(salesperson_id INT, - salesperson_name VARCHAR(30), - sales_state VARCHAR(20), - sales_amount INT, - sales_date DATE) -PARTITION BY LIST (sales_state); +CREATE TABLE sales_list(sales_state VARCHAR(20)) PARTITION BY LIST (sales_state); CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo'); CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok', NULL); @@ -1048,21 +1043,6 @@ ALTER TABLE t SPLIT PARTITION tp1 INTO DROP TABLE t; --- Additional tests for error messages -CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date); -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'); - --- ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022" --- HINT: ALTER TABLE ... MERGE PARTITIONS require combined bounds of new partitions must exactly match the bound of the split partition -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 sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01')); - -DROP TABLE sales_range; - - -- Test for split partition properties: -- * STATISTICS is empty -- * COMMENT is empty -- 2.34.1