\set VERBOSITY terse SET search_path = 'public'; 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; /* 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 TO */ COPY copy_stmt_hooking.test TO stdout; /* not ok */ WARNING: COPY TO will only select rows from parent table "test" COPY copy_stmt_hooking.test (val) TO stdout; /* not ok */ WARNING: COPY TO will only select rows from parent table "test" COPY (SELECT * FROM copy_stmt_hooking.test) 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 (SELECT * FROM copy_stmt_hooking.test) TO stdout (FORMAT CSV); 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 (SELECT * FROM copy_stmt_hooking.test) 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 FROM (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 FROM (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) /* test transformed tuples */ COPY (SELECT * FROM copy_stmt_hooking.test) TO stdout; 1 0 0 6 0 0 7 0 0 11 0 0 16 0 0 21 0 0 26 1 2 /* COPY FROM (insert into table with dropped column) */ COPY copy_stmt_hooking.test(val, c3, c4) FROM stdin; /* COPY FROM (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;