\set VERBOSITY terse SET search_path = 'public'; CREATE EXTENSION pg_pathman; CREATE SCHEMA dropped_cols; /* * we should be able to manage tables with dropped columns */ create table test_range(a int, b int, key int not null); alter table test_range drop column a; select create_range_partitions('test_range', 'key', 1, 10, 2); create_range_partitions ------------------------- 2 (1 row) alter table test_range drop column b; select prepend_range_partition('test_range'); prepend_range_partition ------------------------- test_range_3 (1 row) select * from pathman_partition_list order by parent, partition; parent | partition | parttype | expr | range_min | range_max ------------+--------------+----------+------+-----------+----------- test_range | test_range_1 | 2 | key | 1 | 11 test_range | test_range_2 | 2 | key | 11 | 21 test_range | test_range_3 | 2 | key | -9 | 1 (3 rows) select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathman_test_range_1_check'; pg_get_constraintdef ------------------------------- CHECK (key >= 1 AND key < 11) (1 row) select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathman_test_range_3_check'; pg_get_constraintdef ------------------------------------------ CHECK (key >= '-9'::integer AND key < 1) (1 row) drop table test_range cascade; NOTICE: drop cascades to 4 other objects create table test_hash(a int, b int, key int not null); alter table test_hash drop column a; select create_hash_partitions('test_hash', 'key', 3); create_hash_partitions ------------------------ 3 (1 row) alter table test_hash drop column b; create table test_dummy (like test_hash); select replace_hash_partition('test_hash_2', 'test_dummy', true); replace_hash_partition ------------------------ test_dummy (1 row) select * from pathman_partition_list order by parent, partition; parent | partition | parttype | expr | range_min | range_max -----------+-------------+----------+------+-----------+----------- test_hash | test_hash_0 | 1 | key | | test_hash | test_hash_1 | 1 | key | | test_hash | test_dummy | 1 | key | | (3 rows) select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathman_test_hash_1_check'; pg_get_constraintdef ------------------------------------------------- CHECK (get_hash_part_idx(hashint4(key), 3) = 1) (1 row) select pg_get_constraintdef(oid, true) from pg_constraint where conname = 'pathman_test_dummy_check'; pg_get_constraintdef ------------------------------------------------- CHECK (get_hash_part_idx(hashint4(key), 3) = 2) (1 row) drop table test_hash cascade; NOTICE: drop cascades to 3 other objects -- Yury Smirnov case CREATE TABLE root_dict ( id BIGSERIAL PRIMARY KEY NOT NULL, root_id BIGINT NOT NULL, start_date DATE, num TEXT, main TEXT, dict_code TEXT, dict_name TEXT, edit_num TEXT, edit_date DATE, sign CHAR(4) ); CREATE INDEX "root_dict_root_id_idx" ON "root_dict" ("root_id"); DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM generate_series(1, 3) r LOOP FOR d IN 1..2 LOOP INSERT INTO root_dict (root_id, start_date, num, main, dict_code, dict_name, edit_num, edit_date, sign) VALUES (r.r, '2010-10-10'::date, 'num_' || d, (d % 2) + 1, 'code_' || d, 'name_' || d, NULL, NULL, '2014'); END LOOP; END LOOP; END $$; ALTER TABLE root_dict ADD COLUMN dict_id BIGINT DEFAULT 3; ALTER TABLE root_dict DROP COLUMN dict_code, DROP COLUMN dict_name, DROP COLUMN sign; SELECT create_hash_partitions('root_dict' :: REGCLASS, 'root_id', 3, true); create_hash_partitions ------------------------ 3 (1 row) VACUUM FULL ANALYZE "root_dict"; SELECT set_enable_parent('root_dict' :: REGCLASS, FALSE); set_enable_parent ------------------- (1 row) PREPARE getbyroot AS SELECT id, root_id, start_date, num, main, edit_num, edit_date, dict_id FROM root_dict WHERE root_id = $1; EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) -- errors usually start here EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXECUTE getbyroot(2); id | root_id | start_date | num | main | edit_num | edit_date | dict_id ----+---------+------------+-------+------+----------+-----------+--------- 3 | 2 | 10-10-2010 | num_1 | 2 | | | 3 4 | 2 | 10-10-2010 | num_2 | 1 | | | 3 (2 rows) EXPLAIN EXECUTE getbyroot(2); QUERY PLAN -------------------------------------------------------------------------------------------- Custom Scan (RuntimeAppend) (cost=4.17..11.28 rows=3 width=128) Prune by: (root_dict.root_id = $1) -> Bitmap Heap Scan on root_dict_0 root_dict (cost=4.17..11.28 rows=3 width=128) Recheck Cond: (root_id = $1) -> Bitmap Index Scan on root_dict_0_root_id_idx (cost=0.00..4.17 rows=3 width=0) Index Cond: (root_id = $1) -> Bitmap Heap Scan on root_dict_1 root_dict (cost=4.17..11.28 rows=3 width=128) Recheck Cond: (root_id = $1) -> Bitmap Index Scan on root_dict_1_root_id_idx (cost=0.00..4.17 rows=3 width=0) Index Cond: (root_id = $1) -> Bitmap Heap Scan on root_dict_2 root_dict (cost=4.17..11.28 rows=3 width=128) Recheck Cond: (root_id = $1) -> Bitmap Index Scan on root_dict_2_root_id_idx (cost=0.00..4.17 rows=3 width=0) Index Cond: (root_id = $1) (14 rows) DEALLOCATE getbyroot; DROP TABLE root_dict CASCADE; NOTICE: drop cascades to 3 other objects DROP SCHEMA dropped_cols CASCADE; DROP EXTENSION pg_pathman;