Restructure parsetree representation of DECLARE CURSOR: now it's a

utility statement (DeclareCursorStmt) with a SELECT query dangling from
it, rather than a SELECT query with a few unusual fields in it.  Add
code to determine whether a planned query can safely be run backwards.
If DECLARE CURSOR specifies SCROLL, ensure that the plan can be run
backwards by adding a Materialize plan node if it can't.  Without SCROLL,
you get an error if you try to fetch backwards from a cursor that can't
handle it.  (There is still some discussion about what the exact
behavior should be, but this is necessary infrastructure in any case.)
Along the way, make EXPLAIN DECLARE CURSOR work.

8 files changed, 81 insertions, 72 deletions

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 99d979d57c0..0bf43cab24d 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.98 2003/03/05 20:01:03 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.99 2003/03/10 03:53:49 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -343,8 +343,7 @@ set_subquery_pathlist(Query *root, RelOptInfo *rel,
 	}
 
 	/* Generate the plan for the subquery */
-	rel->subplan = subquery_planner(subquery,
-									-1.0 /* default case */ );
+	rel->subplan = subquery_planner(subquery, 0.0 /* default case */ );
 
 	/* Copy number of output rows from subplan */
 	rel->tuples = rel->subplan->plan_rows;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index bfc11aa0046..d01acdc6182 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.137 2003/02/16 06:06:32 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.138 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1827,6 +1827,41 @@ make_material(List *tlist, Plan *lefttree)
 	return node;
 }
 
+/*
+ * materialize_finished_plan: stick a Material node atop a completed plan
+ *
+ * There are a couple of places where we want to attach a Material node
+ * after completion of subquery_planner().  This currently requires hackery.
+ * Since subquery_planner has already run SS_finalize_plan on the subplan
+ * tree, we have to kluge up parameter lists for the Material node.
+ * Possibly this could be fixed by postponing SS_finalize_plan processing
+ * until setrefs.c is run?
+ */
+Plan *
+materialize_finished_plan(Plan *subplan)
+{
+	Plan	   *matplan;
+	Path		matpath;		/* dummy for result of cost_material */
+
+	matplan = (Plan *) make_material(subplan->targetlist, subplan);
+
+	/* Set cost data */
+	cost_material(&matpath,
+				  subplan->total_cost,
+				  subplan->plan_rows,
+				  subplan->plan_width);
+	matplan->startup_cost = matpath.startup_cost;
+	matplan->total_cost = matpath.total_cost;
+	matplan->plan_rows = subplan->plan_rows;
+	matplan->plan_width = subplan->plan_width;
+
+	/* parameter kluge --- see comments above */
+	matplan->extParam = bms_copy(subplan->extParam);
+	matplan->allParam = bms_copy(subplan->allParam);
+
+	return matplan;
+}
+
 Agg *
 make_agg(Query *root, List *tlist, List *qual,
 		 AggStrategy aggstrategy,
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c
index daa840f789e..97f6b76a8e4 100644
--- a/src/backend/optimizer/plan/planmain.c
+++ b/src/backend/optimizer/plan/planmain.c
@@ -14,7 +14,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planmain.c,v 1.74 2003/01/20 18:54:52 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planmain.c,v 1.75 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -61,14 +61,11 @@
  * indxpath.c need to see it.)
  *
  * tuple_fraction is interpreted as follows:
- *	  0 (or less): expect all tuples to be retrieved (normal case)
+ *	  0: expect all tuples to be retrieved (normal case)
  *	  0 < tuple_fraction < 1: expect the given fraction of tuples available
  *		from the plan to be retrieved
  *	  tuple_fraction >= 1: tuple_fraction is the absolute number of tuples
  *		expected to be retrieved (ie, a LIMIT specification)
- * Note that while this routine and its subroutines treat a negative
- * tuple_fraction the same as 0, grouping_planner has a different
- * interpretation.
  *--------------------
  */
 void
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index c7c072fe2eb..9fa78b8a237 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.150 2003/03/05 20:01:03 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.151 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -19,6 +19,7 @@
 
 #include "catalog/pg_operator.h"
 #include "catalog/pg_type.h"
+#include "executor/executor.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #ifdef OPTIMIZER_DEBUG
@@ -73,8 +74,9 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
  *
  *****************************************************************************/
 Plan *
-planner(Query *parse)
+planner(Query *parse, bool isCursor, int cursorOptions)
 {
+	double		tuple_fraction;
 	Plan	   *result_plan;
 	Index		save_PlannerQueryLevel;
 	List	   *save_PlannerParamVar;
@@ -99,11 +101,38 @@ planner(Query *parse)
 	PlannerQueryLevel = 0;		/* will be 1 in top-level subquery_planner */
 	PlannerParamVar = NIL;
 
+	/* Determine what fraction of the plan is likely to be scanned */
+	if (isCursor)
+	{
+		/*
+		 * We have no real idea how many tuples the user will ultimately
+		 * FETCH from a cursor, but it seems a good bet that he
+		 * doesn't want 'em all.  Optimize for 10% retrieval (you
+		 * gotta better number?  Should this be a SETtable parameter?)
+		 */
+		tuple_fraction = 0.10;
+	}
+	else
+	{
+		/* Default assumption is we need all the tuples */
+		tuple_fraction = 0.0;
+	}
+
 	/* primary planning entry point (may recurse for subqueries) */
-	result_plan = subquery_planner(parse, -1.0 /* default case */ );
+	result_plan = subquery_planner(parse, tuple_fraction);
 
 	Assert(PlannerQueryLevel == 0);
 
+	/*
+	 * If creating a plan for a scrollable cursor, make sure it can
+	 * run backwards on demand.  Add a Material node at the top at need.
+	 */
+	if (isCursor && (cursorOptions & CURSOR_OPT_SCROLL))
+	{
+		if (!ExecSupportsBackwardScan(result_plan))
+			result_plan = materialize_finished_plan(result_plan);
+	}
+
 	/* executor wants to know total number of Params used overall */
 	result_plan->nParamExec = length(PlannerParamVar);
 
@@ -505,14 +534,11 @@ inheritance_planner(Query *parse, List *inheritlist)
  * tuple_fraction is the fraction of tuples we expect will be retrieved
  *
  * tuple_fraction is interpreted as follows:
- *	  < 0: determine fraction by inspection of query (normal case)
- *	  0: expect all tuples to be retrieved
+ *	  0: expect all tuples to be retrieved (normal case)
  *	  0 < tuple_fraction < 1: expect the given fraction of tuples available
  *		from the plan to be retrieved
  *	  tuple_fraction >= 1: tuple_fraction is the absolute number of tuples
  *		expected to be retrieved (ie, a LIMIT specification)
- * The normal case is to pass -1, but some callers pass values >= 0 to
- * override this routine's determination of the appropriate fraction.
  *
  * Returns a query plan.  Also, parse->query_pathkeys is returned as the
  * actual output ordering of the plan (in pathkey format).
@@ -694,29 +720,6 @@ grouping_planner(Query *parse, double tuple_fraction)
 			parse->query_pathkeys = NIL;
 
 		/*
-		 * Figure out whether we expect to retrieve all the tuples that
-		 * the plan can generate, or to stop early due to outside factors
-		 * such as a cursor.  If the caller passed a value >= 0, believe
-		 * that value, else do our own examination of the query context.
-		 */
-		if (tuple_fraction < 0.0)
-		{
-			/* Initial assumption is we need all the tuples */
-			tuple_fraction = 0.0;
-
-			/*
-			 * Check for retrieve-into-portal, ie DECLARE CURSOR.
-			 *
-			 * We have no real idea how many tuples the user will ultimately
-			 * FETCH from a cursor, but it seems a good bet that he
-			 * doesn't want 'em all.  Optimize for 10% retrieval (you
-			 * gotta better number?  Should this be a SETtable parameter?)
-			 */
-			if (parse->isPortal)
-				tuple_fraction = 0.10;
-		}
-
-		/*
 		 * Adjust tuple_fraction if we see that we are going to apply
 		 * limiting/grouping/aggregation/etc.  This is not overridable by
 		 * the caller, since it reflects plan actions that this routine
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index fc428977c33..417eecc1fe3 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/subselect.c,v 1.72 2003/02/09 06:56:27 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/plan/subselect.c,v 1.73 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -222,7 +222,7 @@ make_subplan(SubLink *slink, List *lefthand, bool isTopQual)
 			 slink->subLinkType == ANY_SUBLINK)
 		tuple_fraction = 0.5;	/* 50% */
 	else
-		tuple_fraction = -1.0;	/* default behavior */
+		tuple_fraction = 0.0;	/* default behavior */
 
 	/*
 	 * Generate the plan for the subquery.
@@ -336,12 +336,6 @@ make_subplan(SubLink *slink, List *lefthand, bool isTopQual)
 		 * is anything more complicated than a plain sequential scan, and we
 		 * do it even for seqscan if the qual appears selective enough to
 		 * eliminate many tuples.
-		 *
-		 * XXX It's pretty ugly to be inserting a MATERIAL node at this
-		 * point.  Since subquery_planner has already run SS_finalize_plan
-		 * on the subplan tree, we have to kluge up parameter lists for
-		 * the MATERIAL node.  Possibly this could be fixed by postponing
-		 * SS_finalize_plan processing until setrefs.c is run.
 		 */
 		else if (node->parParam == NIL)
 		{
@@ -380,23 +374,7 @@ make_subplan(SubLink *slink, List *lefthand, bool isTopQual)
 			}
 			if (use_material)
 			{
-				Plan	   *matplan;
-				Path		matpath; /* dummy for result of cost_material */
-
-				matplan = (Plan *) make_material(plan->targetlist, plan);
-				/* need to calculate costs */
-				cost_material(&matpath,
-							  plan->total_cost,
-							  plan->plan_rows,
-							  plan->plan_width);
-				matplan->startup_cost = matpath.startup_cost;
-				matplan->total_cost = matpath.total_cost;
-				matplan->plan_rows = plan->plan_rows;
-				matplan->plan_width = plan->plan_width;
-				/* parameter kluge --- see comments above */
-				matplan->extParam = bms_copy(plan->extParam);
-				matplan->allParam = bms_copy(plan->allParam);
-				node->plan = plan = matplan;
+				node->plan = plan = materialize_finished_plan(plan);
 			}
 		}
 
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 207a813e8e0..a265623bfb7 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -16,7 +16,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.6 2003/02/10 17:08:50 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.7 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -362,8 +362,7 @@ is_simple_subquery(Query *subquery)
 	if (!IsA(subquery, Query) ||
 		subquery->commandType != CMD_SELECT ||
 		subquery->resultRelation != 0 ||
-		subquery->into != NULL ||
-		subquery->isPortal)
+		subquery->into != NULL)
 		elog(ERROR, "is_simple_subquery: subquery is bogus");
 
 	/*
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index f0a64f2980c..1782bcc88b9 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -14,7 +14,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v 1.91 2003/03/05 20:01:03 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v 1.92 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -134,8 +134,7 @@ recurse_set_operations(Node *setOp, Query *parse,
 		/*
 		 * Generate plan for primitive subquery
 		 */
-		subplan = subquery_planner(subquery,
-								   -1.0 /* default case */ );
+		subplan = subquery_planner(subquery, 0.0 /* default case */ );
 
 		/*
 		 * Add a SubqueryScan with the caller-requested targetlist
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 40e440a3754..fcd58374189 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v 1.130 2003/02/16 02:30:38 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/clauses.c,v 1.131 2003/03/10 03:53:50 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -1787,7 +1787,6 @@ inline_function(Oid funcid, List *args, HeapTuple func_tuple,
 		querytree->commandType != CMD_SELECT ||
 		querytree->resultRelation != 0 ||
 		querytree->into ||
-		querytree->isPortal ||
 		querytree->hasAggs ||
 		querytree->hasSubLinks ||
 		querytree->rtable ||