\set VERBOSITY terse SET search_path = 'public'; CREATE EXTENSION pg_pathman; CREATE SCHEMA test_inserts; /* create a partitioned table */ CREATE TABLE test_inserts.storage(a INT4, b INT4 NOT NULL, c NUMERIC, d TEXT); INSERT INTO test_inserts.storage SELECT i * 2, i, i, i::text FROM generate_series(1, 100) i; CREATE UNIQUE INDEX ON test_inserts.storage(a); SELECT create_range_partitions('test_inserts.storage', 'b', 1, 10); /* attach before and after insertion triggers to partitioned table */ CREATE OR REPLACE FUNCTION test_inserts.print_cols_before_change() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'BEFORE INSERTION TRIGGER ON TABLE % HAS EXPIRED. INSERTED ROW: %', tg_table_name, new; RETURN new; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test_inserts.print_cols_after_change() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'AFTER INSERTION TRIGGER ON TABLE % HAS EXPIRED. INSERTED ROW: %', tg_table_name, new; RETURN new; END; $$ LANGUAGE plpgsql; /* set triggers on existing first partition and new generated partitions */ CREATE TRIGGER print_new_row_before_insert BEFORE INSERT ON test_inserts.storage_1 FOR EACH ROW EXECUTE PROCEDURE test_inserts.print_cols_before_change(); CREATE TRIGGER print_new_row_after_insert AFTER INSERT ON test_inserts.storage_1 FOR EACH ROW EXECUTE PROCEDURE test_inserts.print_cols_after_change(); /* set partition init callback that will add triggers to partitions */ CREATE OR REPLACE FUNCTION test_inserts.set_triggers(args jsonb) RETURNS VOID AS $$ BEGIN EXECUTE format('create trigger print_new_row_before_insert before insert on %s.%s for each row execute procedure test_inserts.print_cols_before_change();', args->>'partition_schema', args->>'partition'); EXECUTE format('create trigger print_new_row_after_insert after insert on %s.%s for each row execute procedure test_inserts.print_cols_after_change();', args->>'partition_schema', args->>'partition'); END; $$ LANGUAGE plpgsql; SELECT set_init_callback('test_inserts.storage', 'test_inserts.set_triggers(jsonb)'); /* we don't support ON CONLICT */ INSERT INTO test_inserts.storage VALUES(0, 0, 0, 'UNSUPPORTED_1') ON CONFLICT (a) DO UPDATE SET a = 3; INSERT INTO test_inserts.storage VALUES(0, 0, 0, 'UNSUPPORTED_2') ON CONFLICT (a) DO NOTHING; /* implicitly prepend a partition (no columns have been dropped yet) */ INSERT INTO test_inserts.storage VALUES(0, 0, 0, 'PREPEND.') RETURNING *; SELECT * FROM test_inserts.storage_11; INSERT INTO test_inserts.storage VALUES(1, 0, 0, 'PREPEND..') RETURNING tableoid::regclass; SELECT * FROM test_inserts.storage_11; INSERT INTO test_inserts.storage VALUES(3, 0, 0, 'PREPEND...') RETURNING a + b / 3; SELECT * FROM test_inserts.storage_11; /* cause an unique index conflict (a = 0) */ INSERT INTO test_inserts.storage VALUES(0, 0, 0, 'CONFLICT') RETURNING *; /* drop first column */ ALTER TABLE test_inserts.storage DROP COLUMN a CASCADE; /* will have 3 columns (b, c, d) */ SELECT append_range_partition('test_inserts.storage'); INSERT INTO test_inserts.storage (b, c, d) VALUES (101, 17, '3 cols!'); SELECT * FROM test_inserts.storage_12; /* direct access */ SELECT * FROM test_inserts.storage WHERE b > 100; /* via parent */ /* spawn a new partition (b, c, d) */ INSERT INTO test_inserts.storage (b, c, d) VALUES (111, 17, '3 cols as well!'); SELECT * FROM test_inserts.storage_13; /* direct access */ SELECT * FROM test_inserts.storage WHERE b > 110; /* via parent */ /* column 'a' has been dropped */ INSERT INTO test_inserts.storage VALUES(111, 0, 'DROP_COL_1.') RETURNING *, 17; INSERT INTO test_inserts.storage VALUES(111, 0, 'DROP_COL_1..') RETURNING tableoid::regclass; INSERT INTO test_inserts.storage VALUES(111, 0, 'DROP_COL_1...') RETURNING b * 2, b; /* drop third column */ ALTER TABLE test_inserts.storage DROP COLUMN c CASCADE; /* will have 2 columns (b, d) */ SELECT append_range_partition('test_inserts.storage'); INSERT INTO test_inserts.storage (b, d) VALUES (121, '2 cols!'); SELECT * FROM test_inserts.storage_14; /* direct access */ SELECT * FROM test_inserts.storage WHERE b > 120; /* via parent */ /* column 'c' has been dropped */ INSERT INTO test_inserts.storage VALUES(121, 'DROP_COL_2.') RETURNING *; INSERT INTO test_inserts.storage VALUES(121, 'DROP_COL_2..') RETURNING tableoid::regclass; INSERT INTO test_inserts.storage VALUES(121, 'DROP_COL_2...') RETURNING d || '0_0', b * 3; INSERT INTO test_inserts.storage VALUES(121, 'query_1') RETURNING (SELECT 1); INSERT INTO test_inserts.storage VALUES(121, 'query_2') RETURNING (SELECT generate_series(1, 10) LIMIT 1); INSERT INTO test_inserts.storage VALUES(121, 'query_3') RETURNING (SELECT attname FROM pathman_config WHERE partrel = 'test_inserts.storage'::regclass); INSERT INTO test_inserts.storage VALUES(121, 'query_4') RETURNING 1, 2, 3, 4; /* show number of columns in each partition */ SELECT partition, range_min, range_max, count(partition) FROM pathman_partition_list JOIN pg_attribute ON partition = attrelid WHERE attnum > 0 GROUP BY partition, range_min, range_max ORDER BY range_min::INT4; /* check the data */ SELECT *, tableoid::regclass FROM test_inserts.storage ORDER BY b, d; /* drop data */ TRUNCATE test_inserts.storage; /* one more time! */ INSERT INTO test_inserts.storage (b, d) SELECT i, i FROM generate_series(-2, 120) i; SELECT *, tableoid::regclass FROM test_inserts.storage ORDER BY b, d; /* drop data */ TRUNCATE test_inserts.storage; /* add new column */ ALTER TABLE test_inserts.storage ADD COLUMN e INT8 NOT NULL; /* one more time! x2 */ INSERT INTO test_inserts.storage (b, d, e) SELECT i, i, i FROM generate_series(-2, 120) i; SELECT *, tableoid::regclass FROM test_inserts.storage ORDER BY b, d; /* drop data */ TRUNCATE test_inserts.storage; /* now test RETURNING list using our new column 'e' */ INSERT INTO test_inserts.storage (b, d, e) SELECT i, i, i FROM generate_series(-2, 130, 5) i RETURNING e * 2, b, tableoid::regclass; DROP SCHEMA test_inserts CASCADE; DROP EXTENSION pg_pathman CASCADE;