- Notifications
You must be signed in to change notification settings - Fork67
Known limitations
This a non-exhaustive list of known limitations of pg_pathman:
Currently you can't create multilevel partitioning scheme.
createtabletest(idintnot null, val float8);createunique indexon test (id);select create_range_partitions('test','id',1,10,3);insert into testvalues (0,0)on conflict (id) doupdateset val=1;ERROR:ON CONFLICT clause is not supported with partitioned tables
createtabletest(idintprimary key,datetimestampnot null);select create_range_partitions('test','date', now(),'1 day'::interval,4);insert into testvalues(1, now());insert into testvalues(1, now()+'1 day');table test; id |date----+----------------------------1 |2017-10-0316:33:24.9530121 |2017-10-0416:33:31.476003(2 rows)
createtabletest(idintnot null);createunique indexon test (id);select create_range_partitions('test','id',1,10,3);insert into testvalues (1);createtablesimple(idintreferences test(id));insert into simplevalues (1);ERROR: insertorupdateon table"simple" violatesforeign keyconstraint"simple_id_fkey"DETAIL: Key (id)=(1) is not presentin table"test".
This includes Londiste, pg_logical etc. Instead, take a look atpg_shardman.
Due to significant API changes, these optimizations are not available on 9.5:
- Faster partition pruning in
SELECT ...subqueries under UPDATE and DELETE statements; - Faster partition pruning in
SELECT ... FOR SHARE/UPDATE/etc.
Seethis page for more information.
We've noticed that certain queries cannot be planned by pg_pathman:
DELETE FROM partitioned_table_1 USING partitioned_table_2 ...;UPDATE partitioned_table_1 FROM partitioned_table_2 ...;
This is due to some limitations of PostgreSQL's planner: we can't hook inheritance planner used for DELETE and UPDATE queries on partitioned tables. As a result, we can't plan and execute queries that are going to modifyseveral partitions:
createtablea(valintnot null);select create_range_partitions('a','val',1,10,3);createtableb(valintnot null);select create_range_partitions('b','val',1,10,3);deletefrom a using bwherea.val=b.val;ERROR:DELETEandUPDATE queries with ajoin of partitioned tables are not supported
However, query will work as expected if it'sobvious to the planner that it wants to modify only 1 (or 0) partition:
explain (costs off)deletefrom a using bwherea.val=b.valanda.val<10; QUERY PLAN-----------------------------------------Deleteon a_1-> MergeJoin Merge Cond: (a_1.val=b_1.val)-> Sort Sort Key:a_1.val-> Seq Scanon a_1 Filter: (val<10)-> Sort Sort Key:b_1.val-> Append-> Seq Scanon b_1-> Seq Scanon b_2-> Seq Scanon b_3(13 rows)
As a workaround, you can use WITH statement to hide the joined partitioned table:
explain (costs off) with qas (select*from b)deletefrom a using qwherea.val=q.val; QUERY PLAN---------------------------------------Deleteon aDeleteon aDeleteon a_1Deleteon a_2Deleteon a_3 CTE q-> Append-> Seq Scanon b_1-> Seq Scanon b_2-> Seq Scanon b_3-> HashJoin Hash Cond: (q.val=a.val)-> CTE Scanon q-> Hash-> Seq Scanon a-> MergeJoin Merge Cond: (a_1.val=q.val)-> Sort Sort Key:a_1.val-> Seq Scanon a_1-> Sort Sort Key:q.val-> CTE Scanon q-> MergeJoin Merge Cond: (a_2.val=q.val)-> Sort Sort Key:a_2.val-> Seq Scanon a_2-> Sort Sort Key:q.val-> CTE Scanon q-> MergeJoin Merge Cond: (a_3.val=q.val)-> Sort Sort Key:a_3.val-> Seq Scanon a_3-> Sort Sort Key:q.val-> CTE Scanon q(39 rows)