Fix some interrelated planner issues with initPlans and Param munging.

In commit 68fa28f77 I tried to teach SS_finalize_plan() to cope with
initPlans attached anywhere in the plan tree, by dint of moving its
handling of those into the recursion in finalize_plan().  It turns out that
that doesn't really work: if a lower-level plan node emits an initPlan
output parameter in its targetlist, it's legitimate for upper levels to
reference those Params --- and at the point where this code runs, those
references look just like the Param itself, so finalize_plan() quite
properly rejects them as being in the wrong place.  We could lobotomize
the checks enough to allow that, probably, but then it's not clear that
we'd have any meaningful check for misplaced Params at all.  What seems
better, at least in the near term, is to tweak standard_planner() a bit
so that initPlans are never placed anywhere but the topmost plan node
for a query level, restoring the behavior that occurred pre-9.6.  Possibly
we can do better if this code is ever merged into setrefs.c: then it would
be possible to check a Param's placement only when we'd failed to replace
it with a Var referencing a child plan node's targetlist.

BTW, I'm now suspicious that finalize_plan is doing the wrong thing by
returning the node's allParam rather than extParam to be incorporated
in the parent node's set of used parameters.  However, it makes no
difference given that initPlans only appear at top level, so I'll leave
that alone for now.

Another thing that emerged from this is that standard_planner() needs
to check for initPlans before deciding that it's safe to stick a Gather
node on top in force_parallel_mode mode.  We previously guarded against
that by deciding the plan wasn't wholePlanParallelSafe if any subplans
had been found, but after commit 5ce5e4a12 it's necessary to have this
substitute test, because path parallel_safe markings don't account for
initPlans.  (Normally, we'd have decided the paths weren't safe anyway
due to appearances of SubPlan nodes, Params, or CTE scans somewhere in
the tree --- but it's possible for those all to be optimized away while
initPlans still remain.)

Per fuzz testing by Andreas Seltenreich.

Report: <874m89rw7x.fsf@credativ.de>

2 files changed, 81 insertions, 0 deletions

diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 462ad231c36..3ae918a63c5 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1285,3 +1285,68 @@ fetch all from c;
 (3 rows)
 
 rollback;
+-- Check handling of non-backwards-scan-capable plans with scroll cursors
+begin;
+explain (costs off) declare c1 cursor for select (select 42) as x;
+        QUERY PLAN         
+---------------------------
+ Result
+   InitPlan 1 (returns $0)
+     ->  Result
+(3 rows)
+
+explain (costs off) declare c1 scroll cursor for select (select 42) as x;
+        QUERY PLAN         
+---------------------------
+ Materialize
+   InitPlan 1 (returns $0)
+     ->  Result
+   ->  Result
+(4 rows)
+
+declare c1 scroll cursor for select (select 42) as x;
+fetch all in c1;
+ x  
+----
+ 42
+(1 row)
+
+fetch backward all in c1;
+ x  
+----
+ 42
+(1 row)
+
+rollback;
+begin;
+explain (costs off) declare c2 cursor for select generate_series(1,3) as g;
+ QUERY PLAN 
+------------
+ Result
+(1 row)
+
+explain (costs off) declare c2 scroll cursor for select generate_series(1,3) as g;
+  QUERY PLAN  
+--------------
+ Materialize
+   ->  Result
+(2 rows)
+
+declare c2 scroll cursor for select generate_series(1,3) as g;
+fetch all in c2;
+ g 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+fetch backward all in c2;
+ g 
+---
+ 3
+ 2
+ 1
+(3 rows)
+
+rollback;
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index 01c3b85da9a..a1c812e937f 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -484,3 +484,19 @@ fetch all from c;
 move backward all in c;
 fetch all from c;
 rollback;
+
+-- Check handling of non-backwards-scan-capable plans with scroll cursors
+begin;
+explain (costs off) declare c1 cursor for select (select 42) as x;
+explain (costs off) declare c1 scroll cursor for select (select 42) as x;
+declare c1 scroll cursor for select (select 42) as x;
+fetch all in c1;
+fetch backward all in c1;
+rollback;
+begin;
+explain (costs off) declare c2 cursor for select generate_series(1,3) as g;
+explain (costs off) declare c2 scroll cursor for select generate_series(1,3) as g;
+declare c2 scroll cursor for select generate_series(1,3) as g;
+fetch all in c2;
+fetch backward all in c2;
+rollback;