path: root/src/test/regress/expected/partition_merge.out

--
-- 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:  "sales_apr2022" is not a table
ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022;
ERROR:  "sales_apr2022" is not a table
-- 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:  "sales_external" is not a partition
ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external) INTO sales_all;
ERROR:  "sales_external" is not a partition
-- ERROR:  "sales_external2" is not a partition
ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_external2) INTO sales_all;
ERROR:  "sales_external2" is not a partition
-- 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;
--
-- Try to MERGE partitions of another table.
--
CREATE TABLE t1 (i int, a int, b int, c int) PARTITION BY RANGE (a, b);
CREATE TABLE t1p1 PARTITION OF t1 FOR VALUES FROM (1, 1) TO (1, 2);
CREATE TABLE t2 (i int, t text) PARTITION BY RANGE (t);
CREATE TABLE t2pa PARTITION OF t2 FOR VALUES FROM ('A') TO ('C');
CREATE TABLE t3 (i int, t text);
-- ERROR:  relation "t1p1" is not a partition of relation "t2"
ALTER TABLE t2 MERGE PARTITIONS (t1p1, t2pa) INTO t2p;
ERROR:  relation "t1p1" is not a partition of relation "t2"
-- ERROR:  "t3" is not a partition
ALTER TABLE t2 MERGE PARTITIONS (t2pa, t3) INTO t2p;
ERROR:  "t3" is not a partition
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
--
DROP SCHEMA partitions_merge_schema;