postgres.git / summary / log / commit / refs
commit ce8d5fe0e2802158b65699aeae1551d489948167
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Mar 20 22:23:45 2026 +0000
plpgsql: optimize "SELECT simple-expression INTO var".
Previously, we always fed SELECT ... INTO to the SPI machinery.
While that works for all cases, it's a great deal slower than
the otherwise-equivalent "var := expression" if the expression
is "simple" and the INTO target is a single variable. Users
coming from MSSQL or T_SQL are likely to be surprised by this;
they are used to writing SELECT ... INTO since there is no
"var := expression" syntax in those dialects. Hence, check for
a simple expression and use the faster code path if possible.
(Here, "simple" means whatever exec_is_simple_query accepts,
which basically means "SELECT scalar-expression" without any
input tables, aggregates, qual clauses, etc.)
This optimization is not entirely transparent. Notably, one of
the reasons it's faster is that the hooks that pg_stat_statements
uses aren't called in this path, so that the evaluated expression
no longer appears in pg_stat_statements output as it did before.
There may be some other minor behavioral changes too, although
I tried hard to make error reporting look the same. Hopefully,
none of them are significant enough to not be acceptable as
routine changes in a PG major version.
Author: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://postgr.es/m/CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com
.../pg_stat_statements/expected/level_tracking.out | 8 +-
contrib/pg_stat_statements/expected/plancache.out | 6 +-
src/pl/plpgsql/src/expected/plpgsql_simple.out | 19 ++++
src/pl/plpgsql/src/pl_exec.c | 100 +++++++++++++++++++++
src/pl/plpgsql/src/sql/plpgsql_simple.sql | 17 ++++
5 files changed, 141 insertions(+), 9 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index a15d897e59b..832d65e97ca 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -1500,12 +1500,11 @@ SELECT PLUS_ONE(1);
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+----------------------------------------------------
- 2 | 2 | SELECT (i + $2 + $3)::INTEGER
2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3
2 | 2 | SELECT PLUS_ONE($1)
2 | 2 | SELECT PLUS_TWO($1)
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
-(5 rows)
+(4 rows)
-- immutable SQL function --- can be executed at plan time
CREATE FUNCTION PLUS_THREE(i INTEGER) RETURNS INTEGER AS
@@ -1525,15 +1524,14 @@ SELECT PLUS_THREE(10);
SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
toplevel | calls | rows | query
----------+-------+------+------------------------------------------------------------------------------
- f | 2 | 2 | SELECT (i + $2 + $3)::INTEGER
f | 2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3
t | 2 | 2 | SELECT PLUS_ONE($1)
t | 2 | 2 | SELECT PLUS_THREE($1)
t | 2 | 2 | SELECT PLUS_TWO($1)
- t | 1 | 5 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
+ t | 1 | 4 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
f | 2 | 2 | SELECT i + $2 LIMIT $3
t | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
-(8 rows)
+(7 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
diff --git a/contrib/pg_stat_statements/expected/plancache.out b/contrib/pg_stat_statements/expected/plancache.out
index e152de9f551..32bf913b286 100644
--- a/contrib/pg_stat_statements/expected/plancache.out
+++ b/contrib/pg_stat_statements/expected/plancache.out
@@ -159,11 +159,10 @@ SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_sta
calls | generic_plan_calls | custom_plan_calls | toplevel | query
-------+--------------------+-------------------+----------+----------------------------------------------------
2 | 0 | 0 | t | CALL select_one_proc($1)
- 4 | 2 | 2 | f | SELECT $1
1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
2 | 0 | 0 | t | SELECT select_one_func($1)
2 | 0 | 0 | t | SET plan_cache_mode TO $1
-(5 rows)
+(4 rows)
--
-- EXPLAIN [ANALYZE] EXECUTE + functions/procedures
@@ -211,10 +210,9 @@ SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_sta
2 | 0 | 0 | t | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1)
4 | 0 | 0 | f | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1);
2 | 0 | 0 | t | EXPLAIN (COSTS OFF) SELECT select_one_func($1)
- 4 | 2 | 2 | f | SELECT $1
1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
2 | 0 | 0 | t | SET plan_cache_mode TO $1
-(7 rows)
+(6 rows)
RESET pg_stat_statements.track;
--
diff --git a/src/pl/plpgsql/src/expected/plpgsql_simple.out b/src/pl/plpgsql/src/expected/plpgsql_simple.out
index da351873e74..ccf15ea2200 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_simple.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_simple.out
@@ -129,3 +129,22 @@ begin
raise notice 'val = %', val;
end; $$;
NOTICE: val = 42
+-- We now optimize "SELECT simple-expr INTO var" using the simple-expression
+-- logic. Verify that error reporting works the same as it did before.
+do $$
+declare x bigint := 2^30; y int;
+begin
+ -- overflow during assignment step does not get an extra context line
+ select x*x into y;
+end $$;
+ERROR: integer out of range
+CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
+do $$
+declare x bigint := 2^30; y int;
+begin
+ -- overflow during expression evaluation step does get an extra context line
+ select x*x*x into y;
+end $$;
+ERROR: bigint out of range
+CONTEXT: SQL statement "select x*x*x"
+PL/pgSQL function inline_code_block line 5 at SQL statement
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 84552e32c87..45d667428f4 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -267,6 +267,7 @@ typedef struct count_param_references_context
static void coerce_function_result_tuple(PLpgSQL_execstate *estate,
TupleDesc tupdesc);
static void plpgsql_exec_error_callback(void *arg);
+static void plpgsql_execsql_error_callback(void *arg);
static void copy_plpgsql_datums(PLpgSQL_execstate *estate,
PLpgSQL_function *func);
static void plpgsql_fulfill_promise(PLpgSQL_execstate *estate,
@@ -1301,6 +1302,37 @@ plpgsql_exec_error_callback(void *arg)
estate->func->fn_signature);
}
+/*
+ * error context callback used for "SELECT simple-expr INTO var"
+ *
+ * This should match the behavior of spi.c's _SPI_error_callback(),
+ * so that the construct still reports errors the same as it did
+ * before we optimized it with the simple-expression code path.
+ */
+static void
+plpgsql_execsql_error_callback(void *arg)
+{
+ PLpgSQL_expr *expr = (PLpgSQL_expr *) arg;
+ const char *query = expr->query;
+ int syntaxerrposition;
+
+ /*
+ * If there is a syntax error position, convert to internal syntax error;
+ * otherwise treat the query as an item of context stack
+ */
+ syntaxerrposition = geterrposition();
+ if (syntaxerrposition > 0)
+ {
+ errposition(0);
+ internalerrposition(syntaxerrposition);
+ internalerrquery(query);
+ }
+ else
+ {
+ errcontext("SQL statement \"%s\"", query);
+ }
+}
+
/* ----------
* Support function for initializing local execution variables
@@ -4253,6 +4285,74 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
stmt->mod_stmt_set = true;
}
+ /*
+ * Some users write "SELECT expr INTO var" instead of "var := expr". If
+ * the expression is simple and the INTO target is a single variable, we
+ * can bypass SPI and call ExecEvalExpr() directly. (exec_eval_expr would
+ * actually work for non-simple expressions too, but such an expression
+ * might return more or less than one row, complicating matters greatly.
+ * The potential performance win is small if it's non-simple, and any
+ * errors we might issue would likely look different, so avoid using this
+ * code path for non-simple cases.)
+ */
+ if (expr->expr_simple_expr && stmt->into)
+ {
+ PLpgSQL_datum *target = estate->datums[stmt->target->dno];
+
+ if (target->dtype == PLPGSQL_DTYPE_ROW)
+ {
+ PLpgSQL_row *row = (PLpgSQL_row *) target;
+
+ if (row->nfields == 1)
+ {
+ ErrorContextCallback plerrcontext;
+ Datum value;
+ bool isnull;
+ Oid valtype;
+ int32 valtypmod;
+
+ /*
+ * Setup error traceback support for ereport(). This is so
+ * that error reports for the expression will look similar
+ * whether or not we take this code path.
+ */
+ plerrcontext.callback = plpgsql_execsql_error_callback;
+ plerrcontext.arg = expr;
+ plerrcontext.previous = error_context_stack;
+ error_context_stack = &plerrcontext;
+
+ /* If first time through, create a plan for this expression */
+ if (expr->plan == NULL)
+ exec_prepare_plan(estate, expr, 0);
+
+ /* And evaluate the expression */
+ value = exec_eval_expr(estate, expr,
+ &isnull, &valtype, &valtypmod);
+
+ /*
+ * Pop the error context stack: the code below would not use
+ * SPI's error handling during the assignment step.
+ */
+ error_context_stack = plerrcontext.previous;
+
+ /* Assign the result to the INTO target */
+ exec_assign_value(estate, estate->datums[row->varnos[0]],
+ value, isnull, valtype, valtypmod);
+ exec_eval_cleanup(estate);
+
+ /*
+ * We must duplicate the other effects of the code below, as
+ * well. We know that exactly one row was returned, so it
+ * doesn't matter whether the INTO was STRICT or not.
+ */
+ exec_set_found(estate, true);
+ estate->eval_processed = 1;
+
+ return PLPGSQL_RC_OK;
+ }
+ }
+ }
+
/*
* Set up ParamListInfo to pass to executor
*/
diff --git a/src/pl/plpgsql/src/sql/plpgsql_simple.sql b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
index 72d8afe4500..d64e791800b 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_simple.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
@@ -114,3 +114,20 @@ begin
fetch p_CurData into val;
raise notice 'val = %', val;
end; $$;
+
+-- We now optimize "SELECT simple-expr INTO var" using the simple-expression
+-- logic. Verify that error reporting works the same as it did before.
+
+do $$
+declare x bigint := 2^30; y int;
+begin
+ -- overflow during assignment step does not get an extra context line
+ select x*x into y;
+end $$;
+
+do $$
+declare x bigint := 2^30; y int;
+begin
+ -- overflow during expression evaluation step does get an extra context line
+ select x*x*x into y;
+end $$;
[parent: 4a0b46b6e1e4]