\set VERBOSITY terse
CREATE EXTENSION pg_pathman;
/*
 * Test COPY
 */
CREATE SCHEMA copy_stmt_hooking;
CREATE TABLE copy_stmt_hooking.test(
	val int not null,
	comment text,
	c3 int,
	c4 int);
INSERT INTO copy_stmt_hooking.test SELECT generate_series(1, 20), 'comment';
CREATE INDEX ON copy_stmt_hooking.test(val);
/* test for RANGE partitioning */
SELECT create_range_partitions('copy_stmt_hooking.test', 'val', 1, 5);
 create_range_partitions 
-------------------------
                       4
(1 row)

/* perform VACUUM */
VACUUM FULL copy_stmt_hooking.test;
VACUUM FULL copy_stmt_hooking.test_1;
VACUUM FULL copy_stmt_hooking.test_2;
VACUUM FULL copy_stmt_hooking.test_3;
VACUUM FULL copy_stmt_hooking.test_4;
/* COPY TO */
COPY copy_stmt_hooking.test TO stdout;
1	comment	\N	\N
2	comment	\N	\N
3	comment	\N	\N
4	comment	\N	\N
5	comment	\N	\N
6	comment	\N	\N
7	comment	\N	\N
8	comment	\N	\N
9	comment	\N	\N
10	comment	\N	\N
11	comment	\N	\N
12	comment	\N	\N
13	comment	\N	\N
14	comment	\N	\N
15	comment	\N	\N
16	comment	\N	\N
17	comment	\N	\N
18	comment	\N	\N
19	comment	\N	\N
20	comment	\N	\N
\copy copy_stmt_hooking.test to stdout (format csv)
1,comment,,
2,comment,,
3,comment,,
4,comment,,
5,comment,,
6,comment,,
7,comment,,
8,comment,,
9,comment,,
10,comment,,
11,comment,,
12,comment,,
13,comment,,
14,comment,,
15,comment,,
16,comment,,
17,comment,,
18,comment,,
19,comment,,
20,comment,,
\copy copy_stmt_hooking.test(comment) to stdout
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
comment
/* DELETE ROWS, COPY FROM */
DELETE FROM copy_stmt_hooking.test;
COPY copy_stmt_hooking.test FROM stdin;
SELECT count(*) FROM ONLY copy_stmt_hooking.test;
 count 
-------
     0
(1 row)

SELECT *, tableoid::REGCLASS FROM copy_stmt_hooking.test ORDER BY val;
 val | comment | c3 | c4 |         tableoid         
-----+---------+----+----+--------------------------
   1 | test_1  |  0 |  0 | copy_stmt_hooking.test_1
   6 | test_2  |  0 |  0 | copy_stmt_hooking.test_2
   7 | test_2  |  0 |  0 | copy_stmt_hooking.test_2
  11 | test_3  |  0 |  0 | copy_stmt_hooking.test_3
  16 | test_4  |  0 |  0 | copy_stmt_hooking.test_4
(5 rows)

/* perform VACUUM */
VACUUM FULL copy_stmt_hooking.test;
VACUUM FULL copy_stmt_hooking.test_1;
VACUUM FULL copy_stmt_hooking.test_2;
VACUUM FULL copy_stmt_hooking.test_3;
VACUUM FULL copy_stmt_hooking.test_4;
/* COPY FROM (specified columns) */
COPY copy_stmt_hooking.test (val) TO stdout;
1
6
7
11
16
COPY copy_stmt_hooking.test (val, comment) TO stdout;
1	test_1
6	test_2
7	test_2
11	test_3
16	test_4
COPY copy_stmt_hooking.test (c3, val, comment) TO stdout;
0	1	test_1
0	6	test_2
0	7	test_2
0	11	test_3
0	16	test_4
COPY copy_stmt_hooking.test (val, comment, c3, c4) TO stdout;
1	test_1	0	0
6	test_2	0	0
7	test_2	0	0
11	test_3	0	0
16	test_4	0	0
/* COPY TO (partition does not exist, NOT allowed to create partitions) */
SET pg_pathman.enable_auto_partition = OFF;
COPY copy_stmt_hooking.test FROM stdin;
ERROR:  no suitable partition for key '21'
SELECT * FROM copy_stmt_hooking.test WHERE val > 20;
 val | comment | c3 | c4 
-----+---------+----+----
(0 rows)

/* COPY TO (partition does not exist, allowed to create partitions) */
SET pg_pathman.enable_auto_partition = ON;
COPY copy_stmt_hooking.test FROM stdin;
SELECT * FROM copy_stmt_hooking.test WHERE val > 20;
 val |   comment    | c3 | c4 
-----+--------------+----+----
  21 | test_no_part |  0 |  0
(1 row)

/* COPY FROM (partitioned column is not specified) */
COPY copy_stmt_hooking.test(comment) FROM stdin;
ERROR:  partitioning expression's value should not be NULL
/* COPY FROM (we don't support FREEZE) */
COPY copy_stmt_hooking.test FROM stdin WITH (FREEZE);
ERROR:  freeze is not supported for partitioned tables
/* Drop column (make use of 'tuple_map') */
ALTER TABLE copy_stmt_hooking.test DROP COLUMN comment;
/* create new partition */
SELECT get_number_of_partitions('copy_stmt_hooking.test');
 get_number_of_partitions 
--------------------------
                        5
(1 row)

INSERT INTO copy_stmt_hooking.test (val, c3, c4) VALUES (26, 1, 2);
SELECT get_number_of_partitions('copy_stmt_hooking.test');
 get_number_of_partitions 
--------------------------
                        6
(1 row)

/* check number of columns in 'test' */
SELECT count(*) FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'copy_stmt_hooking.test'::REGCLASS;
 count 
-------
     4
(1 row)

/* check number of columns in 'test_6' */
SELECT count(*) FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'copy_stmt_hooking.test_6'::REGCLASS;
 count 
-------
     3
(1 row)

/* COPY FROM (test transformed tuples) */
COPY copy_stmt_hooking.test (val, c3, c4) TO stdout;
1	0	0
6	0	0
7	0	0
11	0	0
16	0	0
21	0	0
26	1	2
/* COPY TO (insert into table with dropped column) */
COPY copy_stmt_hooking.test(val, c3, c4) FROM stdin;
/* COPY TO (insert into table without dropped column) */
COPY copy_stmt_hooking.test(val, c3, c4) FROM stdin;
/* check tuples from last partition (without dropped column) */
SELECT *, tableoid::REGCLASS FROM copy_stmt_hooking.test ORDER BY val;
 val | c3 | c4 |         tableoid         
-----+----+----+--------------------------
   1 |  0 |  0 | copy_stmt_hooking.test_1
   2 |  1 |  2 | copy_stmt_hooking.test_1
   6 |  0 |  0 | copy_stmt_hooking.test_2
   7 |  0 |  0 | copy_stmt_hooking.test_2
  11 |  0 |  0 | copy_stmt_hooking.test_3
  16 |  0 |  0 | copy_stmt_hooking.test_4
  21 |  0 |  0 | copy_stmt_hooking.test_5
  26 |  1 |  2 | copy_stmt_hooking.test_6
  27 |  1 |  2 | copy_stmt_hooking.test_6
(9 rows)

/* drop modified table */
DROP TABLE copy_stmt_hooking.test CASCADE;
NOTICE:  drop cascades to 7 other objects
/* create table again */
CREATE TABLE copy_stmt_hooking.test(
	val int not null,
	comment text,
	c3 int,
	c4 int);
CREATE INDEX ON copy_stmt_hooking.test(val);
/* test for HASH partitioning */
SELECT create_hash_partitions('copy_stmt_hooking.test', 'val', 5);
 create_hash_partitions 
------------------------
                      5
(1 row)

/* DELETE ROWS, COPY FROM */
DELETE FROM copy_stmt_hooking.test;
COPY copy_stmt_hooking.test FROM stdin;
SELECT count(*) FROM ONLY copy_stmt_hooking.test;
 count 
-------
     0
(1 row)

SELECT * FROM copy_stmt_hooking.test ORDER BY val;
 val | comment | c3 | c4 
-----+---------+----+----
   1 | hash_1  |  0 |  0
   6 | hash_2  |  0 |  0
(2 rows)

/* Check dropped colums before partitioning */
CREATE TABLE copy_stmt_hooking.test2 (
	a varchar(50),
	b varchar(50),
	t timestamp without time zone not null
);
ALTER TABLE copy_stmt_hooking.test2 DROP COLUMN a;
SELECT create_range_partitions('copy_stmt_hooking.test2',
	't',
	'2017-01-01 00:00:00'::timestamp,
	interval '1 hour', 5, false
);
 create_range_partitions 
-------------------------
                       5
(1 row)

COPY copy_stmt_hooking.test2(t) FROM stdin;
SELECT COUNT(*) FROM copy_stmt_hooking.test2;
 count 
-------
     1
(1 row)

DROP SCHEMA copy_stmt_hooking CASCADE;
NOTICE:  drop cascades to 797 other objects
/*
 * Test auto check constraint renaming
 */
CREATE SCHEMA rename;
/*
 * Check that auto naming sequence is renamed
 */
CREATE TABLE rename.parent(id int not null);
SELECT create_range_partitions('rename.parent', 'id', 1, 2, 2);
 create_range_partitions 
-------------------------
                       2
(1 row)

SELECT 'rename.parent'::regclass;		/* parent is OK */
   regclass    
---------------
 rename.parent
(1 row)

SELECT 'rename.parent_seq'::regclass;	/* sequence is OK */
     regclass      
-------------------
 rename.parent_seq
(1 row)

ALTER TABLE rename.parent RENAME TO parent_renamed;
SELECT 'rename.parent_renamed'::regclass;		/* parent is OK */
       regclass        
-----------------------
 rename.parent_renamed
(1 row)

SELECT 'rename.parent_renamed_seq'::regclass;	/* sequence is OK */
         regclass          
---------------------------
 rename.parent_renamed_seq
(1 row)

SELECT append_range_partition('rename.parent_renamed'); /* can append */
 append_range_partition  
-------------------------
 rename.parent_renamed_3
(1 row)

DROP SEQUENCE rename.parent_renamed_seq;
ALTER TABLE rename.parent_renamed RENAME TO parent;
SELECT 'rename.parent'::regclass;		/* parent is OK */
   regclass    
---------------
 rename.parent
(1 row)

/*
 * Check that partitioning constraints are renamed
 */
CREATE TABLE rename.test(a serial, b int);
SELECT create_hash_partitions('rename.test', 'a', 3);
 create_hash_partitions 
------------------------
                      3
(1 row)

ALTER TABLE rename.test_0 RENAME TO test_one;
/* We expect to find check constraint renamed as well */
SELECT r.conname, pg_get_constraintdef(r.oid, true)
FROM pg_constraint r
WHERE r.conrelid = 'rename.test_one'::regclass AND r.contype = 'c';
        conname         |             pg_get_constraintdef              
------------------------+-----------------------------------------------
 pathman_test_one_check | CHECK (get_hash_part_idx(hashint4(a), 3) = 0)
(1 row)

/* Generates check constraint for relation */
CREATE OR REPLACE FUNCTION add_constraint(rel regclass)
RETURNS VOID AS $$
declare
	constraint_name text := build_check_constraint_name(rel);
BEGIN
	EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (a < 100);',
				   rel, constraint_name);
END
$$
LANGUAGE plpgsql;
/*
 * Check that it doesn't affect regular inherited
 * tables that aren't managed by pg_pathman
 */
CREATE TABLE rename.test_inh (LIKE rename.test INCLUDING ALL);
CREATE TABLE rename.test_inh_1 (LIKE rename.test INCLUDING ALL);
ALTER TABLE rename.test_inh_1 INHERIT rename.test_inh;
SELECT add_constraint('rename.test_inh_1');
 add_constraint 
----------------
 
(1 row)

ALTER TABLE rename.test_inh_1 RENAME TO test_inh_one;
/* Show check constraints of rename.test_inh_one */
SELECT r.conname, pg_get_constraintdef(r.oid, true)
FROM pg_constraint r
WHERE r.conrelid = 'rename.test_inh_one'::regclass AND r.contype = 'c';
         conname          | pg_get_constraintdef 
--------------------------+----------------------
 pathman_test_inh_1_check | CHECK (a < 100)
(1 row)

/*
 * Check that plain tables are not affected too
 */
CREATE TABLE rename.plain_test(a serial, b int);
ALTER TABLE rename.plain_test RENAME TO plain_test_renamed;
SELECT add_constraint('rename.plain_test_renamed');
 add_constraint 
----------------
 
(1 row)

/* Show check constraints of rename.plain_test_renamed */
SELECT r.conname, pg_get_constraintdef(r.oid, true)
FROM pg_constraint r
WHERE r.conrelid = 'rename.plain_test_renamed'::regclass AND r.contype = 'c';
             conname              | pg_get_constraintdef 
----------------------------------+----------------------
 pathman_plain_test_renamed_check | CHECK (a < 100)
(1 row)

ALTER TABLE rename.plain_test_renamed RENAME TO plain_test;
/* ... and check constraints of rename.plain_test */
SELECT r.conname, pg_get_constraintdef(r.oid, true)
FROM pg_constraint r
WHERE r.conrelid = 'rename.plain_test'::regclass AND r.contype = 'c';
             conname              | pg_get_constraintdef 
----------------------------------+----------------------
 pathman_plain_test_renamed_check | CHECK (a < 100)
(1 row)

DROP SCHEMA rename CASCADE;
NOTICE:  drop cascades to 11 other objects
/*
 * Test DROP INDEX CONCURRENTLY (test snapshots)
 */
CREATE SCHEMA drop_index;
CREATE TABLE drop_index.test (val INT4 NOT NULL);
CREATE INDEX ON drop_index.test (val);
SELECT create_hash_partitions('drop_index.test', 'val', 2);
 create_hash_partitions 
------------------------
                      2
(1 row)

DROP INDEX CONCURRENTLY drop_index.test_0_val_idx;
DROP SCHEMA drop_index CASCADE;
NOTICE:  drop cascades to 3 other objects
DROP EXTENSION pg_pathman;