Tom Lane писал(а) 2023-02-07 18:29:
Ronan Dunklau <ronan.dunk...@aiven.io> writes:
The following comment can be found in functions.c, about the
SQLFunctionCache:
* Note that currently this has only the lifespan of the calling
query.
* Someday we should rewrite this code to use plancache.c to save
parse/plan
* results for longer than that.
I would be interested in working on this, primarily to avoid this
problem of
having generic query plans for SQL functions but maybe having a longer
lived
cache as well would be nice to have.
Is there any reason not too, or pitfalls we would like to avoid ?
AFAIR it's just lack of round tuits. There would probably be some
semantic side-effects, though if you pay attention you could likely
make things better while you are at it. The existing behavior of
parsing and planning all the statements at once is not very desirable
--- for instance, it doesn't work to do
CREATE TABLE foo AS ...;
SELECT * FROM foo;
I think if we're going to nuke this code and start over, we should
try to make that sort of case work.
regards, tom lane
Hi.
I've tried to make SQL functions use CachedPlan machinery. The main goal
was to allow SQL functions to use custom plans
(the work was started from question - why sql function is so slow
compared to plpgsql one). It turned out that
plpgsql function used custom plan and eliminated scan of all irrelevant
sections, but
exec-time pruning didn't cope with pruning when ScalarArrayOpExpr,
filtering data using int[] parameter.
In current prototype there are two restrictions. The first one is that
CachecPlan has lifetime of a query - it's not
saved for future use, as we don't have something like plpgsql hashtable
for long live function storage. Second -
SQL language functions in sql_body form (with stored queryTree_list) are
handled in the old way, as we currently lack
tools to make cached plans from query trees.
Currently this change solves the issue of inefficient plans for queries
over partitioned tables. For example, function like
CREATE OR REPLACE FUNCTION public.test_get_records(ids integer[])
RETURNS SETOF test
LANGUAGE sql
AS $function$
select *
from test
where id = any (ids)
$function$;
for hash-distributed table test can perform pruning in plan time and
can have plan like
Append (cost=0.00..51.88 rows=26 width=36)
-> Seq Scan on test_0 test_1 (cost=0.00..25.88 rows=13
width=36)
Filter: (id = ANY ('{1,2}'::integer[]))
-> Seq Scan on test_2 (cost=0.00..25.88 rows=13 width=36)
Filter: (id = ANY ('{1,2}'::integer[]))
instead of
Append (cost=0.00..155.54 rows=248 width=36)
-> Seq Scan on test_0 test_1 (cost=0.00..38.58 rows=62
width=36)
Filter: (id = ANY ($1))
-> Seq Scan on test_1 test_2 (cost=0.00..38.58 rows=62
width=36)
Filter: (id = ANY ($1))
-> Seq Scan on test_2 test_3 (cost=0.00..38.58 rows=62
width=36)
Filter: (id = ANY ($1))
-> Seq Scan on test_3 test_4 (cost=0.00..38.58 rows=62
width=36)
Filter: (id = ANY ($1))
This patch definitely requires more work, and I share it to get some
early feedback.
What should we do with "pre-parsed" SQL functions (when prosrc is
empty)? How should we create cached plans when we don't have raw
parsetrees?
Currently we can create cached plans without raw parsetrees, but this
means that plan revalidation doesn't work, choose_custom_plan()
always returns false and we get generic plan. Perhaps, we need some form
of GetCachedPlan(), which ignores raw_parse_tree?
In this case how could we possibly cache plans for session lifetime
(like plpgsql language does) if we can't use cached revalidation
machinery?
I hope to get some hints to move further.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From d7cdde449daeefbfd9ccd3e72ae282f21be0c1c8 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Fri, 23 Aug 2024 15:48:38 +0300
Subject: [PATCH] Use custom plan machinery for SQL functions
---
contrib/citext/expected/citext.out | 2 +-
contrib/citext/expected/citext_1.out | 2 +-
src/backend/executor/functions.c | 308 ++++++++++++++----
src/backend/utils/misc/guc_tables.c | 12 +
src/include/executor/functions.h | 2 +
src/test/regress/expected/plpgsql.out | 2 +-
src/test/regress/expected/select_parallel.out | 4 +-
src/test/regress/expected/sysviews.out | 3 +-
8 files changed, 265 insertions(+), 70 deletions(-)
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
index 8c0bf54f0f3..f8765053651 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -1816,7 +1816,7 @@ SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citex
SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error";
ERROR: regexp_match() does not support the "global" option
HINT: Use the regexp_matches function instead.
-CONTEXT: SQL function "regexp_match" statement 1
+CONTEXT: SQL function "regexp_match" during startup
SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
t
---
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out
index c5e5f180f2b..356dc6bba43 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -1816,7 +1816,7 @@ SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citex
SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error";
ERROR: regexp_match() does not support the "global" option
HINT: Use the regexp_matches function instead.
-CONTEXT: SQL function "regexp_match" statement 1
+CONTEXT: SQL function "regexp_match" during startup
SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
t
---
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 692854e2b3e..a67cc888ea6 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -33,9 +33,14 @@
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/plancache.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+/*
+ * GUC variables
+ */
+bool enable_sql_func_custom_plans = true;
/*
* Specialized DestReceiver for collecting query output in a SQL function
@@ -112,6 +117,11 @@ typedef struct
JunkFilter *junkFilter; /* will be NULL if function returns VOID */
+ /* Cached plans support */
+ List *queryTree_list; /* list of query lists */
+ List *plansource_list; /* list of plansource */
+ List *cplan_list; /* list of cached plans */
+
/*
* func_state is a List of execution_state records, each of which is the
* first for its original parsetree, with any additional records chained
@@ -454,6 +464,40 @@ sql_fn_resolve_param_name(SQLFunctionParseInfoPtr pinfo,
return NULL;
}
+/* Precheck command for validity in a function */
+static void
+check_planned_stmt(PlannedStmt *stmt, SQLFunctionCachePtr fcache)
+{
+
+ /*
+ * Precheck all commands for validity in a function. This should
+ * generally match the restrictions spi.c applies.
+ */
+ if (stmt->commandType == CMD_UTILITY)
+ {
+ if (IsA(stmt->utilityStmt, CopyStmt) &&
+ ((CopyStmt *) stmt->utilityStmt)->filename == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot COPY to/from client in an SQL function")));
+
+ if (IsA(stmt->utilityStmt, TransactionStmt))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /* translator: %s is a SQL statement name */
+ errmsg("%s is not allowed in an SQL function",
+ CreateCommandName(stmt->utilityStmt))));
+ }
+
+ if (fcache->readonly_func && !CommandIsReadOnly(stmt))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ /* translator: %s is a SQL statement name */
+ errmsg("%s is not allowed in a non-volatile function",
+ CreateCommandName((Node *) stmt))));
+
+}
+
/*
* Set up the per-query execution_state records for a SQL function.
*
@@ -466,85 +510,117 @@ init_execution_state(List *queryTree_list,
bool lazyEvalOK)
{
List *eslist = NIL;
+ List *cplan_list = NIL;
execution_state *lasttages = NULL;
ListCell *lc1;
foreach(lc1, queryTree_list)
{
- List *qtlist = lfirst_node(List, lc1);
+ List *qtlist;
execution_state *firstes = NULL;
execution_state *preves = NULL;
ListCell *lc2;
- foreach(lc2, qtlist)
+ if (fcache->plansource_list)
{
- Query *queryTree = lfirst_node(Query, lc2);
- PlannedStmt *stmt;
- execution_state *newes;
+ CachedPlan *cplan;
+ CachedPlanSource *plansource;
+ int cur_idx;
- /* Plan the query if needed */
- if (queryTree->commandType == CMD_UTILITY)
- {
- /* Utility commands require no planning. */
- stmt = makeNode(PlannedStmt);
- stmt->commandType = CMD_UTILITY;
- stmt->canSetTag = queryTree->canSetTag;
- stmt->utilityStmt = queryTree->utilityStmt;
- stmt->stmt_location = queryTree->stmt_location;
- stmt->stmt_len = queryTree->stmt_len;
- stmt->queryId = queryTree->queryId;
- }
- else
- stmt = pg_plan_query(queryTree,
- fcache->src,
- CURSOR_OPT_PARALLEL_OK,
- NULL);
+ /* Find plan source, corresponding to this query list */
+ cur_idx = foreach_current_index(lc1);
+ plansource = list_nth(fcache->plansource_list, cur_idx);
/*
- * Precheck all commands for validity in a function. This should
- * generally match the restrictions spi.c applies.
+ * Get plan for the query. If paramLI is set, we can get custom
+ * plan
*/
- if (stmt->commandType == CMD_UTILITY)
+ cplan = GetCachedPlan(plansource, fcache->paramLI, NULL, NULL);
+
+ /* Record cplan in plan list to be released on replanning */
+ cplan_list = lappend(cplan_list, cplan);
+
+ /* For each planned statement create execution state */
+ foreach(lc2, cplan->stmt_list)
{
- if (IsA(stmt->utilityStmt, CopyStmt) &&
- ((CopyStmt *) stmt->utilityStmt)->filename == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot COPY to/from client in an SQL function")));
+ PlannedStmt *stmt = lfirst(lc2);
+ execution_state *newes;
- if (IsA(stmt->utilityStmt, TransactionStmt))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is a SQL statement name */
- errmsg("%s is not allowed in an SQL function",
- CreateCommandName(stmt->utilityStmt))));
+ /* Check that stmt is valid for SQL function */
+ check_planned_stmt(stmt, fcache);
+
+ newes = (execution_state *) palloc(sizeof(execution_state));
+
+ if (preves)
+ preves->next = newes;
+ else
+ firstes = newes;
+
+ newes->next = NULL;
+ newes->status = F_EXEC_START;
+ newes->setsResult = false; /* might change below */
+ newes->lazyEval = false; /* might change below */
+ newes->stmt = stmt;
+ newes->qd = NULL;
+
+ if (stmt->canSetTag)
+ lasttages = newes;
+
+ preves = newes;
}
+ }
+ else
+ {
+ qtlist = lfirst_node(List, lc1);
- if (fcache->readonly_func && !CommandIsReadOnly(stmt))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- /* translator: %s is a SQL statement name */
- errmsg("%s is not allowed in a non-volatile function",
- CreateCommandName((Node *) stmt))));
-
- /* OK, build the execution_state for this query */
- newes = (execution_state *) palloc(sizeof(execution_state));
- if (preves)
- preves->next = newes;
- else
- firstes = newes;
+ foreach(lc2, qtlist)
+ {
+ Query *queryTree = lfirst_node(Query, lc2);
+ PlannedStmt *stmt;
+ execution_state *newes;
+
+ /* Plan the query if needed */
+ if (queryTree->commandType == CMD_UTILITY)
+ {
+ /* Utility commands require no planning. */
+ stmt = makeNode(PlannedStmt);
+ stmt->commandType = CMD_UTILITY;
+ stmt->canSetTag = queryTree->canSetTag;
+ stmt->utilityStmt = queryTree->utilityStmt;
+ stmt->stmt_location = queryTree->stmt_location;
+ stmt->stmt_len = queryTree->stmt_len;
+ stmt->queryId = queryTree->queryId;
+ }
+ else
+ {
+ /* Get generic plan for the query */
+ stmt = pg_plan_query(queryTree,
+ fcache->src,
+ CURSOR_OPT_PARALLEL_OK,
+ NULL);
+ }
+
+ /* Check that stmt is valid for SQL function */
+ check_planned_stmt(stmt, fcache);
- newes->next = NULL;
- newes->status = F_EXEC_START;
- newes->setsResult = false; /* might change below */
- newes->lazyEval = false; /* might change below */
- newes->stmt = stmt;
- newes->qd = NULL;
+ newes = (execution_state *) palloc(sizeof(execution_state));
+ if (preves)
+ preves->next = newes;
+ else
+ firstes = newes;
+
+ newes->next = NULL;
+ newes->status = F_EXEC_START;
+ newes->setsResult = false; /* might change below */
+ newes->lazyEval = false; /* might change below */
+ newes->stmt = stmt;
+ newes->qd = NULL;
- if (queryTree->canSetTag)
- lasttages = newes;
+ if (queryTree->canSetTag)
+ lasttages = newes;
- preves = newes;
+ preves = newes;
+ }
}
eslist = lappend(eslist, firstes);
@@ -573,6 +649,7 @@ init_execution_state(List *queryTree_list,
fcache->lazyEval = lasttages->lazyEval = true;
}
+ fcache->cplan_list = cplan_list;
return eslist;
}
@@ -596,6 +673,7 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
ListCell *lc;
Datum tmp;
bool isNull;
+ List *plansource_list;
/*
* Create memory context that holds all the SQLFunctionCache data. It
@@ -680,6 +758,7 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
* plancache.c.
*/
queryTree_list = NIL;
+ plansource_list = NIL;
if (!isNull)
{
Node *n;
@@ -711,6 +790,14 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
{
RawStmt *parsetree = lfirst_node(RawStmt, lc);
List *queryTree_sublist;
+ CachedPlanSource *plansource;
+
+ /* Alas, we can create custom plans only for RawStmt */
+ if (enable_sql_func_custom_plans)
+ {
+ plansource = CreateCachedPlan(parsetree, fcache->src, CreateCommandTag(parsetree->stmt));
+ plansource_list = lappend(plansource_list, plansource);
+ }
queryTree_sublist = pg_analyze_and_rewrite_withcb(parsetree,
fcache->src,
@@ -751,6 +838,34 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
false,
&resulttlist);
+ /*
+ * Queries could be rewritten by check_sql_fn_retval(). Now when they have
+ * their final form, we can complete plan cache entry creation.
+ */
+ if (plansource_list != NIL)
+ {
+ ListCell *qlc;
+ ListCell *plc;
+
+ forboth(qlc, queryTree_list, plc, plansource_list)
+ {
+ List *queryTree_sublist = lfirst(qlc);
+ CachedPlanSource *plansource = lfirst(plc);
+
+
+ /* Finish filling in the CachedPlanSource */
+ CompleteCachedPlan(plansource,
+ queryTree_sublist,
+ NULL,
+ NULL,
+ 0,
+ (ParserSetupHook) sql_fn_parser_setup,
+ fcache->pinfo,
+ CURSOR_OPT_PARALLEL_OK | CURSOR_OPT_NO_SCROLL,
+ false);
+ }
+ }
+
/*
* Construct a JunkFilter we can use to coerce the returned rowtype to the
* desired form, unless the result type is VOID, in which case there's
@@ -795,10 +910,18 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
lazyEvalOK = true;
}
- /* Finally, plan the queries */
- fcache->func_state = init_execution_state(queryTree_list,
- fcache,
- lazyEvalOK);
+ fcache->queryTree_list = queryTree_list;
+ fcache->plansource_list = plansource_list;
+
+ /*
+ * Finally, plan the queries. Skip planning if we use cached plans
+ * machinery - anyway we'll have to replan on the first run when
+ * parameters are substituted.
+ */
+ if (plansource_list == NIL)
+ fcache->func_state = init_execution_state(queryTree_list,
+ fcache,
+ lazyEvalOK);
/* Mark fcache with time of creation to show it's valid */
fcache->lxid = MyProc->vxid.lxid;
@@ -969,7 +1092,13 @@ postquel_sub_params(SQLFunctionCachePtr fcache,
prm->value = MakeExpandedObjectReadOnly(fcinfo->args[i].value,
prm->isnull,
get_typlen(argtypes[i]));
- prm->pflags = 0;
+
+ /*
+ * PARAM_FLAG_CONST is necessary to build efficient custom plan.
+ */
+ if (fcache->plansource_list)
+ prm->pflags = PARAM_FLAG_CONST;
+
prm->ptype = argtypes[i];
}
}
@@ -1022,6 +1151,33 @@ postquel_get_single_result(TupleTableSlot *slot,
return value;
}
+/*
+ * Release plans. This function is called prior to planning
+ * statements with new parameters. When custom plans are generated
+ * for each function call in a statement, they can consume too much memory, so
+ * release them. Generic plans will survive it as plansource holds
+ * reference to a generic plan.
+ */
+static void
+release_plans(List *cplans)
+{
+ ListCell *lc;
+
+ /*
+ * We support separate plan list, so that we visit each plan here only
+ * once
+ */
+ foreach(lc, cplans)
+ {
+ CachedPlan *cplan = lfirst(lc);
+
+ ReleaseCachedPlan(cplan, NULL);
+ }
+
+ /* Cleanup the list itself */
+ list_free(cplans);
+}
+
/*
* fmgr_sql: function call manager for SQL functions
*/
@@ -1040,6 +1196,7 @@ fmgr_sql(PG_FUNCTION_ARGS)
Datum result;
List *eslist;
ListCell *eslc;
+ bool build_cached_plans = false;
/*
* Setup error traceback support for ereport()
@@ -1129,12 +1286,35 @@ fmgr_sql(PG_FUNCTION_ARGS)
break;
}
+ /*
+ * We could skip actual planning if decided to use cached plans. In this
+ * case we have to build cached plans now.
+ */
+ if (fcache->plansource_list != NIL && eslist == NIL)
+ build_cached_plans = true;
+
/*
* Convert params to appropriate format if starting a fresh execution. (If
* continuing execution, we can re-use prior params.)
*/
- if (is_first && es && es->status == F_EXEC_START)
+ if ((is_first && es && es->status == F_EXEC_START) || build_cached_plans)
+ {
postquel_sub_params(fcache, fcinfo);
+ if (fcache->plansource_list)
+ {
+ /* replan the queries */
+ release_plans(fcache->cplan_list);
+ list_free_deep(fcache->func_state);
+
+ fcache->func_state = init_execution_state(fcache->queryTree_list,
+ fcache,
+ lazyEvalOK);
+ /* restore execution state and eslist-related variables */
+ eslist = fcache->func_state;
+ eslc = list_head(eslist);
+ es = linitial(fcache->func_state);
+ }
+ }
/*
* Build tuplestore to hold results, if we don't have one already. Note
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 521ec5591c8..8f66d5ff9fe 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -46,6 +46,7 @@
#include "commands/vacuum.h"
#include "common/file_utils.h"
#include "common/scram-common.h"
+#include "executor/functions.h"
#include "jit/jit.h"
#include "libpq/auth.h"
#include "libpq/libpq.h"
@@ -976,6 +977,17 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_sql_func_custom_plans", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enable plan cache machinery and custom plans for "
+ "SQL language functions."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+ },
+ &enable_sql_func_custom_plans,
+ true,
+ NULL, NULL, NULL
+ },
{
{"enable_async_append", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of async append plans."),
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index 27f948c6f52..8f3805d7782 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -53,4 +53,6 @@ extern bool check_sql_fn_retval(List *queryTreeLists,
extern DestReceiver *CreateSQLFunctionDestReceiver(void);
+extern PGDLLIMPORT bool enable_sql_func_custom_plans;
+
#endif /* FUNCTIONS_H */
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 074af8f33a8..c1a6297043e 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -4474,7 +4474,7 @@ create table public.stuffs (stuff text);
SAVEPOINT a;
select error2('nonexistent.stuffs');
ERROR: schema "nonexistent" does not exist
-CONTEXT: SQL function "error1" statement 1
+CONTEXT: SQL function "error1" during startup
PL/pgSQL function error2(text) line 3 at RETURN
ROLLBACK TO a;
select error2('public.stuffs');
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index c565407082c..4a1c2e53bba 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1370,7 +1370,7 @@ select set_and_report_role();
select set_role_and_error(0);
ERROR: division by zero
-CONTEXT: SQL function "set_role_and_error" statement 1
+CONTEXT: SQL function "set_role_and_error" during startup
set debug_parallel_query = 1;
select set_and_report_role();
set_and_report_role
@@ -1380,7 +1380,7 @@ select set_and_report_role();
select set_role_and_error(0);
ERROR: division by zero
-CONTEXT: SQL function "set_role_and_error" statement 1
+CONTEXT: SQL function "set_role_and_error" during startup
parallel worker
reset debug_parallel_query;
drop function set_and_report_role();
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index fad7fc3a7e0..48df088d568 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -169,8 +169,9 @@ select name, setting from pg_settings where name like 'enable%';
enable_presorted_aggregate | on
enable_seqscan | on
enable_sort | on
+ enable_sql_func_custom_plans | on
enable_tidscan | on
-(22 rows)
+(23 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
--
2.34.1
