Known limitations

This a non-exhaustive list of known limitations of pg_pathman:

UPDATE and DELETE queries on a partitioned table joined with another partitioned table

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 modify several partitions:

create table a(val int not null);
select create_range_partitions('a', 'val', 1, 10, 3);
create table b(val int not null);
select create_range_partitions('b', 'val', 1, 10, 3);

delete from a using b where a.val = b.val;
ERROR:  DELETE and UPDATE queries with a join of partitioned tables are not supported

However, query will work as expected if it's obvious to the planner that it wants to modify only 1 (or 0) partition:

explain (costs off)
delete from a using b where a.val = b.val and a.val < 10;
               QUERY PLAN
-----------------------------------------
 Delete on a_1
   ->  Merge Join
         Merge Cond: (a_1.val = b_1.val)
         ->  Sort
               Sort Key: a_1.val
               ->  Seq Scan on a_1
                     Filter: (val < 10)
         ->  Sort
               Sort Key: b_1.val
               ->  Append
                     ->  Seq Scan on b_1
                     ->  Seq Scan on b_2
                     ->  Seq Scan on b_3
(13 rows)

As a workaround, you can use WITH statement to hide the joined partitioned table:

explain (costs off) 
with q as (select * from b)
delete from a using q where a.val = q.val;
              QUERY PLAN               
---------------------------------------
 Delete on a
   Delete on a
   Delete on a_1
   Delete on a_2
   Delete on a_3
   CTE q
     ->  Append
           ->  Seq Scan on b_1
           ->  Seq Scan on b_2
           ->  Seq Scan on b_3
   ->  Hash Join
         Hash Cond: (q.val = a.val)
         ->  CTE Scan on q
         ->  Hash
               ->  Seq Scan on a
   ->  Merge Join
         Merge Cond: (a_1.val = q.val)
         ->  Sort
               Sort Key: a_1.val
               ->  Seq Scan on a_1
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
   ->  Merge Join
         Merge Cond: (a_2.val = q.val)
         ->  Sort
               Sort Key: a_2.val
               ->  Seq Scan on a_2
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
   ->  Merge Join
         Merge Cond: (a_3.val = q.val)
         ->  Sort
               Sort Key: a_3.val
               ->  Seq Scan on a_3
         ->  Sort
               Sort Key: q.val
               ->  CTE Scan on q
(39 rows)