public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: Dmitry Koval <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Date: Mon, 22 Apr 2024 13:31:48 +0300
Message-ID: <CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com> (raw)
In-Reply-To: <CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAMbWs49c_7dT_oiP729b-JPRiDtTuDca3exN_cOZZwjGG-0bMA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfdv1ZkY9cPOqsLvDY9AA-fCYKHM4P07+-Mu+xq5o=daS6w@mail.gmail.com>
<CA+TgmoYcjL+w2BQzku5iNXKR5fyxJMSP3avQta8xngioTX7D7A@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfdv-uR663BO6G2kRuLEQjob+UBHuD3iyXF+qGHXQFRN0-Q@mail.gmail.com>
<[email protected]>
<CAPpHfdvShLh5txtQdh6=ZMCnzGZHv8W+RHAe8pY94rfzghWTAg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAPpHfdtLk5M4FV6C33DnBusWpsbwwmrQ83mpu7N8PZMupbftyw@mail.gmail.com>
Hi!
On Fri, Apr 19, 2024 at 4:29 PM Alexander Korotkov <[email protected]> wrote:
> On Fri, Apr 19, 2024 at 2:26 AM Dmitry Koval <[email protected]> wrote:
> > 18.04.2024 19:00, Alexander Lakhin wrote:
> > > leaves a strange constraint:
> > > \d+ t*
> > > Table "public.tp_0"
> > > ...
> > > Not-null constraints:
> > > "merge-16385-26BCB0-tmp_i_not_null" NOT NULL "i"
> >
> > Thanks!
> > Attached fix (with test) for this case.
> > The patch should be applied after patches
> > v6-0001- ... .patch ... v6-0004- ... .patch
>
> I've incorporated this fix with 0001 patch.
>
> Also added to the patchset
> 005 – tab completion by Dagfinn [1]
> 006 – draft fix for table AM issue spotted by Alexander Lakhin [2]
> 007 – doc review by Justin [3]
>
> I'm continuing work on this.
>
> Links
> 1. https://www.postgresql.org/message-id/87plumiox2.fsf%40wibble.ilmari.org
> 2. https://www.postgresql.org/message-id/84ada05b-be5c-473e-6d1c-ebe5dd21b190%40gmail.com
> 3. https://www.postgresql.org/message-id/ZiGH0xc1lxJ71ZfB%40pryzbyj2023
0001
The way we handle name collisions during MERGE PARTITIONS operation is
reworked by integration of patch [3]. This makes note about commit in
[2] not relevant.
0002
The persistence of the new partition is copied as suggested in [1].
But the checks are in-place, because search_path could influence new
table persistence. Per review [2], commit message typos are fixed,
documentation is revised, revised tests to cover schema-qualification,
usage of search_path.
0003
Making code more clear that we're not going to dereference the NULL
datum per note in [2].
0004
Gender-neutral terms are used per suggestions in [2].
0005
Commit message revised
0006
Revise documentation mentioning we're going to copy the parent's table
AM. Regression tests are added. Commit message revised.
0007
Commit message revised
Links
1. https://www.postgresql.org/message-id/CA%2BTgmoYcjL%2Bw2BQzku5iNXKR5fyxJMSP3avQta8xngioTX7D7A%40mail...
2. https://www.postgresql.org/message-id/CA%2BTgmoY_4r6BeeSCTim04nAiCmmXg-1pG1toxQovZOP2qaFJ0A%40mail.g...
3. https://www.postgresql.org/message-id/f8b5cbf5-965e-4e5b-b506-33bbf41b0d50%40postgrespro.ru
------
Regards,
Alexander Korotkov
Attachments:
[application/octet-stream] v8-0003-Fix-error-message-in-check_partition_bounds_for_s.patch (4.0K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/2-v8-0003-Fix-error-message-in-check_partition_bounds_for_s.patch)
download | inline diff:
From f1b75b45cb6c7f0070344f4d4e3d7b14b7792733 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 18 Apr 2024 12:56:51 +0300
Subject: [PATCH v8 3/7] Fix error message in
check_partition_bounds_for_split_range()
Currently, the error message is produced by a system of complex substitutions
making it quite untranslatable and hard to read. This commit splits this into
4 plain error messages suitable for translation.
Reported-by: Kyotaro Horiguchi
Discussion: https://postgr.es/m/20240408.152402.1485994009160660141.horikyota.ntt%40gmail.com
---
src/backend/partitioning/partbounds.c | 71 +++++++++++++++++++--------
1 file changed, 50 insertions(+), 21 deletions(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index b08edf87a69..2fb39e3d006 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5211,7 +5211,7 @@ check_partition_bounds_for_split_range(Relation parent,
if (first || last)
{
PartitionBoundSpec *split_spec = get_partition_bound_spec(splitPartOid, splitPartName);
- bool overlap = false;
+ PartitionRangeDatum *datum;
if (first)
{
@@ -5229,8 +5229,30 @@ check_partition_bounds_for_split_range(Relation parent,
* Lower bound of "spec" should be equal (or greater than or equal
* in case defaultPart=true) to lower bound of split partition.
*/
- if ((!defaultPart && cmpval) || (defaultPart && cmpval < 0))
- overlap = true;
+ if (!defaultPart)
+ {
+ if (cmpval != 0)
+ {
+ datum = list_nth(spec->lowerdatums, abs(cmpval) - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("lower bound of partition \"%s\" is not equal to lower bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ }
+ else
+ {
+ if (cmpval < 0)
+ {
+ datum = list_nth(spec->lowerdatums, abs(cmpval) - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("lower bound of partition \"%s\" is less than lower bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ }
}
else
{
@@ -5243,29 +5265,36 @@ check_partition_bounds_for_split_range(Relation parent,
key->partcollation,
upper->datums, upper->kind,
false, split_upper);
+ datum = cmpval ? list_nth(spec->lowerdatums, abs(cmpval) - 1) : NULL;
/*
* Upper bound of "spec" should be equal (or less than or equal in
* case defaultPart=true) to upper bound of split partition.
*/
- if ((!defaultPart && cmpval) || (defaultPart && cmpval > 0))
- overlap = true;
- }
-
- if (overlap)
- {
- PartitionRangeDatum *datum;
-
- datum = list_nth(first ? spec->lowerdatums : spec->upperdatums, abs(cmpval) - 1);
-
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("%s bound of partition \"%s\" is %s %s bound of split partition",
- first ? "lower" : "upper",
- relname,
- defaultPart ? (first ? "less than" : "greater than") : "not equal to",
- first ? "lower" : "upper"),
- parser_errposition(pstate, datum->location)));
+ if (!defaultPart)
+ {
+ if (cmpval != 0)
+ {
+ datum = list_nth(spec->upperdatums, abs(cmpval) - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("upper bound of partition \"%s\" is not equal to upper bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ }
+ else
+ {
+ if (cmpval > 0)
+ {
+ datum = list_nth(spec->upperdatums, abs(cmpval) - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition",
+ relname),
+ parser_errposition(pstate, datum->location)));
+ }
+ }
}
}
}
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch (205.0K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/3-v8-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch)
download | inline diff:
From 3b0d557c29c15ded94908114ed7ab1fe7cc1ab75 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 22 Apr 2024 11:31:57 +0300
Subject: [PATCH v8 4/7] Rename tables in tests of partition MERGE/SPLIT
operations
Replace "salesman" with "salesperson", "salesmen" with "salespeople". The
names are both gramatically correct and gender-neutral.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/fdaa003e-919c-cbc9-4f0c-e4546e96bd65%40gmail.com
Reviewed-by: Robert Haas
---
src/test/regress/expected/partition_merge.out | 666 ++++-----
src/test/regress/expected/partition_split.out | 1266 ++++++++---------
src/test/regress/sql/partition_merge.sql | 160 +--
src/test/regress/sql/partition_split.sql | 280 ++--
4 files changed, 1186 insertions(+), 1186 deletions(-)
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 3aae5f89e80..a92a270c591 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -11,12 +11,12 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -60,7 +60,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -122,68 +122,68 @@ SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemanam
(1 row)
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ 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;
- salesman_id | salesman_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
+ 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;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ 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';
- salesman_id | salesman_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
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ 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
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(10 rows)
RESET enable_seqscan;
@@ -191,7 +191,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -216,15 +216,15 @@ INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
-- name
ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022, sales_others) INTO sales_others;
select * from sales_others;
- salesman_id | salesman_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
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ 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
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(7 rows)
-- show partitions with conditions:
@@ -246,7 +246,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -255,219 +255,219 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1);
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_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
+ 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
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_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
+ 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;
- salesman_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
+ 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;
- salesman_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
+ 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;
- salesman_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
+ 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(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_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
+ 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
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_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
+ 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;
- salesman_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
+ 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;
- salesman_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
+ 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
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
-select * from salesmans;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
+select * from salespeople;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(6 rows)
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+select * from salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(5 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -489,88 +489,88 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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;
- salesman_id | salesman_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
+ 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_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ 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;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ 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;
- salesman_id | salesman_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
+ 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;
- salesman_id | salesman_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
+ 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;
- salesman_id | salesman_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
+ 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)
DROP TABLE sales_range;
@@ -581,8 +581,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -614,33 +614,33 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
CREATE TABLE sales_west PARTITION OF sales_list FOR VALUES IN ('Lisbon', 'New York', 'Madrid');
CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok');
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('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
@@ -669,68 +669,68 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
(3 rows)
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(14 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_all;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(10 rows)
-- Use indexscan for testing indexes after merging partitions
SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
RESET enable_seqscan;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index e2a362ffafd..55ae37ad370 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 (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -85,8 +85,8 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
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 greater than upper bound of split partition
-LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
- ^
+LINE 4: PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO...
+ ^
-- ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of previous 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'),
@@ -114,13 +114,13 @@ 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
----------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
- salesman_id | integer | | | | plain | |
- salesman_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 | |
+ salesperson_name | character varying(30) | | | | extended | |
+ sales_amount | 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'),
@@ -132,7 +132,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -155,68 +155,68 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ 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 sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ 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
(3 rows)
SELECT * FROM sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -226,13 +226,13 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION partition_split_schema2.sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
\d+ sales_range
- Partitioned table "partition_split_schema.sales_range"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
- salesman_id | integer | | | | plain | |
- salesman_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 | |
+ salesperson_name | character varying(30) | | | | extended | |
+ sales_amount | integer | | | | plain | |
+ sales_date | date | | | | plain | |
Partition key: RANGE (sales_date)
Partitions: partition_split_schema2.sales_mar2022 FOR VALUES FROM ('03-01-2022') TO ('04-01-2022'),
sales_apr2022 FOR VALUES FROM ('04-01-2022') TO ('05-01-2022'),
@@ -255,61 +255,61 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ 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 sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ 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
(3 rows)
SELECT * FROM partition_split_schema2.sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
@@ -319,7 +319,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -327,103 +327,103 @@ CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month
CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1) TO (2022, 1, 1);
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_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
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | 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
+ 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
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | 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
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_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
+ 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;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
(4 rows)
SELECT * FROM sales_other;
- salesman_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
+ 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 SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_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 | 1 | 10 | 2022.01.10 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | 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
+ 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 | 1 | 10 | 2022.01.10 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | 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
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_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
+ 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;
- salesman_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 | 1 | 10 | 2022.01.10 | Sales department
+ 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 | 1 | 10 | 2022.01.10 | Sales department
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_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
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ 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
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
(3 rows)
SELECT * FROM sales_other;
- salesman_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
+ 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)
--ERROR: relation "sales_jan_feb2022" does not exist
@@ -435,7 +435,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -454,19 +454,19 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 6 | Poirot | 150 | 02-11-2022
- 7 | Li | 175 | 03-08-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 11 | Trump | 380 | 04-06-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 7 | Li | 175 | 03-08-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 11 | Trump | 380 | 04-06-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(11 rows)
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
@@ -484,34 +484,34 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_feb2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ 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
(3 rows)
SELECT * FROM sales_mar2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_others where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -544,7 +544,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
-- ERROR: lower bound of partition "sales_error" conflicts with upper bound of previous partition "sales_dec2022"
@@ -601,7 +601,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301),
PARTITION sales_others DEFAULT);
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
@@ -613,20 +613,20 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
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;
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
@@ -635,50 +635,50 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
-- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
DETAIL: Failing row contains (1, 0, 03-11-2022).
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(-1) is not present in table "salesmans".
+ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(-1) is not present in table "salespeople".
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
INSERT INTO sales VALUES (10, 150, '2022-03-01');
@@ -687,280 +687,280 @@ INSERT INTO sales VALUES (19, 200, '2022-03-04');
INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(40) is not present in table "salesmans".
+ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(40) is not present in table "salespeople".
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
-INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+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');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ 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 = 'salesmans2_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+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),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
+SELECT * FROM salespeople1_2;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans2_3;
- salesman_id | salesman_name
--------------+---------------
- 2 | Ivanov
+SELECT * FROM salespeople2_3;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 2 | Ivanov
(1 row)
-SELECT * FROM salesmans3_4;
- salesman_id | salesman_name
--------------+---------------
- 3 | May
+SELECT * FROM salespeople3_4;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 3 | May
(1 row)
-SELECT * FROM salesmans4_5;
- salesman_id | salesman_name
--------------+---------------
- 4 | Ford
+SELECT * FROM salespeople4_5;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 4 | Ford
(1 row)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ 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 = 'salesmans2_3'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
+select * from salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
(2 rows)
-select * from salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+select * from salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-select * from salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+select * from salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -980,31 +980,31 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
@@ -1012,50 +1012,50 @@ ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
PARTITION sales_apr2022_10_20 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20'),
PARTITION sales_apr2022_20_30 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_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
+ 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;
- salesman_id | salesman_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
+ 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_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ 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;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
(1 row)
DROP TABLE sales_range;
@@ -1066,8 +1066,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1106,8 +1106,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1131,109 +1131,109 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (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');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
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'));
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(14 rows)
SELECT * FROM sales_west;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(4 rows)
SELECT * FROM sales_east;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
(2 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_central;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(4 rows)
-- Use indexscan for testing indexes after splitting partition
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -1244,7 +1244,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
INSERT INTO sales_range VALUES (2, 'Smirnoff', 500, '2022-02-09');
@@ -1267,42 +1267,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ 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_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(8 rows)
DROP TABLE sales_range;
@@ -1311,7 +1311,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-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');
@@ -1334,42 +1334,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ 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_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
(8 rows)
DROP TABLE sales_range;
@@ -1377,7 +1377,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -1399,56 +1399,56 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_others DEFAULT);
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_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
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ 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
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ 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 sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ 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
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(4 rows)
DROP TABLE sales_range;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 5a69425d96e..085c422d540 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -14,13 +14,13 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -56,7 +56,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -115,7 +115,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -158,7 +158,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -169,15 +169,15 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1)
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -187,8 +187,8 @@ SELECT * FROM sales_other;
ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022;
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -200,14 +200,14 @@ DROP TABLE sales_date;
--
-- Test: merge partitions of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -216,80 +216,80 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-select * from salesmans;
-select * from salesmans01_10;
-select * from salesmans10_40;
+select * from salespeople;
+select * from salespeople01_10;
+select * from salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -336,8 +336,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -373,14 +373,14 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
@@ -389,20 +389,20 @@ CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi'
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('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)
@@ -427,7 +427,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
RESET enable_seqscan;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index b4430133522..3a7f2f9c294 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 (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -109,7 +109,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -146,7 +146,7 @@ DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -188,7 +188,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -198,15 +198,15 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -217,8 +217,8 @@ ALTER TABLE sales_date SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -234,7 +234,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -285,7 +285,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
@@ -335,7 +335,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
@@ -350,11 +350,11 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
sales_date DATE) PARTITION BY RANGE (sales_date);
@@ -376,28 +376,28 @@ SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conre
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
@@ -408,36 +408,36 @@ INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -446,125 +446,125 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
+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 salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
+INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- Split partition has identity column:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
+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),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
-SELECT * FROM salesmans2_3;
-SELECT * FROM salesmans3_4;
-SELECT * FROM salesmans4_5;
+SELECT * FROM salespeople1_2;
+SELECT * FROM salespeople2_3;
+SELECT * FROM salespeople3_4;
+SELECT * FROM salespeople4_5;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- New partitions have identity-columns:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
-select * from salesmans10_20;
-select * from salesmans20_30;
-select * from salesmans30_40;
+select * from salespeople01_10;
+select * from salespeople10_20;
+select * from salespeople20_30;
+select * from salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -611,8 +611,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -648,8 +648,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -676,34 +676,34 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (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');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
@@ -722,7 +722,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
SET enable_indexscan = ON;
SET enable_seqscan = ON;
@@ -734,7 +734,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -772,7 +772,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -809,7 +809,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+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_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0002-Make-new-partitions-with-parent-s-persistence-dur.patch (37.6K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/4-v8-0002-Make-new-partitions-with-parent-s-persistence-dur.patch)
download | inline diff:
From f10a3a01abbd782d5ae49b4da819c0100490fb90 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 18 Apr 2024 12:41:09 +0300
Subject: [PATCH v8 2/7] Make new partitions with parent's persistence during
MERGE/SPLIT operations
The createPartitionTable() function is responsible for creating new partitions
for ALTER TABLE ... MERGE PARTITIONS, and ALTER TABLE ... SPLIT PARTITION
commands. It emulates the behaviour of CREATE TABLE ... (LIKE ...), where
new table persistence should be specified by the user. In the table
partitioning persistent of the partition and its parent must match. So, this
commit makes createPartitionTable() copy the persistence of the parent
partition.
Also, this commit makes createPartitionTable() recheck the persistence after
the new table creation. This is needed because persistence might be affected
by pg_temp in search_path.
This commit also changes the signature of createPartitionTable() making it
take the parent's Relation itself instead of the name of the parent relation,
and return the Relation of new partition. That doesn't lead to
complications, because both callers have the parent table open and need to
open the new partition.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/dbc8b96c-3cf0-d1ee-860d-0e491da20485%40gmail.com
Author: Dmitry Koval
Reviewed-by: Alexander Korotkov, Robert Haas
---
doc/src/sgml/ref/alter_table.sgml | 21 +++
src/backend/commands/tablecmds.c | 75 ++++++----
src/test/regress/expected/partition_merge.out | 134 +++++++++++++++---
src/test/regress/expected/partition_split.out | 75 +++++++++-
src/test/regress/sql/partition_merge.sql | 88 +++++++++++-
src/test/regress/sql/partition_split.sql | 43 +++++-
6 files changed, 379 insertions(+), 57 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fe36ff82e52..a793899b69b 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1153,6 +1153,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
splitting we have a partition with the same name).
Only simple, non-partitioned partition can be split.
</para>
+ <para>
+ Any indexes, constraints and user-defined row-level triggers that exist
+ in the parent table are cloned on new partitions
+ <replaceable class="parameter">partition_name1</replaceable>,
+ <replaceable class="parameter">partition_name2</replaceable>, ... .
+ New partitions will have the same column names and types as the
+ partitioned table to which they belong.
+ If the parent table is persistent then new partitions are created
+ persistent. If the parent table is temporary then new partitions
+ are also created temporary.
+ </para>
<note>
<para>
This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock.
@@ -1213,6 +1224,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
can have the same name as one of the merged partitions. Only simple,
non-partitioned partitions can be merged.
</para>
+ <para>
+ Any indexes, constraints and user-defined row-level triggers that exist
+ in the parent table are cloned on the new partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ The new partition will have the same column names and types as the
+ partitioned table to which it belongs.
+ If the parent table is persistent then the new partition is created
+ persistent. If the parent table is temporary then the new partition
+ is also created temporary.
+ </para>
<note>
<para>
This command acquires an <literal>ACCESS EXCLUSIVE</literal> lock.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dbb6ee356bb..1b89e18eb7e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21183,18 +21183,30 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
/*
* createPartitionTable: create table for a new partition with given name
- * (newPartName) like table (modelRelName)
+ * (newPartName) like table (modelRel)
*
- * Emulates command: CREATE TABLE <newPartName> (LIKE <modelRelName>
+ * Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
* INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ * Function returns the created relation (locked in AccessExclusiveLock mode).
*/
-static void
-createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
+static Relation
+createPartitionTable(RangeVar *newPartName, Relation modelRel,
AlterTableUtilityContext *context)
{
CreateStmt *createStmt;
TableLikeClause *tlc;
PlannedStmt *wrapper;
+ Relation newRel;
+
+ /* If existing rel is temp, it must belong to this session */
+ if (modelRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
+ !modelRel->rd_islocaltemp)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create as partition of temporary relation of another session")));
+
+ /* New partition should have the same persistence as modelRel */
+ newPartName->relpersistence = modelRel->rd_rel->relpersistence;
createStmt = makeNode(CreateStmt);
createStmt->relation = newPartName;
@@ -21207,7 +21219,8 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
createStmt->if_not_exists = false;
tlc = makeNode(TableLikeClause);
- tlc->relation = modelRelName;
+ tlc->relation = makeRangeVar(get_namespace_name(RelationGetNamespace(modelRel)),
+ RelationGetRelationName(modelRel), -1);
/*
* Indexes will be inherited on "attach new partitions" stage, after data
@@ -21233,6 +21246,35 @@ createPartitionTable(RangeVar *newPartName, RangeVar *modelRelName,
NULL,
None_Receiver,
NULL);
+
+ /*
+ * Open the new partition with no lock, because we already have
+ * AccessExclusiveLock placed there after creation.
+ */
+ newRel = table_openrv(newPartName, NoLock);
+
+ /*
+ * 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 (modelRel->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(modelRel))));
+
+ /* Permanent rels cannot be partitions belonging to temporary parent */
+ if (newRel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+ modelRel->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(modelRel))));
+
+ return newRel;
}
/*
@@ -21252,7 +21294,6 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
char tmpRelName[NAMEDATALEN];
List *newPartRels = NIL;
ObjectAddress object;
- RangeVar *parentName;
Oid defaultPartOid;
defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
@@ -21324,18 +21365,12 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
/* Create new partitions (like split partition), without indexes. */
- parentName = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- RelationGetRelationName(rel), -1);
foreach(listptr, cmd->partlist)
{
SinglePartitionSpec *sps = (SinglePartitionSpec *) lfirst(listptr);
Relation newPartRel;
- createPartitionTable(sps->name, parentName, context);
-
- /* Open the new partition and acquire exclusive lock on it. */
- newPartRel = table_openrv(sps->name, AccessExclusiveLock);
-
+ newPartRel = createPartitionTable(sps->name, rel, context);
newPartRels = lappend(newPartRels, newPartRel);
}
@@ -21539,18 +21574,8 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
}
- createPartitionTable(cmd->name,
- makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- RelationGetRelationName(rel), -1),
- context);
-
- /*
- * Open the new partition and acquire exclusive lock on it. This will
- * stop all the operations with partitioned table. This might seem
- * excessive, but this is the way we make sure nobody is planning queries
- * involving merging partitions.
- */
- newPartRel = table_openrv(cmd->name, AccessExclusiveLock);
+ /* Create table for new partition, use partitioned table as model. */
+ newPartRel = createPartitionTable(cmd->name, rel, context);
/* Copy data from merged partitions to new partition. */
moveMergedTablesRows(rel, mergingPartitionsList, newPartRel);
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 2e0bfdc705d..3aae5f89e80 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -3,6 +3,7 @@
-- Tests for "ALTER TABLE ... MERGE PARTITIONS ..." command
--
CREATE SCHEMA partitions_merge_schema;
+CREATE SCHEMA partitions_merge_schema2;
SET search_path = partitions_merge_schema, public;
--
-- BY RANGE partitioning
@@ -36,18 +37,23 @@ ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of p
-- (space between sections sales_dec2021 and sales_jan2022)
ALTER TABLE sales_range MERGE PARTITIONS (sales_dec2021, sales_jan2022, sales_feb2022) INTO sales_dec_jan_feb2022;
ERROR: lower bound of partition "sales_jan2022" conflicts with upper bound of previous partition "sales_dec2021"
--- NO ERROR: test for custom partitions order
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_jan_feb_mar2022;
+-- 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 (
+ partitions_merge_schema.sales_feb2022,
+ 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
------------------------+---------+------------------+--------------------------------------------------
- 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_jan_feb_mar2022 | r | f | FOR VALUES FROM ('01-01-2022') TO ('04-01-2022')
- sales_others | r | f | DEFAULT
+ 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
(4 rows)
DROP TABLE sales_range;
@@ -95,23 +101,24 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
sales_others | r | f | DEFAULT
(5 rows)
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022;
+-- 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
------------------------+---------+------------------+--------------------------------------------------
- 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
+ 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
(3 rows)
-SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema';
- schemaname | tablename | indexname | tablespace | indexdef
--------------------------+-----------------------+--------------------------------------+------------+-----------------------------------------------------------------------------------------------------------------------------
- partitions_merge_schema | sales_feb_mar_apr2022 | sales_feb_mar_apr2022_sales_date_idx | | CREATE INDEX sales_feb_mar_apr2022_sales_date_idx ON partitions_merge_schema.sales_feb_mar_apr2022 USING btree (sales_date)
+SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema2';
+ schemaname | tablename | indexname | tablespace | indexdef
+--------------------------+-----------------------+--------------------------------------+------------+------------------------------------------------------------------------------------------------------------------------------
+ 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;
@@ -141,7 +148,7 @@ SELECT * FROM sales_jan2022;
13 | Gandi | 377 | 01-09-2022
(3 rows)
-SELECT * FROM sales_feb_mar_apr2022;
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022;
salesman_id | salesman_name | sales_amount | sales_date
-------------+---------------+--------------+------------
2 | Smirnoff | 500 | 02-10-2022
@@ -164,7 +171,7 @@ SELECT * FROM sales_others;
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
-SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022 where sales_date > '2022-01-01';
salesman_id | salesman_name | sales_amount | sales_date
-------------+---------------+--------------+------------
2 | Smirnoff | 500 | 02-10-2022
@@ -746,6 +753,34 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
--
+-- Try to MERGE partitions of temporary table.
+--
+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
+(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
+(1 row)
+
+DROP TABLE t;
+--
-- Check the partition index name if the partition name is the same as one
-- of the merged partitions.
--
@@ -771,4 +806,63 @@ Not-null constraints:
DROP TABLE t;
--
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = partitions_merge_schema, pg_temp, public;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+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;
+ oid | relpersistence
+-----+----------------
+ 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
+(2 rows)
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+ERROR: cannot create a temporary relation as partition of permanent relation "t"
+SET search_path = partitions_merge_schema, public;
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+ERROR: cannot create a temporary relation as partition of permanent relation "t"
+DROP TABLE t;
+SET search_path = pg_temp, partitions_merge_schema, public;
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+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;
+ oid | relpersistence
+-----+----------------
+ 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
+(2 rows)
+
+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;
+ROLLBACK;
+RESET search_path;
+--
DROP SCHEMA partitions_merge_schema;
+DROP SCHEMA partitions_merge_schema2;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 419d169f036..e2a362ffafd 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -3,6 +3,7 @@
-- Tests for "ALTER TABLE ... SPLIT PARTITION ..." command
--
CREATE SCHEMA partition_split_schema;
+CREATE SCHEMA partition_split_schema2;
SET search_path = partition_split_schema, public;
--
-- BY RANGE partitioning
@@ -104,6 +105,28 @@ 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
LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-02') TO...
^
+-- Check the source partition not in the search path
+SET search_path = partition_split_schema2, public;
+ALTER TABLE partition_split_schema.sales_range
+SPLIT PARTITION partition_split_schema.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-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
+---------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
+ salesman_id | integer | | | | plain | |
+ salesman_name | character varying(30) | | | | extended | |
+ sales_amount | 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'),
+ partition_split_schema2.sales_mar2022 FOR VALUES FROM ('03-01-2022') TO ('04-01-2022'),
+ sales_jan2022 FOR VALUES FROM ('01-01-2022') TO ('02-01-2022')
+
DROP TABLE sales_range;
DROP TABLE sales_others;
--
@@ -197,10 +220,26 @@ CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amou
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;
+-- Split partition, also check schema qualification of new partitions
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 partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+ PARTITION partition_split_schema2.sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+\d+ sales_range
+ Partitioned table "partition_split_schema.sales_range"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+---------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
+ salesman_id | integer | | | | plain | |
+ salesman_name | character varying(30) | | | | extended | |
+ sales_amount | integer | | | | plain | |
+ sales_date | date | | | | plain | |
+Partition key: RANGE (sales_date)
+Partitions: partition_split_schema2.sales_mar2022 FOR VALUES FROM ('03-01-2022') TO ('04-01-2022'),
+ sales_apr2022 FOR VALUES FROM ('04-01-2022') TO ('05-01-2022'),
+ sales_feb2022 FOR VALUES FROM ('02-01-2022') TO ('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');
INSERT INTO sales_range VALUES (2, 'Smirnoff', 500, '2022-02-10');
INSERT INTO sales_range VALUES (3, 'Ford', 2000, '2022-04-30');
@@ -250,7 +289,7 @@ SELECT * FROM sales_feb2022;
8 | Ericsson | 185 | 02-23-2022
(3 rows)
-SELECT * FROM sales_mar2022;
+SELECT * FROM partition_split_schema2.sales_mar2022;
salesman_id | salesman_name | sales_amount | sales_date
-------------+---------------+--------------+------------
7 | Li | 175 | 03-08-2022
@@ -1427,4 +1466,34 @@ ERROR: relation "t1pa" is not a partition of relation "t2"
DROP TABLE t2;
DROP TABLE t1;
--
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) 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_2 | FOR VALUES FROM (0) TO (2) | t
+(1 row)
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+-- Partitions 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_1 | FOR VALUES FROM (0) TO (1) | t
+ tp_1_2 | FOR VALUES FROM (1) TO (2) | t
+(2 rows)
+
+DROP TABLE t;
+--
DROP SCHEMA partition_split_schema;
+DROP SCHEMA partition_split_schema2;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 72b1cb0b35e..5a69425d96e 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -4,6 +4,7 @@
--
CREATE SCHEMA partitions_merge_schema;
+CREATE SCHEMA partitions_merge_schema2;
SET search_path = partitions_merge_schema, public;
--
@@ -37,8 +38,13 @@ ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022) INTO sal
-- (space between sections sales_dec2021 and sales_jan2022)
ALTER TABLE sales_range MERGE PARTITIONS (sales_dec2021, sales_jan2022, sales_feb2022) INTO sales_dec_jan_feb2022;
--- NO ERROR: test for custom partitions order
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_jan2022) INTO sales_jan_feb_mar2022;
+-- 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 (
+ partitions_merge_schema.sales_feb2022,
+ 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
@@ -81,7 +87,8 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
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;
-ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022;
+-- 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)
@@ -89,17 +96,17 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
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;
-SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema';
+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 sales_feb_mar_apr2022;
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022;
SELECT * FROM sales_others;
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
-SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
+SELECT * FROM partitions_merge_schema2.sales_feb_mar_apr2022 where sales_date > '2022-01-01';
RESET enable_seqscan;
@@ -444,6 +451,28 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Try to MERGE partitions of temporary table.
+--
+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;
+
+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;
+
+DROP TABLE t;
+
--
-- Check the partition index name if the partition name is the same as one
-- of the merged partitions.
@@ -462,5 +491,52 @@ ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
DROP TABLE t;
+--
+-- Try mixing permanent and temporary partitions.
+--
+SET search_path = partitions_merge_schema, pg_temp, public;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+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;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+
+SET search_path = partitions_merge_schema, public;
+
+-- Can't merge persistent partitions into a temporary partition
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
+DROP TABLE t;
+
+SET search_path = pg_temp, partitions_merge_schema, public;
+
+BEGIN;
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+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;
+
+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;
+ROLLBACK;
+
+RESET search_path;
+
--
DROP SCHEMA partitions_merge_schema;
+DROP SCHEMA partitions_merge_schema2;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index b63532ee562..b4430133522 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -4,6 +4,7 @@
--
CREATE SCHEMA partition_split_schema;
+CREATE SCHEMA partition_split_schema2;
SET search_path = partition_split_schema, public;
--
@@ -92,6 +93,16 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
+-- Check the source partition not in the search path
+SET search_path = partition_split_schema2, public;
+ALTER TABLE partition_split_schema.sales_range
+SPLIT PARTITION partition_split_schema.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-05-01'));
+SET search_path = partition_split_schema, public;
+\d+ sales_range
+
DROP TABLE sales_range;
DROP TABLE sales_others;
@@ -140,10 +151,12 @@ 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;
+-- Split partition, also check schema qualification of new partitions
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 partition_split_schema.sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
+ PARTITION partition_split_schema2.sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
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');
INSERT INTO sales_range VALUES (2, 'Smirnoff', 500, '2022-02-10');
@@ -163,7 +176,7 @@ INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
SELECT * FROM sales_jan2022;
SELECT * FROM sales_feb2022;
-SELECT * FROM sales_mar2022;
+SELECT * FROM partition_split_schema2.sales_mar2022;
SELECT * FROM sales_apr2022;
SELECT * FROM sales_others;
@@ -844,5 +857,29 @@ ALTER TABLE t2 SPLIT PARTITION t1pa INTO
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Try to SPLIT partition of temporary table.
+--
+CREATE TEMP TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TEMP TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) 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;
+
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+
+-- Partitions 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;
+
+DROP TABLE t;
+
--
DROP SCHEMA partition_split_schema;
+DROP SCHEMA partition_split_schema2;
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0005-Add-tab-completion-for-partition-MERGE-SPLIT-oper.patch (2.7K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/5-v8-0005-Add-tab-completion-for-partition-MERGE-SPLIT-oper.patch)
download | inline diff:
From c86c051eb0fd187f82f0c8111421ccb33f9cceb5 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 22 Apr 2024 13:20:11 +0300
Subject: [PATCH v8 5/7] Add tab completion for partition MERGE/SPLIT
operations
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit implements psql tab completion for ALTER TABLE ... SPLIT PARTITION
and ALTER TABLE ... MERGE PARTITIONS commands.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/5dee3937-8e9f-cca4-11fb-737709a92b37%40gmail.com
Author: Dagfinn Ilmari Mannsåker
---
src/bin/psql/tab-complete.c | 18 ++++++++++++++++--
1 file changed, 16 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6fee3160f02..97cd5d9f628 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2353,6 +2353,7 @@ psql_completion(const char *text, int start, int end)
"OWNER TO", "SET", "VALIDATE CONSTRAINT",
"REPLICA IDENTITY", "ATTACH PARTITION",
"DETACH PARTITION", "FORCE ROW LEVEL SECURITY",
+ "SPLIT PARTITION", "MERGE PARTITIONS (",
"OF", "NOT OF");
/* ALTER TABLE xxx ADD */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD"))
@@ -2609,10 +2610,10 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("FROM (", "IN (", "WITH (");
/*
- * If we have ALTER TABLE <foo> DETACH PARTITION, provide a list of
+ * If we have ALTER TABLE <foo> DETACH|SPLIT PARTITION, provide a list of
* partitions of <foo>.
*/
- else if (Matches("ALTER", "TABLE", MatchAny, "DETACH", "PARTITION"))
+ else if (Matches("ALTER", "TABLE", MatchAny, "DETACH|SPLIT", "PARTITION"))
{
set_completion_reference(prev3_wd);
COMPLETE_WITH_SCHEMA_QUERY(Query_for_partition_of_table);
@@ -2620,6 +2621,19 @@ psql_completion(const char *text, int start, int end)
else if (Matches("ALTER", "TABLE", MatchAny, "DETACH", "PARTITION", MatchAny))
COMPLETE_WITH("CONCURRENTLY", "FINALIZE");
+ /* ALTER TABLE <name> SPLIT PARTITION <name> */
+ else if (Matches("ALTER", "TABLE", MatchAny, "SPLIT", "PARTITION", MatchAny))
+ COMPLETE_WITH("INTO ( PARTITION");
+
+ /* ALTER TABLE <name> MERGE PARTITIONS ( */
+ else if (Matches("ALTER", "TABLE", MatchAny, "MERGE", "PARTITIONS", "("))
+ {
+ set_completion_reference(prev4_wd);
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_partition_of_table);
+ }
+ else if (Matches("ALTER", "TABLE", MatchAny, "MERGE", "PARTITIONS", "(*)"))
+ COMPLETE_WITH("INTO");
+
/* ALTER TABLE <name> OF */
else if (Matches("ALTER", "TABLE", MatchAny, "OF"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes);
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0001-Change-the-way-ATExecMergePartitions-handles-the-.patch (7.5K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/6-v8-0001-Change-the-way-ATExecMergePartitions-handles-the-.patch)
download | inline diff:
From f17c461ea4e054950ee4e4f423ea223d20382477 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Fri, 19 Apr 2024 13:39:45 +0300
Subject: [PATCH v8 1/7] Change the way ATExecMergePartitions() handles the
name collision
The name collision happens when the name of the new partition is the same as
the name of one of the merging partitions. Currently, ATExecMergePartitions()
first gives the new partition a temporary name and then renames it when old
partitions are deleted. That negatively influences the naming of related
objects like indexes and constrains, which could inherit a temporary name.
This commit changes the implementation in the following way. A merging
partition gets renamed first, then the new partition is created with the
right name immediately. This resolves the issue of the naming of related
objects.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/edfbd846-dcc1-42d1-ac26-715691b687d3%40postgrespro.ru
Author: Dmitry Koval
Reviewed-by: Alexander Korotkov, Robert Haas
---
src/backend/commands/tablecmds.c | 63 +++++++++----------
src/test/regress/expected/partition_merge.out | 25 ++++++++
src/test/regress/sql/partition_merge.sql | 18 ++++++
3 files changed, 73 insertions(+), 33 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3556240c8ed..dbb6ee356bb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21477,9 +21477,6 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
ListCell *listptr;
List *mergingPartitionsList = NIL;
Oid defaultPartOid;
- char tmpRelName[NAMEDATALEN];
- RangeVar *mergePartName = cmd->name;
- bool isSameName = false;
/*
* Lock all merged partitions, check them and create list with partitions
@@ -21501,8 +21498,28 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
* function transformPartitionCmdForMerge().
*/
if (equal(name, cmd->name))
+ {
/* One new partition can have the same name as merged partition. */
- isSameName = true;
+ char tmpRelName[NAMEDATALEN];
+
+ /* Generate temporary name. */
+ sprintf(tmpRelName, "merge-%u-%X-tmp", RelationGetRelid(rel), MyProcPid);
+
+ /*
+ * Rename the existing partition with a temporary name, leaving it
+ * free for the new partition. We don't need to care about this
+ * in the future because we're going to eventually drop the
+ * existing partition anyway.
+ */
+ RenameRelationInternal(RelationGetRelid(mergingPartition),
+ tmpRelName, false, false);
+
+ /*
+ * We must bump the command counter to make the new partition
+ * tuple visible for rename.
+ */
+ CommandCounterIncrement();
+ }
/* Store a next merging partition into the list. */
mergingPartitionsList = lappend(mergingPartitionsList,
@@ -21522,15 +21539,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
DetachPartitionFinalize(rel, mergingPartition, false, defaultPartOid);
}
- /* Create table for new partition, use partitioned table as model. */
- if (isSameName)
- {
- /* Create partition table with generated temporary name. */
- sprintf(tmpRelName, "merge-%u-%X-tmp", RelationGetRelid(rel), MyProcPid);
- mergePartName = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- tmpRelName, -1);
- }
- createPartitionTable(mergePartName,
+ createPartitionTable(cmd->name,
makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
RelationGetRelationName(rel), -1),
context);
@@ -21541,18 +21550,12 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
* excessive, but this is the way we make sure nobody is planning queries
* involving merging partitions.
*/
- newPartRel = table_openrv(mergePartName, AccessExclusiveLock);
+ newPartRel = table_openrv(cmd->name, AccessExclusiveLock);
/* Copy data from merged partitions to new partition. */
moveMergedTablesRows(rel, mergingPartitionsList, newPartRel);
- /*
- * Attach a new partition to the partitioned table. wqueue = NULL:
- * verification for each cloned constraint is not need.
- */
- attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
-
- /* Unlock and drop merged partitions. */
+ /* Drop the current partitions before attaching the new one. */
foreach(listptr, mergingPartitionsList)
{
ObjectAddress object;
@@ -21570,18 +21573,12 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
list_free(mergingPartitionsList);
- /* Rename new partition if it is needed. */
- if (isSameName)
- {
- /*
- * We must bump the command counter to make the new partition tuple
- * visible for rename.
- */
- CommandCounterIncrement();
- /* Rename partition. */
- RenameRelationInternal(RelationGetRelid(newPartRel),
- cmd->name->relname, false, false);
- }
+ /*
+ * Attach a new partition to the partitioned table. wqueue = NULL:
+ * verification for each cloned constraint is not needed.
+ */
+ attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 373d32948ca..2e0bfdc705d 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -746,4 +746,29 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
--
+-- Check the partition index name if the partition name is the same as one
+-- of the merged partitions.
+--
+CREATE TABLE t (i int, PRIMARY KEY(i)) PARTITION BY RANGE (i);
+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);
+CREATE INDEX tidx ON t(i);
+ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
+-- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'.
+-- Not-null constraint name should be 'tp_1_2_i_not_null'.
+\d+ tp_1_2
+ Table "partitions_merge_schema.tp_1_2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i | integer | | not null | | plain | |
+Partition of: t FOR VALUES FROM (0) TO (2)
+Partition constraint: ((i IS NOT NULL) AND (i >= 0) AND (i < 2))
+Indexes:
+ "tp_1_2_pkey" PRIMARY KEY, btree (i)
+ "tp_1_2_i_idx" btree (i)
+Not-null constraints:
+ "tp_1_2_i_not_null" NOT NULL "i"
+
+DROP TABLE t;
+--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 6a0b35b1799..72b1cb0b35e 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -444,5 +444,23 @@ DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
+--
+-- Check the partition index name if the partition name is the same as one
+-- of the merged partitions.
+--
+CREATE TABLE t (i int, PRIMARY KEY(i)) PARTITION BY RANGE (i);
+
+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);
+
+CREATE INDEX tidx ON t(i);
+ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2;
+
+-- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'.
+-- Not-null constraint name should be 'tp_1_2_i_not_null'.
+\d+ tp_1_2
+
+DROP TABLE t;
+
--
DROP SCHEMA partitions_merge_schema;
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0006-Inherit-parent-s-AM-for-partition-MERGE-SPLIT-ope.patch (9.3K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/7-v8-0006-Inherit-parent-s-AM-for-partition-MERGE-SPLIT-ope.patch)
download | inline diff:
From 5fc0bcb59f1f40a2d10dd191d208b074145bd4c4 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 22 Apr 2024 13:21:14 +0300
Subject: [PATCH v8 6/7] Inherit parent's AM for partition MERGE/SPLIT
operations
This commit makes new partitions created by ALTER TABLE ... SPLIT PARTITION
and ALTER TABLE ... MERGE PARTITIONS commands inherit the paret table access
method.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/84ada05b-be5c-473e-6d1c-ebe5dd21b190%40gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 8 +++----
src/backend/commands/tablecmds.c | 6 +++++
src/test/regress/expected/partition_merge.out | 18 +++++++++++++++
src/test/regress/expected/partition_split.out | 23 +++++++++++++++++--
src/test/regress/sql/partition_merge.sql | 13 +++++++++++
src/test/regress/sql/partition_split.sql | 14 +++++++++++
6 files changed, 76 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a793899b69b..dba49a7ac8f 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1158,8 +1158,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
in the parent table are cloned on new partitions
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>, ... .
- New partitions will have the same column names and types as the
- partitioned table to which they belong.
+ New partitions will have the same table access method,
+ same column names and types as the partitioned table to which they belong.
If the parent table is persistent then new partitions are created
persistent. If the parent table is temporary then new partitions
are also created temporary.
@@ -1228,8 +1228,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
Any indexes, constraints and user-defined row-level triggers that exist
in the parent table are cloned on the new partition
<replaceable class="parameter">partition_name</replaceable>.
- The new partition will have the same column names and types as the
- partitioned table to which it belongs.
+ The new partition will have the same table access method,
+ same column names and types as the partitioned table to which it belongs.
If the parent table is persistent then the new partition is created
persistent. If the parent table is temporary then the new partition
is also created temporary.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1b89e18eb7e..fd0812cd649 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -21187,6 +21187,11 @@ moveSplitTableRows(Relation rel, Relation splitRel, List *partlist, List *newPar
*
* Emulates command: CREATE [TEMP] TABLE <newPartName> (LIKE <modelRel's name>
* INCLUDING ALL EXCLUDING INDEXES EXCLUDING IDENTITY)
+ *
+ * Also, this function sets the new partition access method same as parent
+ * table access methods (similarly to CREATE TABLE ... PARTITION OF). It
+ * checks that parent and child tables have compatible persistence.
+ *
* Function returns the created relation (locked in AccessExclusiveLock mode).
*/
static Relation
@@ -21217,6 +21222,7 @@ createPartitionTable(RangeVar *newPartName, Relation modelRel,
createStmt->oncommit = ONCOMMIT_NOOP;
createStmt->tablespacename = NULL;
createStmt->if_not_exists = false;
+ createStmt->accessMethod = get_am_name(modelRel->rd_rel->relam);
tlc = makeNode(TableLikeClause);
tlc->relation = makeRangeVar(get_namespace_name(RelationGetNamespace(modelRel)),
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index a92a270c591..92999703217 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -862,6 +862,24 @@ 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;
ROLLBACK;
+-- Check the new partition inherits parent's table access method
+SET search_path = partitions_merge_schema, public;
+CREATE ACCESS METHOD partitions_merge_heap TYPE TABLE HANDLER heap_tableam_handler;
+CREATE TABLE t (i int) PARTITION BY RANGE (i) USING partitions_merge_heap;
+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);
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+SELECT c.relname, a.amname
+FROM pg_class c JOIN pg_am a ON c.relam = a.oid
+WHERE c.oid IN ('t'::regclass, 'tp_0_2'::regclass);
+ relname | amname
+---------+-----------------------
+ tp_0_2 | partitions_merge_heap
+ t | partitions_merge_heap
+(2 rows)
+
+DROP TABLE t;
+DROP ACCESS METHOD partitions_merge_heap;
RESET search_path;
--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 55ae37ad370..326fa1bd400 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -85,8 +85,8 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
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 greater than upper bound of split partition
-LINE 4: PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO...
- ^
+LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
+ ^
-- ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of previous 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'),
@@ -1494,6 +1494,25 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
(2 rows)
DROP TABLE t;
+-- Check new partitions inherits parent's table access method
+CREATE ACCESS METHOD partition_split_heap TYPE TABLE HANDLER heap_tableam_handler;
+CREATE TABLE t (i int) PARTITION BY RANGE (i) USING partition_split_heap;
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+SELECT c.relname, a.amname
+FROM pg_class c JOIN pg_am a ON c.relam = a.oid
+WHERE c.oid IN ('t'::regclass, 'tp_0_1'::regclass, 'tp_1_2'::regclass);
+ relname | amname
+---------+----------------------
+ t | partition_split_heap
+ tp_0_1 | partition_split_heap
+ tp_1_2 | partition_split_heap
+(3 rows)
+
+DROP TABLE t;
+DROP ACCESS METHOD partition_split_heap;
--
DROP SCHEMA partition_split_schema;
DROP SCHEMA partition_split_schema2;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 085c422d540..23795cf9d94 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -535,6 +535,19 @@ SET search_path = partitions_merge_schema, pg_temp, public;
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ROLLBACK;
+-- Check the new partition inherits parent's table access method
+SET search_path = partitions_merge_schema, public;
+CREATE ACCESS METHOD partitions_merge_heap TYPE TABLE HANDLER heap_tableam_handler;
+CREATE TABLE t (i int) PARTITION BY RANGE (i) USING partitions_merge_heap;
+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);
+ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
+SELECT c.relname, a.amname
+FROM pg_class c JOIN pg_am a ON c.relam = a.oid
+WHERE c.oid IN ('t'::regclass, 'tp_0_2'::regclass);
+DROP TABLE t;
+DROP ACCESS METHOD partitions_merge_heap;
+
RESET search_path;
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 3a7f2f9c294..73e8c2fbeb9 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -880,6 +880,20 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
DROP TABLE t;
+-- Check new partitions inherits parent's table access method
+CREATE ACCESS METHOD partition_split_heap TYPE TABLE HANDLER heap_tableam_handler;
+CREATE TABLE t (i int) PARTITION BY RANGE (i) USING partition_split_heap;
+CREATE TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2);
+ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
+ (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
+ PARTITION tp_1_2 FOR VALUES FROM (1) TO (2));
+SELECT c.relname, a.amname
+FROM pg_class c JOIN pg_am a ON c.relam = a.oid
+WHERE c.oid IN ('t'::regclass, 'tp_0_1'::regclass, 'tp_1_2'::regclass);
+DROP TABLE t;
+DROP ACCESS METHOD partition_split_heap;
+
+
--
DROP SCHEMA partition_split_schema;
DROP SCHEMA partition_split_schema2;
--
2.39.3 (Apple Git-145)
[application/octet-stream] v8-0007-Grammar-fixes-for-documentation-of-partition-MERG.patch (6.2K, ../CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com/8-v8-0007-Grammar-fixes-for-documentation-of-partition-MERG.patch)
download | inline diff:
From 146d6946d7ff58dda8c32e048d9b8cf30c8278de Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 22 Apr 2024 13:18:31 +0300
Subject: [PATCH v8 7/7] Grammar fixes for documentation of partition
MERGE/SPLIT operations
Discussion: https://postgr.es/m/ZiGH0xc1lxJ71ZfB%40pryzbyj2023
Author: Justin Pryzby
---
doc/src/sgml/ddl.sgml | 4 ++--
doc/src/sgml/ref/alter_table.sgml | 22 +++++++++++-----------
2 files changed, 13 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 026bfff70f3..01277b1d327 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4384,7 +4384,7 @@ ALTER INDEX measurement_city_id_logdate_key
<para>
There is also an option for merging multiple table partitions into
- a single partition using the
+ a single partition using
<link linkend="sql-altertable-merge-partitions"><command>ALTER TABLE ... MERGE PARTITIONS</command></link>.
This feature simplifies the management of partitioned tables by allowing
users to combine partitions that are no longer needed as
@@ -4403,7 +4403,7 @@ ALTER TABLE measurement
<para>
Similarly to merging multiple table partitions, there is an option for
- splitting a single partition into multiple using the
+ splitting a single partition into multiple partitions using
<link linkend="sql-altertable-split-partition"><command>ALTER TABLE ... SPLIT PARTITION</command></link>.
This feature could come in handy when one partition grows too big
and needs to be split into multiple. It's important to note that
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dba49a7ac8f..db477856395 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1136,16 +1136,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
If the split partition is a DEFAULT partition, one of the new partitions must be DEFAULT.
In case one of the new partitions or one of existing partitions is DEFAULT,
new partitions <replaceable class="parameter">partition_name1</replaceable>,
- <replaceable class="parameter">partition_name2</replaceable>, ... can have spaces
+ <replaceable class="parameter">partition_name2</replaceable>, ... can have gaps
between partitions bounds. If the partitioned table does not have a DEFAULT
partition, the DEFAULT partition can be defined as one of the new partitions.
</para>
<para>
In case new partitions do not contain a DEFAULT partition and the partitioned table
- does not have a DEFAULT partition, the following must be true: sum bounds of
+ does not have a DEFAULT partition, the following must be true: the sum bounds of
new partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>, ... should be
- equal to bound of split partition <replaceable class="parameter">partition_name</replaceable>.
+ equal to the bounds of split partition <replaceable class="parameter">partition_name</replaceable>.
One of the new partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>, ... can have
the same name as split partition <replaceable class="parameter">partition_name</replaceable>
@@ -1179,24 +1179,24 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
- This form merges several partitions into the one partition of the target table.
- Hash-partitioning is not supported. If DEFAULT partition is not in the
+ This form merges several partitions of the target table into a single partition.
+ Hash-partitioning is not supported. If a DEFAULT partition is not in the
list of partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...]:
<itemizedlist>
<listitem>
<para>
- For range-partitioned tables it is necessary that the ranges
+ For range-partitioned tables, it is necessary that the ranges
of the partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...] can
- be merged into one range without spaces and overlaps (otherwise an error
+ be merged into one range with neither gaps nor overlaps (otherwise an error
will be generated). The combined range will be the range for the partition
<replaceable class="parameter">partition_name</replaceable>.
</para>
</listitem>
<listitem>
<para>
- For list-partitioned tables the value lists of all partitions
+ For list-partitioned tables, the value lists of all partitions
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...] are
combined and form the list of values of partition
@@ -1204,7 +1204,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
</listitem>
</itemizedlist>
- If DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>,
+ If a DEFAULT partition is in the list of partitions <replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable> [, ...]:
<itemizedlist>
<listitem>
@@ -1215,8 +1215,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
<listitem>
<para>
- For range- and list-partitioned tables the ranges and lists of values
- of the merged partitions can be any.
+ For range- and list-partitioned tables, the ranges and lists of values
+ of the merged partitions can be anything.
</para>
</listitem>
</itemizedlist>
--
2.39.3 (Apple Git-145)
view thread (48+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
In-Reply-To: <CAPpHfdtB9L1C7HLE30WDQfQQgUZo5P+5=9kLr5vQSpDZ-2AV_A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox