postgres.git / summary / log / commit / refs

commit    a28c1fb619bfbb82e4dc4965502d33f5a77bde52
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Fri Mar 21 15:30:42 2025 +0000
First released in: 18.0, 13.21, 14.18, 15.13, 16.9, 17.5

    Fix plpgsql's handling of simple expressions in scrollable cursors.
    
    exec_save_simple_expr did not account for the possibility that
    standard_planner would stick a Materialize node atop the plan
    of even a simple Result, if CURSOR_OPT_SCROLL is set.  This led
    to an "unexpected plan node type" error.
    
    This is a very old bug, but it'd only be reached by declaring a
    cursor for a "SELECT simple-expression" query and explicitly
    marking it scrollable, which is an odd thing to do.  So the lack
    of prior reports isn't too surprising.
    
    Bug: #18859
    Reported-by: Olleg Samoylov <splarv@ya.ru>
    Author: Andrei Lepikhov <lepihov@gmail.com>
    Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
    Discussion: https://postgr.es/m/18859-0d5f28ac99a37059@postgresql.org
    Backpatch-through: 13


 src/pl/plpgsql/src/expected/plpgsql_simple.out | 11 +++++++++++
 src/pl/plpgsql/src/pl_exec.c                   | 12 +++++++-----
 src/pl/plpgsql/src/sql/plpgsql_simple.sql      | 12 ++++++++++++
 3 files changed, 30 insertions(+), 5 deletions(-)

diff --git a/src/pl/plpgsql/src/expected/plpgsql_simple.out b/src/pl/plpgsql/src/expected/plpgsql_simple.out
index 7b22e60f198..da351873e74 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_simple.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_simple.out
@@ -118,3 +118,14 @@ select simplecaller();
            44
 (1 row)
 
+-- Check handling of simple expression in a scrollable cursor (bug #18859)
+do $$
+declare
+ p_CurData refcursor;
+ val int;
+begin
+ open p_CurData scroll for select 42;
+ fetch p_CurData into val;
+ raise notice 'val = %', val;
+end; $$;
+NOTICE:  val = 42
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 8719c731de9..0394da0a1c0 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8139,10 +8139,12 @@ exec_save_simple_expr(PLpgSQL_expr *expr, CachedPlan *cplan)
 	/*
 	 * Ordinarily, the plan node should be a simple Result.  However, if
 	 * debug_parallel_query is on, the planner might've stuck a Gather node
-	 * atop that.  The simplest way to deal with this is to look through the
-	 * Gather node.  The Gather node's tlist would normally contain a Var
-	 * referencing the child node's output, but it could also be a Param, or
-	 * it could be a Const that setrefs.c copied as-is.
+	 * atop that; and/or if this plan is for a scrollable cursor, the planner
+	 * might've stuck a Material node atop it.  The simplest way to deal with
+	 * this is to look through the Gather and/or Material nodes.  The upper
+	 * node's tlist would normally contain a Var referencing the child node's
+	 * output, but it could also be a Param, or it could be a Const that
+	 * setrefs.c copied as-is.
 	 */
 	plan = stmt->planTree;
 	for (;;)
@@ -8160,7 +8162,7 @@ exec_save_simple_expr(PLpgSQL_expr *expr, CachedPlan *cplan)
 				   ((Result *) plan)->resconstantqual == NULL);
 			break;
 		}
-		else if (IsA(plan, Gather))
+		else if (IsA(plan, Gather) || IsA(plan, Material))
 		{
 			Assert(plan->lefttree != NULL &&
 				   plan->righttree == NULL &&
diff --git a/src/pl/plpgsql/src/sql/plpgsql_simple.sql b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
index 143bf09dce4..72d8afe4500 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_simple.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
@@ -102,3 +102,15 @@ as $$select 22 + 22$$;
 select simplecaller();
 
 select simplecaller();
+
+-- Check handling of simple expression in a scrollable cursor (bug #18859)
+
+do $$
+declare
+ p_CurData refcursor;
+ val int;
+begin
+ open p_CurData scroll for select 42;
+ fetch p_CurData into val;
+ raise notice 'val = %', val;
+end; $$;


[parent: 9070e5840aa3]