diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/isolation/expected/partition-merge.out | 199 | ||||
| -rw-r--r-- | src/test/isolation/isolation_schedule | 1 | ||||
| -rw-r--r-- | src/test/isolation/specs/partition-merge.spec | 54 | ||||
| -rw-r--r-- | src/test/modules/test_ddl_deparse/test_ddl_deparse.c | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_merge.out | 732 | ||||
| -rw-r--r-- | src/test/regress/parallel_schedule | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/partition_merge.sql | 430 |
7 files changed, 1420 insertions, 1 deletions
diff --git a/src/test/isolation/expected/partition-merge.out b/src/test/isolation/expected/partition-merge.out new file mode 100644 index 00000000000..98446aaab5a --- /dev/null +++ b/src/test/isolation/expected/partition-merge.out @@ -0,0 +1,199 @@ +Parsed test spec with 2 sessions + +starting permutation: s2b s2i s2c s1b s1merg s2b s2u s1c s2c s2s +step s2b: BEGIN; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1b: BEGIN; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2b s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +step s2b: BEGIN; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2b s2i s2c s1bs s1merg s2b s2u s1c s2c s2s +step s2b: BEGIN; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2brr s2i s2c s1b s1merg s2b s2u s1c s2c s2s +step s2brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1b: BEGIN; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2brr s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +step s2brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2brr s2i s2c s1bs s1merg s2b s2u s1c s2c s2s +step s2brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2bs s2i s2c s1b s1merg s2b s2u s1c s2c s2s +step s2bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1b: BEGIN; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2bs s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +step s2bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1brr: BEGIN ISOLATION LEVEL REPEATABLE READ; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + + +starting permutation: s2bs s2i s2c s1bs s1merg s2b s2u s1c s2c s2s +step s2bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s2i: INSERT INTO tpart VALUES (1, 'text01'); +step s2c: COMMIT; +step s1bs: BEGIN ISOLATION LEVEL SERIALIZABLE; +step s1merg: ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; +step s2b: BEGIN; +step s2u: UPDATE tpart SET t = 'text01modif' where i = 1; <waiting ...> +step s1c: COMMIT; +step s2u: <... completed> +step s2c: COMMIT; +step s2s: SELECT * FROM tpart; + i|t +--+----------- + 5|text05 +15|text15 + 1|text01modif +25|text25 +35|text35 +(5 rows) + diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 188fc04f85e..8bcaa8a6254 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -103,6 +103,7 @@ test: partition-key-update-1 test: partition-key-update-2 test: partition-key-update-3 test: partition-key-update-4 +test: partition-merge test: plpgsql-toast test: cluster-conflict test: cluster-conflict-partition diff --git a/src/test/isolation/specs/partition-merge.spec b/src/test/isolation/specs/partition-merge.spec new file mode 100644 index 00000000000..ec48732c583 --- /dev/null +++ b/src/test/isolation/specs/partition-merge.spec @@ -0,0 +1,54 @@ +# Verify that MERGE operation locks DML operations with partitioned table + +setup +{ + DROP TABLE IF EXISTS tpart; + CREATE TABLE tpart(i int, t text) partition by range(i); + CREATE TABLE tpart_00_10 PARTITION OF tpart FOR VALUES FROM (0) TO (10); + CREATE TABLE tpart_10_20 PARTITION OF tpart FOR VALUES FROM (10) TO (20); + CREATE TABLE tpart_20_30 PARTITION OF tpart FOR VALUES FROM (20) TO (30); + CREATE TABLE tpart_default PARTITION OF tpart DEFAULT; + INSERT INTO tpart VALUES (5, 'text05'); + INSERT INTO tpart VALUES (15, 'text15'); + INSERT INTO tpart VALUES (25, 'text25'); + INSERT INTO tpart VALUES (35, 'text35'); +} + +teardown +{ + DROP TABLE tpart; +} + +session s1 +step s1b { BEGIN; } +step s1brr { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step s1bs { BEGIN ISOLATION LEVEL SERIALIZABLE; } +step s1merg { ALTER TABLE tpart MERGE PARTITIONS (tpart_00_10, tpart_10_20) INTO tpart_00_20; } +step s1c { COMMIT; } + + +session s2 +step s2b { BEGIN; } +step s2brr { BEGIN ISOLATION LEVEL REPEATABLE READ; } +step s2bs { BEGIN ISOLATION LEVEL SERIALIZABLE; } +step s2i { INSERT INTO tpart VALUES (1, 'text01'); } +step s2u { UPDATE tpart SET t = 'text01modif' where i = 1; } +step s2c { COMMIT; } +step s2s { SELECT * FROM tpart; } + + +# s2 inserts row into table. s1 starts MERGE PARTITIONS then +# s2 trying to update inserted row and waits until s1 finished +# MERGE operation. + +permutation s2b s2i s2c s1b s1merg s2b s2u s1c s2c s2s +permutation s2b s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +permutation s2b s2i s2c s1bs s1merg s2b s2u s1c s2c s2s + +permutation s2brr s2i s2c s1b s1merg s2b s2u s1c s2c s2s +permutation s2brr s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +permutation s2brr s2i s2c s1bs s1merg s2b s2u s1c s2c s2s + +permutation s2bs s2i s2c s1b s1merg s2b s2u s1c s2c s2s +permutation s2bs s2i s2c s1brr s1merg s2b s2u s1c s2c s2s +permutation s2bs s2i s2c s1bs s1merg s2b s2u s1c s2c s2s diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c index 48563b2cf01..69920757b21 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -300,6 +300,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS) case AT_DetachPartitionFinalize: strtype = "DETACH PARTITION ... FINALIZE"; break; + case AT_MergePartitions: + strtype = "MERGE PARTITIONS"; + break; case AT_AddIdentity: strtype = "ADD IDENTITY"; break; diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out new file mode 100644 index 00000000000..2ba0ec47d97 --- /dev/null +++ b/src/test/regress/expected/partition_merge.out @@ -0,0 +1,732 @@ +-- +-- PARTITIONS_MERGE +-- Tests for "ALTER TABLE ... MERGE PARTITIONS ..." command +-- +CREATE SCHEMA partitions_merge_schema; +SET search_path = partitions_merge_schema, public; +-- +-- BY RANGE partitioning +-- +-- +-- 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_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_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'); +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +-- ERROR: partition with name "sales_feb2022" already used +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; +ERROR: partition with name "sales_feb2022" already used +LINE 1: ...e MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2... + ^ +-- ERROR: cannot merge non-table partition "sales_apr2022" +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022; +ERROR: cannot merge non-table partition "sales_apr2022" +-- ERROR: invalid partitions order, partition "sales_mar2022" can not be merged +-- (space between sections sales_jan2022 and sales_mar2022) +ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022) INTO sales_jan_mar2022; +ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of previous partition "sales_jan2022" +-- ERROR: invalid partitions order, partition "sales_jan2022" can not be merged +-- (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; +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 +(4 rows) + +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_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 PARTITION OF sales_range FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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 pg_catalog.pg_get_partkeydef('sales_range'::regclass); + pg_get_partkeydef +-------------------- + RANGE (sales_date) +(1 row) + +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + oid | relkind | inhdetachpending | pg_get_expr +---------------+---------+------------------+-------------------------------------------------- + sales_apr2022 | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022') + sales_feb2022 | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022') + sales_jan2022 | r | f | FOR VALUES FROM ('01-01-2022') TO ('02-01-2022') + sales_mar2022 | r | f | FOR VALUES FROM ('03-01-2022') TO ('04-01-2022') + sales_others | r | f | DEFAULT +(5 rows) + +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO 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 +(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) +(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 +(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 +(3 rows) + +SELECT * FROM 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 +(10 rows) + +SELECT * FROM sales_others; + salesman_id | salesman_name | sales_amount | sales_date +-------------+---------------+--------------+------------ + 14 | Smith | 510 | 05-04-2022 +(1 row) + +-- Use indexscan for test indexes +SET enable_seqscan = OFF; +SELECT * FROM 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 +(10 rows) + +RESET enable_seqscan; +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_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 PARTITION OF sales_range FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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'); +-- Merge partitions (include DEFAULT partition) into partition with the same +-- 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 +(7 rows) + +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + oid | relkind | inhdetachpending | pg_get_expr +---------------+---------+------------------+-------------------------------------------------- + sales_apr2022 | r | f | FOR VALUES FROM ('04-01-2022') TO ('05-01-2022') + sales_feb2022 | r | f | FOR VALUES FROM ('02-01-2022') TO ('03-01-2022') + sales_others | r | f | DEFAULT +(3 rows) + +DROP TABLE sales_range; +-- +-- Test for: +-- * composite partition key; +-- * GENERATED column; +-- * column with DEFAULT value. +-- +CREATE TABLE sales_date (salesman_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') + PARTITION BY RANGE (sales_year, sales_month, sales_day); +CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1) TO (2022, 1, 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); +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 +(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 +(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 +(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 +(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 +(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); +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 +(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 +(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 +(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 +(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 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 + AFTER INSERT + ON salesmans + FOR EACH STATEMENT + EXECUTE PROCEDURE after_insert_row_trigger('salesmans'); +CREATE TRIGGER salesmans_after_insert_row_trigger + AFTER INSERT + ON salesmans + FOR EACH ROW + EXECUTE PROCEDURE after_insert_row_trigger('salesmans'); +-- 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 +-- 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; +-- 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 +-- 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 +(1 row) + +SELECT * FROM salesmans10_40; + salesman_id | salesman_name +-------------+--------------- + 10 | May + 19 | Ivanov + 20 | Smirnoff + 30 | Ford +(4 rows) + +DROP TABLE salesmans; +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 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 +(6 rows) + +select * from salesmans01_10; + salesman_id | salesman_name +-------------+--------------- + 1 | Poirot +(1 row) + +select * from salesmans10_40; + salesman_id | salesman_name +-------------+--------------- + 19 | Ivanov + 10 | May + 20 | Smirnoff + 31 | Popov + 30 | Ford +(5 rows) + +DROP TABLE salesmans; +-- +-- 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_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_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'); +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; +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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 +(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 +(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 +(2 rows) + +SELECT * FROM sales_apr2022_10_20; + salesman_id | salesman_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 +(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 +(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 +(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 +(4 rows) + +DROP TABLE sales_range; +-- +-- BY LIST partitioning +-- +-- +-- Test: specific errors for BY LIST partitioning +-- +CREATE TABLE sales_list +(salesman_id INT GENERATED ALWAYS AS IDENTITY, + salesman_name VARCHAR(30), + sales_state VARCHAR(20), + sales_amount INT, + sales_date DATE) +PARTITION BY LIST (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; +CREATE TABLE sales_list2 (LIKE sales_list) PARTITION BY LIST (sales_state); +CREATE TABLE sales_nord2 PARTITION OF sales_list2 FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki'); +CREATE TABLE sales_others2 PARTITION OF sales_list2 DEFAULT; +CREATE TABLE sales_external (LIKE sales_list); +CREATE TABLE sales_external2 (vch VARCHAR(5)); +-- ERROR: partition bound for relation "sales_external" is null +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external) INTO sales_all; +ERROR: partition bound for relation "sales_external" is null +-- ERROR: partition bound for relation "sales_external2" is null +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external2) INTO sales_all; +ERROR: partition bound for relation "sales_external2" is null +-- ERROR: relation "sales_nord2" is not a partition of relation "sales_list" +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_nord2, sales_east) INTO sales_all; +ERROR: relation "sales_nord2" is not a partition of relation "sales_list" +DROP TABLE sales_external2; +DROP TABLE sales_external; +DROP TABLE sales_list2; +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), + 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_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'); +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_list'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + oid | relkind | inhdetachpending | pg_get_expr +---------------+---------+------------------+------------------------------------------------------ + sales_central | r | f | FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv') + sales_east | r | f | FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok') + sales_nord | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki') + sales_west | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid') + sales_others | r | f | DEFAULT +(5 rows) + +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) INTO sales_all; +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_list'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + oid | relkind | inhdetachpending | pg_get_expr +--------------+---------+------------------+-------------------------------------------------------------------------------------------------------------- + sales_all | r | f | FOR VALUES IN ('Lisbon', 'New York', 'Madrid', 'Bejing', 'Delhi', 'Vladivostok', 'Warsaw', 'Berlin', 'Kyiv') + sales_nord | r | f | FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki') + sales_others | r | f | DEFAULT +(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 +(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 +(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 +(10 rows) + +-- Use indexscan for test indexes after merge 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 +(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 +(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 +(1 row) + +RESET enable_seqscan; +DROP TABLE sales_list; +-- +DROP SCHEMA partitions_merge_schema; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index e9184b5a409..0bd1444767b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -119,7 +119,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr # The stats test resets stats, so nothing else needing stats access can be in # this group. # ---------- -test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats predicate +test: partition_merge partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats predicate # event_trigger depends on create_am and cannot run concurrently with # any test that runs DDL diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql new file mode 100644 index 00000000000..bb461e6623a --- /dev/null +++ b/src/test/regress/sql/partition_merge.sql @@ -0,0 +1,430 @@ +-- +-- PARTITIONS_MERGE +-- Tests for "ALTER TABLE ... MERGE PARTITIONS ..." command +-- + +CREATE SCHEMA partitions_merge_schema; +SET search_path = partitions_merge_schema, public; + +-- +-- BY RANGE partitioning +-- + +-- +-- 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_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_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'); + +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; + +-- ERROR: partition with name "sales_feb2022" already used +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; +-- ERROR: cannot merge non-table partition "sales_apr2022" +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022; +-- ERROR: invalid partitions order, partition "sales_mar2022" can not be merged +-- (space between sections sales_jan2022 and sales_mar2022) +ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022) INTO sales_jan_mar2022; +-- ERROR: invalid partitions order, partition "sales_jan2022" can not be merged +-- (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; + +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; + +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_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 PARTITION OF sales_range FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); + +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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 pg_catalog.pg_get_partkeydef('sales_range'::regclass); + +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_range'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + +ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO 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; + +SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemaname = 'partitions_merge_schema'; + +SELECT * FROM sales_range; +SELECT * FROM sales_jan2022; +SELECT * FROM sales_feb_mar_apr2022; +SELECT * FROM sales_others; + +-- Use indexscan for test indexes +SET enable_seqscan = OFF; + +SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01'; + +RESET enable_seqscan; + +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_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 PARTITION OF sales_range FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); +CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); + +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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'); + +-- Merge partitions (include DEFAULT partition) into partition with the same +-- name +ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022, sales_others) INTO sales_others; + +select * from sales_others; + +-- 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; + +DROP TABLE sales_range; + +-- +-- Test for: +-- * composite partition key; +-- * GENERATED column; +-- * column with DEFAULT value. +-- +CREATE TABLE sales_date (salesman_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') + PARTITION BY RANGE (sales_year, sales_month, sales_day); + +CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1) TO (2022, 1, 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); + +SELECT * FROM sales_date; +SELECT * FROM sales_dec2022; +SELECT * FROM sales_jan2022; +SELECT * FROM sales_feb2022; +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); + +SELECT * FROM sales_date; +SELECT * FROM sales_dec2022; +SELECT * FROM sales_jan_feb2022; +SELECT * FROM sales_other; + +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 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 + AFTER INSERT + ON salesmans + FOR EACH STATEMENT + EXECUTE PROCEDURE after_insert_row_trigger('salesmans'); + +CREATE TRIGGER salesmans_after_insert_row_trigger + AFTER INSERT + ON salesmans + FOR EACH ROW + EXECUTE PROCEDURE after_insert_row_trigger('salesmans'); + +-- 2 triggers should fire here (row + statement): +INSERT INTO salesmans VALUES (10, 'May'); +-- 1 trigger should fire here (row): +INSERT INTO salesmans10_20 VALUES (19, 'Ivanov'); + +ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40; + +-- 2 triggers should fire here (row + statement): +INSERT INTO salesmans VALUES (20, 'Smirnoff'); +-- 1 trigger should fire here (row): +INSERT INTO salesmans10_40 VALUES (30, 'Ford'); + +SELECT * FROM salesmans01_10; +SELECT * FROM salesmans10_40; + +DROP TABLE salesmans; +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 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; +select * from salesmans01_10; +select * from salesmans10_40; + +DROP TABLE salesmans; + +-- +-- 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_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_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'); +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; + +CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date); + +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'); +INSERT INTO sales_range VALUES (4, 'Ivanov', 750, '2022-04-13'); +INSERT INTO sales_range VALUES (5, 'Deev', 250, '2022-04-07'); +INSERT INTO sales_range VALUES (6, 'Poirot', 150, '2022-02-11'); +INSERT INTO sales_range VALUES (7, 'Li', 175, '2022-03-08'); +INSERT INTO sales_range VALUES (8, 'Ericsson', 185, '2022-02-23'); +INSERT INTO sales_range VALUES (9, 'Muller', 250, '2022-03-11'); +INSERT INTO sales_range VALUES (10, 'Halder', 350, '2022-01-28'); +INSERT INTO sales_range VALUES (11, 'Trump', 380, '2022-04-06'); +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; +SELECT * FROM sales_apr2022; +SELECT * FROM sales_apr2022_01_10; +SELECT * FROM sales_apr2022_10_20; +SELECT * FROM sales_apr2022_20_30; + +ALTER TABLE sales_apr2022 MERGE PARTITIONS (sales_apr2022_01_10, sales_apr2022_10_20, sales_apr2022_20_30) INTO sales_apr_all; + +SELECT * FROM sales_range; +SELECT * FROM sales_apr2022; +SELECT * FROM sales_apr_all; + +DROP TABLE sales_range; + +-- +-- BY LIST partitioning +-- + +-- +-- Test: specific errors for BY LIST partitioning +-- +CREATE TABLE sales_list +(salesman_id INT GENERATED ALWAYS AS IDENTITY, + salesman_name VARCHAR(30), + sales_state VARCHAR(20), + sales_amount INT, + sales_date DATE) +PARTITION BY LIST (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; + + +CREATE TABLE sales_list2 (LIKE sales_list) PARTITION BY LIST (sales_state); +CREATE TABLE sales_nord2 PARTITION OF sales_list2 FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki'); +CREATE TABLE sales_others2 PARTITION OF sales_list2 DEFAULT; + + +CREATE TABLE sales_external (LIKE sales_list); +CREATE TABLE sales_external2 (vch VARCHAR(5)); + +-- ERROR: partition bound for relation "sales_external" is null +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external) INTO sales_all; +-- ERROR: partition bound for relation "sales_external2" is null +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external2) INTO sales_all; +-- ERROR: relation "sales_nord2" is not a partition of relation "sales_list" +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_nord2, sales_east) INTO sales_all; + +DROP TABLE sales_external2; +DROP TABLE sales_external; +DROP TABLE sales_list2; +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), + 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_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'); + +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_list'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + +ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) INTO sales_all; + +-- show partitions with conditions: +SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid) + FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i + WHERE c.oid = i.inhrelid AND i.inhparent = 'sales_list'::regclass + ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text; + +SELECT * FROM sales_list; +SELECT * FROM sales_nord; +SELECT * FROM sales_all; + +-- Use indexscan for test indexes after merge partitions +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'; + +RESET enable_seqscan; + +DROP TABLE sales_list; + +-- +DROP SCHEMA partitions_merge_schema; |