v60-0001-refactor-v60.no-cfbot
application/octet-stream
Filename: v60-0001-refactor-v60.no-cfbot
Type: application/octet-stream
Part: 0
From e0b1f82bb0ce10e07db4316d1d81cdaeac6b29f2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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