Expand partitioned table RTEs level by level, without flattening.

Flattening the partitioning hierarchy at this stage makes various
desirable optimizations difficult.  The original use case for this
patch was partition-wise join, which wants to match up the partitions
in one partitioning hierarchy with those in another such hierarchy.
However, it now seems that it will also be useful in making partition
pruning work using the PartitionDesc rather than constraint exclusion,
because with a flattened expansion, we have no easy way to figure out
which PartitionDescs apply to which leaf tables in a multi-level
partition hierarchy.

As it turns out, we end up creating both rte->inh and !rte->inh RTEs
for each intermediate partitioned table, just as we previously did for
the root table.  This seems unnecessary since the partitioned tables
have no storage and are not scanned.  We might want to go back and
rejigger things so that no partitioned tables (including the parent)
need !rte->inh RTEs, but that seems to require some adjustments not
related to the core purpose of this patch.

Ashutosh Bapat, reviewed by me and by Amit Langote.  Some final
adjustments by me.

Discussion: http://postgr.es/m/CAFjFpRd=1venqLL7oGU=C1dEkuvk2DJgvF+7uKbnPHaum1mvHQ@mail.gmail.com

4 files changed, 115 insertions, 0 deletions

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 1fa9650ec97..2fb0b4d86ec 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -625,6 +625,28 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b
 (3 rows)
 
 drop table parted_tab;
+-- Check UPDATE with multi-level partitioned inherited target
+create table mlparted_tab (a int, b char, c text) partition by list (a);
+create table mlparted_tab_part1 partition of mlparted_tab for values in (1);
+create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b);
+create table mlparted_tab_part3 partition of mlparted_tab for values in (3);
+create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a');
+create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b');
+insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a');
+update mlparted_tab mlp set c = 'xxx'
+from
+  (select a from some_tab union all select a+1 from some_tab) ss (a)
+where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
+select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2;
+       relname       | a | b |  c  
+---------------------+---+---+-----
+ mlparted_tab_part1  | 1 | a | 
+ mlparted_tab_part2a | 2 | a | 
+ mlparted_tab_part2b | 2 | b | xxx
+ mlparted_tab_part3  | 3 | a | xxx
+(4 rows)
+
+drop table mlparted_tab;
 drop table some_tab cascade;
 NOTICE:  drop cascades to table some_tab_child
 /* Test multiple inheritance of column defaults */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9f4c88dab4e..06a84e8e1c7 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5328,6 +5328,59 @@ LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
 HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
 --
+-- test LATERAL reference propagation down a multi-level inheritance hierarchy
+-- produced for a multi-level partitioned table hierarchy.
+--
+create table pt1 (a int, b int, c varchar) partition by range(a);
+create table pt1p1 partition of pt1 for values from (0) to (100) partition by range(b);
+create table pt1p2 partition of pt1 for values from (100) to (200);
+create table pt1p1p1 partition of pt1p1 for values from (0) to (100);
+insert into pt1 values (1, 1, 'x'), (101, 101, 'y');
+create table ut1 (a int, b int, c varchar);
+insert into ut1 values (101, 101, 'y'), (2, 2, 'z');
+explain (verbose, costs off)
+select t1.b, ss.phv from ut1 t1 left join lateral
+              (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+					  from pt1 t2 join ut1 t3 on t2.a = t3.b) ss
+              on t1.a = ss.t2a order by t1.a;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Sort
+   Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+   Sort Key: t1.a
+   ->  Nested Loop Left Join
+         Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+         ->  Seq Scan on public.ut1 t1
+               Output: t1.a, t1.b, t1.c
+         ->  Hash Join
+               Output: t2.a, LEAST(t1.a, t2.a, t3.a)
+               Hash Cond: (t3.b = t2.a)
+               ->  Seq Scan on public.ut1 t3
+                     Output: t3.a, t3.b, t3.c
+               ->  Hash
+                     Output: t2.a
+                     ->  Append
+                           ->  Seq Scan on public.pt1p1p1 t2
+                                 Output: t2.a
+                                 Filter: (t1.a = t2.a)
+                           ->  Seq Scan on public.pt1p2 t2_1
+                                 Output: t2_1.a
+                                 Filter: (t1.a = t2_1.a)
+(21 rows)
+
+select t1.b, ss.phv from ut1 t1 left join lateral
+              (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+					  from pt1 t2 join ut1 t3 on t2.a = t3.b) ss
+              on t1.a = ss.t2a order by t1.a;
+  b  | phv 
+-----+-----
+   2 |    
+ 101 | 101
+(2 rows)
+
+drop table pt1;
+drop table ut1;
+--
 -- test that foreign key join estimation performs sanely for outer joins
 --
 begin;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index c96580cd814..01780d49773 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -154,6 +154,23 @@ where parted_tab.a = ss.a;
 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
 
 drop table parted_tab;
+
+-- Check UPDATE with multi-level partitioned inherited target
+create table mlparted_tab (a int, b char, c text) partition by list (a);
+create table mlparted_tab_part1 partition of mlparted_tab for values in (1);
+create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b);
+create table mlparted_tab_part3 partition of mlparted_tab for values in (3);
+create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a');
+create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b');
+insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a');
+
+update mlparted_tab mlp set c = 'xxx'
+from
+  (select a from some_tab union all select a+1 from some_tab) ss (a)
+where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
+select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2;
+
+drop table mlparted_tab;
 drop table some_tab cascade;
 
 /* Test multiple inheritance of column defaults */
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 835d67551cd..8b21838e927 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1734,6 +1734,29 @@ delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
 delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
 
 --
+-- test LATERAL reference propagation down a multi-level inheritance hierarchy
+-- produced for a multi-level partitioned table hierarchy.
+--
+create table pt1 (a int, b int, c varchar) partition by range(a);
+create table pt1p1 partition of pt1 for values from (0) to (100) partition by range(b);
+create table pt1p2 partition of pt1 for values from (100) to (200);
+create table pt1p1p1 partition of pt1p1 for values from (0) to (100);
+insert into pt1 values (1, 1, 'x'), (101, 101, 'y');
+create table ut1 (a int, b int, c varchar);
+insert into ut1 values (101, 101, 'y'), (2, 2, 'z');
+explain (verbose, costs off)
+select t1.b, ss.phv from ut1 t1 left join lateral
+              (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+					  from pt1 t2 join ut1 t3 on t2.a = t3.b) ss
+              on t1.a = ss.t2a order by t1.a;
+select t1.b, ss.phv from ut1 t1 left join lateral
+              (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
+					  from pt1 t2 join ut1 t3 on t2.a = t3.b) ss
+              on t1.a = ss.t2a order by t1.a;
+
+drop table pt1;
+drop table ut1;
+--
 -- test that foreign key join estimation performs sanely for outer joins
 --