\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); NOTICE: sequence "test_seq" does not exist, skipping 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 TO (partitioned column is not specified) */ COPY copy_stmt_hooking.test(comment) FROM stdin; ERROR: partitioned column's value should not be NULL /* delete all data */ SELECT drop_partitions('copy_stmt_hooking.test', true); NOTICE: function copy_stmt_hooking.test_upd_trig_func() does not exist, skipping drop_partitions ----------------- 5 (1 row) /* 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) DROP SCHEMA copy_stmt_hooking CASCADE; NOTICE: drop cascades to 7 other objects /* * Test auto check constraint renaming */ CREATE SCHEMA rename; 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 */ \d+ rename.test_one Table "rename.test_one" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+---------------------------------------------------------+---------+--------------+------------- a | integer | not null default nextval('rename.test_a_seq'::regclass) | plain | | b | integer | | plain | | Check constraints: "pathman_test_one_1_check" CHECK (get_hash_part_idx(hashint4(a), 3) = 0) Inherits: rename.test /* Generates check constraint for relation */ CREATE OR REPLACE FUNCTION add_constraint(rel regclass, att text) RETURNS VOID AS $$ declare constraint_name text := build_check_constraint_name(rel, 'a'); 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', 'a'); add_constraint ---------------- (1 row) ALTER TABLE rename.test_inh_1 RENAME TO test_inh_one; \d+ rename.test_inh_one Table "rename.test_inh_one" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+---------------------------------------------------------+---------+--------------+------------- a | integer | not null default nextval('rename.test_a_seq'::regclass) | plain | | b | integer | | plain | | Check constraints: "pathman_test_inh_1_1_check" CHECK (a < 100) Inherits: rename.test_inh /* 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', 'a'); add_constraint ---------------- (1 row) \d+ rename.plain_test_renamed Table "rename.plain_test_renamed" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+---------------------------------------------------------------+---------+--------------+------------- a | integer | not null default nextval('rename.plain_test_a_seq'::regclass) | plain | | b | integer | | plain | | Check constraints: "pathman_plain_test_renamed_1_check" CHECK (a < 100) ALTER TABLE rename.plain_test_renamed RENAME TO plain_test; \d+ rename.plain_test Table "rename.plain_test" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+---------------------------------------------------------------+---------+--------------+------------- a | integer | not null default nextval('rename.plain_test_a_seq'::regclass) | plain | | b | integer | | plain | | Check constraints: "pathman_plain_test_renamed_1_check" CHECK (a < 100) DROP SCHEMA rename CASCADE; NOTICE: drop cascades to 7 other objects DROP EXTENSION pg_pathman;