Hi, folks.
I've looked through performance and found that most performance issues
was caused by CachedPlanSource machinery itself. At least, a lot of it.
And so decided to go along and implement plan cache for sql functions.
I'm not sure that it's clean enough, but at least it seems to be
working. While working at it I've found issues in
RevalidateCachedQuery() and fixed them. What have changed:
- now plans are released after execution;
- revalidation now takes locks on analyzed_tree;
- query tree is copied prior to analyzing in RevalidateCachedQuery();
- queryTree_list in SQLFunctionCache is no necessary and so has gone.
Now sql functions plans are actually saved. The most of it is a
simplified version of plpgsql plan cache. Perhaps, I've missed
something.
We have some cases when we don't save CachedPlanSource for future use.
One case is for trigger functions (pl_comp.c has some logic to handle
them specially, and I didn't want to introduce it so far). Another (more
interesting) issue is invalidation. SQL functions have a feature of
rewriting query when targetlist doesn't match function call context. I
haven't thought this through carefully during last patch version, but
luckily got some tests, which showed this behavior. When compiled with
RANDOMIZE_ALLOCATED_MEMORY, the following test case dumped core (because
after invalidation executor got "name" fields, but expected text):
create table t (nspname text, tname text);
CREATE OR REPLACE FUNCTION get_basic_attributes_from_pg_tables(
_schemaname name, _tablename name)
RETURNS TABLE(tname text, tablespace text, owner text)
LANGUAGE sql
AS $function$
SELECT
schemaname || '.' || tablename AS "full name",
tablespace AS "tablespace",
tableowner AS "tableowner"
FROM pg_tables
WHERE pg_tables.schemaname = _schemaname AND pg_tables.tablename =
_tablename
ORDER BY 1;
$function$;
create or replace function trg_func() RETURNS TRIGGER
AS
$$
declare
t record;
begin
FOR t IN (SELECT * FROM
get_basic_attributes_from_pg_tables(new.nspname, new.tname)) LOOP
RAISE WARNING '"% % %"', t.owner, t.tablespace, t.tname;
END LOOP;
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
create trigger t_ins_t AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION
trg_func();
set debug_discard_caches to 1;
insert into t values('pg_catalog', 'pg_class');
It's harder to achieve this without permanent cache (harder to trigger
revalidation), but it's still possible.
What happened here is that during revalidation query plan was rebuilt,
but modifications to query tree, made by check_sql_fn_retval() , were
lost.
To fix this issue:
1) We avoid caching modified plans (and check_sql_fn_retval() now
reports if it touched a query tree);
2) For non-cached plans we still need a hack (callback) into
CachedPlanSource to rebuild query tree if invalidation happens. This
callback rebuilds query tree, using check_sql_fn_retval(). We sure that
callback parameters, specifying actual function return type, should not
be recalculated, as such plans can appear only during one function
execution and are not reused.
3) To prove that result type was not changed between plans execution, we
build plans with fixed_result = true.
4) When we get saved plan, prior to using it, we check that result tlist
matches the one built while planning function execution. Otherwise, we
recreate CachedPlanSource.
Well, it appeared more complicated than I've expected, but now it seems
simple SQL functions have much better performance.
create or replace function fx(int) returns int as $$ select $1 + $1; $$
language sql immutable;
create or replace function fx2(int) returns int as $$ select 2 * $1; $$
language sql immutable;
create or replace function fx3 (int) returns int immutable begin atomic
select $1 + $1; end;
create or replace function fx4(int) returns numeric as $$ select $1 +
$1; $$ language sql immutable;
-- sql function
do $$
begin
for i in 1..1000000 loop
perform fx((random()*100)::int);
end loop;
end;
$$;
Time: 3008.869 ms (00:03.009)
-- dynamic SQL
do
$$ begin
for i in 1..1000000 loop
execute 'select $1 + $1' using (random()*100)::int;
end loop;
end;
$$;
Time: 4915.295 ms (00:04.915)do $$
-- pre-parsed function
begin
for i in 1..1000000 loop
perform fx3((random()*100)::int);
end loop;
end;
$$;
Time: 2992.166 ms (00:02.992)
-- no plan caching due to need in fixing target list:
do $$
begin
for i in 1..1000000 loop
perform fx4((random()*100)::int);
end loop;
end;
$$;
Time: 11020.820 ms (00:11.021)
--
Best regards,
Alexander Pyhalov,
Postgres ProfessionalFrom d73e0a9463332d7f25dc42b5bca7e1878d0ee7fe Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Fri, 14 Feb 2025 10:08:52 +0300
Subject: [PATCH 4/4] Handle SQL functions which are modified between rewrite
and plan stages.
Query can be modified between rewrite and plan stages by
check_sql_fn_retval(). If later revalidation is considered by
RevalidateCachedQuery(), modifications, done by check_sql_fn_retval(),
could be lost.
To fix this issue
1) don't cache plans, which were modified by check_sql_fn_retval();
2) introduce callback in RevalidateCachedQuery(), which calls
check_sql_fn_retval() for non-saved plans, which were invalidated;
3) build plans with fixed_result = true, so that if target list
was mistakenly changed on revalidation, we throw an error instead
of crashing.
---
src/backend/catalog/pg_proc.c | 2 +-
src/backend/executor/functions.c | 134 +++++++++++++++++++++++++--
src/backend/optimizer/util/clauses.c | 4 +-
src/backend/utils/cache/plancache.c | 24 +++++
src/include/executor/functions.h | 3 +-
src/include/utils/plancache.h | 9 ++
6 files changed, 165 insertions(+), 11 deletions(-)
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index fe0490259e9..196a01ca803 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -960,7 +960,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
(void) check_sql_fn_retval(querytree_list,
rettype, rettupdesc,
proc->prokind,
- false, NULL);
+ false, NULL, NULL);
}
error_context_stack = sqlerrcontext.previous;
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 7fe49dbe17c..f2e3e601a65 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -174,9 +174,18 @@ typedef struct SQLFunctionPlanEntry
*/
SQLFunctionParseInfoPtr pinfo; /* cached information about arguments */
- MemoryContext entry_ctx; /* memory context for allocated fields of this entry */
+ MemoryContext entry_ctx; /* memory context for allocated fields of this
+ * entry */
} SQLFunctionPlanEntry;
+/* Data necessary to plansource after-rewrite callback to modify query targetlist */
+typedef struct SQLFunctionPlanSourceCallbackData
+{
+ Oid rettype; /* function return type */
+ TupleDesc rettupdesc; /* function return record type */
+ char prokind; /* function kind */
+} SQLFunctionPlanSourceCallbackData;
+
static HTAB *sql_plan_cache_htab = NULL;
/* non-export function prototypes */
@@ -212,14 +221,18 @@ static void sqlfunction_shutdown(DestReceiver *self);
static void sqlfunction_destroy(DestReceiver *self);
/* SQL-functions plan cache-related routines */
-static void compute_plan_entry_key(SQLFunctionPlanKey *hashkey, FunctionCallInfo fcinfo, Form_pg_proc procedureStruct);
-static SQLFunctionPlanEntry *get_cached_plan_entry(SQLFunctionPlanKey *hashkey);
+static void compute_plan_entry_key(SQLFunctionPlanKey * hashkey, FunctionCallInfo fcinfo, Form_pg_proc procedureStruct);
+static SQLFunctionPlanEntry * get_cached_plan_entry(SQLFunctionPlanKey * hashkey);
static void save_cached_plan_entry(SQLFunctionPlanKey * hashkey, HeapTuple procedureTuple, List *plansource_list, List *result_tlist, bool returnsTuple, SQLFunctionParseInfoPtr pinfo, MemoryContext alianable_context);
static void delete_cached_plan_entry(SQLFunctionPlanEntry * entry);
static bool check_sql_fn_retval_matches(List *tlist, Oid rettype, TupleDesc rettupdesc, char prokind);
static bool target_entry_has_compatible_type(TargetEntry *tle, Oid res_type, int32 res_typmod);
+static void plancache_rewrite_cb(struct CachedPlanSource *plansource, List *tlist, void *arg);
+
+static void register_plancache_cb(List *queryTree_list, List *plansource_list, Oid rettype, TupleDesc rettupdesc, char prokind);
+
/*
* Fill array of arguments with actual function argument types oids
*/
@@ -837,6 +850,66 @@ target_entry_has_compatible_type(TargetEntry *tle, Oid res_type, int32 res_typmo
return result;
}
+/* Rewrite queries when plan is revalidated */
+static void
+plancache_rewrite_cb(struct CachedPlanSource *plansource, List *queries, void *arg)
+{
+ SQLFunctionPlanSourceCallbackData *cbdata = (SQLFunctionPlanSourceCallbackData *) arg;
+
+ /* Shouldn't happen */
+ if (cbdata == NULL)
+ elog(ERROR, "plancache callback data is missing");
+
+ check_sql_fn_retval(list_make1(queries) /* expects list of lists */ , cbdata->rettype, cbdata->rettupdesc, cbdata->prokind, false, NULL, NULL);
+}
+
+
+/*
+ * Register plancache callback which fires after query rewrite if plansource is invalidated.
+ */
+static void
+register_plancache_cb(List *queryTree_list, List *plansource_list, Oid rettype, TupleDesc rettupdesc, char prokind)
+{
+ ListCell *qlc;
+ ListCell *slc;
+ ListCell *plc;
+ CachedPlanSource *modified_plansource = NULL;
+ SQLFunctionPlanSourceCallbackData *cbdata = NULL;
+
+ /* find plansource, which was modified by check_sql_fn_retval() */
+ forboth(qlc, queryTree_list, plc, plansource_list)
+ {
+ List *sublist = lfirst_node(List, qlc);
+
+ foreach(slc, sublist)
+ {
+ Query *q = lfirst_node(Query, slc);
+
+ if (q->canSetTag)
+ modified_plansource = (CachedPlanSource *) lfirst(plc);
+ }
+ }
+
+ /*
+ * We've modified some queries, so now should find corresponding
+ * plansource
+ */
+ if (modified_plansource == NULL)
+ elog(ERROR, "couldn't find plansource, corresponding to query with modified targetlist");
+
+ /*
+ * We don't care much about persistent storage for callback data or
+ * finding out actual rettype and rettupdesc as such callback is only
+ * registered for plans, which are not saved.
+ */
+ cbdata = palloc(sizeof(SQLFunctionPlanSourceCallbackData));
+ cbdata->rettype = rettype;
+ cbdata->rettupdesc = rettupdesc;
+ cbdata->prokind = prokind;
+
+ CachedPlanRegisterPostRewriteCallback(modified_plansource, plancache_rewrite_cb, (void *) cbdata);
+}
+
/*
* Check if result tlist would be changed by check_sql_fn_retval()
*/
@@ -1063,6 +1136,7 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
else
{
MemoryContext alianable_context = fcontext;
+ bool tlist_was_modified = false;
/* We need to preserve parse info */
if (use_plan_cache)
@@ -1179,7 +1253,8 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
rettupdesc,
procedureStruct->prokind,
false,
- &resulttlist);
+ &resulttlist,
+ &tlist_was_modified);
/*
* Queries could be rewritten by check_sql_fn_retval(). Now when they
@@ -1196,7 +1271,13 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
CachedPlanSource *plansource = lfirst(plc);
- /* Finish filling in the CachedPlanSource */
+ /*
+ * Finish filling in the CachedPlanSource. We force fixed
+ * result type to be sure that query rewriting by
+ * sql_fn_retval_cb() leads to the same result. And it should,
+ * as callback is used only when plansource is not saved
+ * (during one function call).
+ */
CompleteCachedPlan(plansource,
queryTree_sublist,
NULL,
@@ -1205,10 +1286,24 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
(ParserSetupHook) sql_fn_parser_setup,
fcache->pinfo,
CURSOR_OPT_PARALLEL_OK | CURSOR_OPT_NO_SCROLL,
- false);
+ true);
+
}
}
+ if (tlist_was_modified)
+ {
+ /* Avoid caching plan if check_sql_fn_retval() has modified query */
+ use_plan_cache = false;
+
+ /*
+ * Now we know that target query was modified. Find corresponding
+ * plansource and add callback, which would reapply these
+ * modifications if plan is invalidated.
+ */
+ register_plancache_cb(queryTree_list, plansource_list, rettype, rettupdesc, procedureStruct->prokind);
+ }
+
/* If we can possibly use cached plan entry, save it. */
if (use_plan_cache)
save_cached_plan_entry(&plan_cache_entry_key, procedureTuple, plansource_list, resulttlist, fcache->returnsTuple, fcache->pinfo, alianable_context);
@@ -2139,7 +2234,8 @@ check_sql_fn_retval(List *queryTreeLists,
Oid rettype, TupleDesc rettupdesc,
char prokind,
bool insertDroppedCols,
- List **resultTargetList)
+ List **resultTargetList,
+ bool *targetListModified)
{
bool is_tuple_result = false;
Query *parse;
@@ -2151,6 +2247,10 @@ check_sql_fn_retval(List *queryTreeLists,
List *upper_tlist = NIL;
bool upper_tlist_nontrivial = false;
ListCell *lc;
+ List *tlist_copy = NIL;
+
+ if (targetListModified)
+ *targetListModified = false;
if (resultTargetList)
*resultTargetList = NIL; /* initialize in case of VOID result */
@@ -2240,6 +2340,14 @@ check_sql_fn_retval(List *queryTreeLists,
* just does a projection.
*/
+ /*
+ * If caller wants to check check if tlist was modified, we have no much
+ * choice except copying original tlist and compare (as tlist could be
+ * modified in place).
+ */
+ if (targetListModified)
+ tlist_copy = copyObject(tlist);
+
/*
* Count the non-junk entries in the result targetlist.
*/
@@ -2343,6 +2451,12 @@ check_sql_fn_retval(List *queryTreeLists,
/* Return tlist if requested */
if (resultTargetList)
*resultTargetList = tlist;
+ if (targetListModified)
+ {
+ *targetListModified = !equal(tlist, tlist_copy);
+
+ list_free_deep(tlist_copy);
+ }
return true;
}
@@ -2514,6 +2628,12 @@ tlist_coercion_finished:
/* Return tlist (possibly modified) if requested */
if (resultTargetList)
*resultTargetList = upper_tlist;
+ if (targetListModified)
+ {
+ *targetListModified = upper_tlist_nontrivial || !equal(tlist, tlist_copy);
+
+ list_free_deep(tlist_copy);
+ }
return is_tuple_result;
}
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..0b7c9657c8c 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4742,7 +4742,7 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
if (check_sql_fn_retval(list_make1(querytree_list),
result_type, rettupdesc,
funcform->prokind,
- false, NULL))
+ false, NULL, NULL))
goto fail; /* reject whole-tuple-result cases */
/*
@@ -5288,7 +5288,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
if (!check_sql_fn_retval(list_make1(querytree_list),
fexpr->funcresulttype, rettupdesc,
funcform->prokind,
- true, NULL) &&
+ true, NULL, NULL) &&
(functypclass == TYPEFUNC_COMPOSITE ||
functypclass == TYPEFUNC_COMPOSITE_DOMAIN ||
functypclass == TYPEFUNC_RECORD))
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index da65a6010bd..51344caf411 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -279,6 +279,8 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
plansource->total_custom_cost = 0;
plansource->num_generic_plans = 0;
plansource->num_custom_plans = 0;
+ plansource->post_rewrite_cb = NULL;
+ plansource->post_rewrite_cb_arg = NULL;
MemoryContextSwitchTo(oldcxt);
@@ -306,6 +308,20 @@ CreateCachedPlanForQuery(Query *analyzed_parse_tree,
return plansource;
}
+/*
+ * CachedPlanRegisterPostRewriteCallback() registers plansource
+ * callback which fires after rewriting query during plan
+ * revalidation.
+ */
+void
+CachedPlanRegisterPostRewriteCallback(CachedPlanSource *plansource,
+ post_rewrite_cb_type post_rewrite_cb,
+ void* post_rewrite_cb_arg)
+{
+ plansource->post_rewrite_cb = post_rewrite_cb;
+ plansource->post_rewrite_cb_arg = post_rewrite_cb_arg;
+}
+
/*
* CreateOneShotCachedPlan: initially create a one-shot plan cache entry.
*
@@ -787,6 +803,11 @@ RevalidateCachedQuery(CachedPlanSource *plansource,
plansource->num_params,
queryEnv);
+
+ /* Call post-rewrite callback */
+ if (plansource->post_rewrite_cb)
+ plansource->post_rewrite_cb(plansource, tlist, plansource->post_rewrite_cb_arg);
+
/* Release snapshot if we got one */
if (snapshot_set)
PopActiveSnapshot();
@@ -1663,6 +1684,9 @@ CopyCachedPlan(CachedPlanSource *plansource)
newsource->rewriteRowSecurity = plansource->rewriteRowSecurity;
newsource->dependsOnRLS = plansource->dependsOnRLS;
+ newsource->post_rewrite_cb = plansource->post_rewrite_cb;
+ newsource->post_rewrite_cb_arg = plansource->post_rewrite_cb_arg;
+
newsource->gplan = NULL;
newsource->is_oneshot = false;
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index a6ae2e72d79..144a9b91fba 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -49,7 +49,8 @@ extern bool check_sql_fn_retval(List *queryTreeLists,
Oid rettype, TupleDesc rettupdesc,
char prokind,
bool insertDroppedCols,
- List **resultTargetList);
+ List **resultTargetList,
+ bool *targetListModified);
extern DestReceiver *CreateSQLFunctionDestReceiver(void);
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 1493f726649..8f0853062c8 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -35,6 +35,9 @@ typedef enum
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN,
} PlanCacheMode;
+struct CachedPlanSource;
+typedef void (*post_rewrite_cb_type) (struct CachedPlanSource *plansource, List *tlist, void *arg);
+
/* GUC parameter */
extern PGDLLIMPORT int plan_cache_mode;
@@ -134,6 +137,10 @@ typedef struct CachedPlanSource
double total_custom_cost; /* total cost of custom plans so far */
int64 num_custom_plans; /* # of custom plans included in total */
int64 num_generic_plans; /* # of generic plans */
+
+ /* Post-rewrite callback */
+ post_rewrite_cb_type post_rewrite_cb;
+ void *post_rewrite_cb_arg; /* post-rewrite callback argument */
} CachedPlanSource;
/*
@@ -211,6 +218,8 @@ extern void CompleteCachedPlan(CachedPlanSource *plansource,
int cursor_options,
bool fixed_result);
+extern void CachedPlanRegisterPostRewriteCallback(CachedPlanSource *plansource, post_rewrite_cb_type post_rewrite_cb, void* post_rewrite_cb_arg);
+
extern void SaveCachedPlan(CachedPlanSource *plansource);
extern void DropCachedPlan(CachedPlanSource *plansource);
--
2.43.0
From ea3963de57f8afc3a448e3f95b85ea5174c70405 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Fri, 7 Feb 2025 11:51:24 +0300
Subject: [PATCH 3/4] Introduce SQL functions plan cache
---
src/backend/executor/functions.c | 658 ++++++++++++++----
.../expected/test_extensions.out | 2 +-
2 files changed, 524 insertions(+), 136 deletions(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index d9eb49d9f68..7fe49dbe17c 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -18,6 +18,8 @@
#include "access/xact.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+#include "commands/trigger.h"
+#include "commands/event_trigger.h"
#include "executor/functions.h"
#include "funcapi.h"
#include "miscadmin.h"
@@ -137,6 +139,45 @@ typedef struct
typedef SQLFunctionCache *SQLFunctionCachePtr;
+/*
+ * Plan cache-related structures
+ */
+typedef struct SQLFunctionPlanKey
+{
+ Oid fn_oid;
+ Oid inputCollation;
+ Oid argtypes[FUNC_MAX_ARGS];
+} SQLFunctionPlanKey;
+
+typedef struct SQLFunctionPlanEntry
+{
+ SQLFunctionPlanKey key;
+
+ /* Fields required to invalidate a cache entry */
+ TransactionId fn_xmin;
+ ItemPointerData fn_tid;
+
+ /*
+ * result_tlist is required to recreate function execution state as well
+ * as to validate a cache entry
+ */
+ List *result_tlist;
+
+ bool returnsTuple; /* True if this function returns tuple */
+ List *plansource_list; /* List of CachedPlanSource for this
+ * function */
+
+ /*
+ * SQLFunctionParseInfoPtr is used as hooks arguments, so should persist
+ * across calls. Fortunately, if it doesn't, this means that argtypes or
+ * collation mismatches and we get new cache entry.
+ */
+ SQLFunctionParseInfoPtr pinfo; /* cached information about arguments */
+
+ MemoryContext entry_ctx; /* memory context for allocated fields of this entry */
+} SQLFunctionPlanEntry;
+
+static HTAB *sql_plan_cache_htab = NULL;
/* non-export function prototypes */
static Node *sql_fn_param_ref(ParseState *pstate, ParamRef *pref);
@@ -170,6 +211,48 @@ static bool sqlfunction_receive(TupleTableSlot *slot, DestReceiver *self);
static void sqlfunction_shutdown(DestReceiver *self);
static void sqlfunction_destroy(DestReceiver *self);
+/* SQL-functions plan cache-related routines */
+static void compute_plan_entry_key(SQLFunctionPlanKey *hashkey, FunctionCallInfo fcinfo, Form_pg_proc procedureStruct);
+static SQLFunctionPlanEntry *get_cached_plan_entry(SQLFunctionPlanKey *hashkey);
+static void save_cached_plan_entry(SQLFunctionPlanKey * hashkey, HeapTuple procedureTuple, List *plansource_list, List *result_tlist, bool returnsTuple, SQLFunctionParseInfoPtr pinfo, MemoryContext alianable_context);
+static void delete_cached_plan_entry(SQLFunctionPlanEntry * entry);
+
+static bool check_sql_fn_retval_matches(List *tlist, Oid rettype, TupleDesc rettupdesc, char prokind);
+static bool target_entry_has_compatible_type(TargetEntry *tle, Oid res_type, int32 res_typmod);
+
+/*
+ * Fill array of arguments with actual function argument types oids
+ */
+static void
+compute_argument_types(Oid *argOidVect, Form_pg_proc procedureStruct, Node *call_expr)
+{
+ int argnum;
+ int nargs;
+
+ nargs = procedureStruct->pronargs;
+ if (nargs > 0)
+ {
+ memcpy(argOidVect,
+ procedureStruct->proargtypes.values,
+ nargs * sizeof(Oid));
+
+ for (argnum = 0; argnum < nargs; argnum++)
+ {
+ Oid argtype = argOidVect[argnum];
+
+ if (IsPolymorphicType(argtype))
+ {
+ argtype = get_call_expr_argtype(call_expr, argnum);
+ if (argtype == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("could not determine actual type of argument declared %s",
+ format_type_be(argOidVect[argnum]))));
+ argOidVect[argnum] = argtype;
+ }
+ }
+ }
+}
/*
* Prepare the SQLFunctionParseInfo struct for parsing a SQL function body
@@ -203,31 +286,8 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple,
pinfo->nargs = nargs = procedureStruct->pronargs;
if (nargs > 0)
{
- Oid *argOidVect;
- int argnum;
-
- argOidVect = (Oid *) palloc(nargs * sizeof(Oid));
- memcpy(argOidVect,
- procedureStruct->proargtypes.values,
- nargs * sizeof(Oid));
-
- for (argnum = 0; argnum < nargs; argnum++)
- {
- Oid argtype = argOidVect[argnum];
-
- if (IsPolymorphicType(argtype))
- {
- argtype = get_call_expr_argtype(call_expr, argnum);
- if (argtype == InvalidOid)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("could not determine actual type of argument declared %s",
- format_type_be(argOidVect[argnum]))));
- argOidVect[argnum] = argtype;
- }
- }
-
- pinfo->argtypes = argOidVect;
+ pinfo->argtypes = (Oid *) palloc(nargs * sizeof(Oid));
+ compute_argument_types(pinfo->argtypes, procedureStruct, call_expr);
}
/*
@@ -605,6 +665,264 @@ init_execution_state(SQLFunctionCachePtr fcache,
return eslist;
}
+/*
+ * Compute key for searching plan entry in backend cache
+ */
+static void
+compute_plan_entry_key(SQLFunctionPlanKey * hashkey, FunctionCallInfo fcinfo, Form_pg_proc procedureStruct)
+{
+ MemSet(hashkey, 0, sizeof(SQLFunctionPlanKey));
+
+ hashkey->fn_oid = fcinfo->flinfo->fn_oid;
+
+ /* set input collation, if known */
+ hashkey->inputCollation = fcinfo->fncollation;
+
+ if (procedureStruct->pronargs > 0)
+ {
+ /* get the argument types */
+ compute_argument_types(hashkey->argtypes, procedureStruct, fcinfo->flinfo->fn_expr);
+ }
+}
+
+/*
+ * Get cached plan by pre-computed key
+ */
+static SQLFunctionPlanEntry *
+get_cached_plan_entry(SQLFunctionPlanKey * hashkey)
+{
+ SQLFunctionPlanEntry *plan_entry = NULL;
+
+ if (sql_plan_cache_htab)
+ {
+ plan_entry = (SQLFunctionPlanEntry *) hash_search(sql_plan_cache_htab,
+ hashkey,
+ HASH_FIND,
+ NULL);
+ }
+ return plan_entry;
+}
+
+/*
+ * Save function execution plan in cache
+ */
+static void
+save_cached_plan_entry(SQLFunctionPlanKey * hashkey, HeapTuple procedureTuple, List *plansource_list, List *result_tlist, bool returnsTuple, SQLFunctionParseInfoPtr pinfo, MemoryContext alianable_context)
+{
+ MemoryContext oldcontext;
+ MemoryContext entry_context;
+ SQLFunctionPlanEntry *entry;
+ ListCell *lc;
+ bool found;
+
+ if (sql_plan_cache_htab == NULL)
+ {
+ HASHCTL ctl;
+
+ ctl.keysize = sizeof(SQLFunctionPlanKey);
+ ctl.entrysize = sizeof(SQLFunctionPlanEntry);
+ ctl.hcxt = CacheMemoryContext;
+
+ sql_plan_cache_htab = hash_create("SQL function plan hash",
+ 100 /* arbitrary initial size */ ,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+ }
+
+ entry = (SQLFunctionPlanEntry *) hash_search(sql_plan_cache_htab,
+ hashkey,
+ HASH_ENTER,
+ &found);
+ if (found)
+ elog(WARNING, "trying to insert a function that already exists");
+
+ /*
+ * Create long-lived memory context that holds entry fields
+ */
+ entry_context = AllocSetContextCreate(CacheMemoryContext,
+ "SQL function plan entry context",
+ ALLOCSET_DEFAULT_SIZES);
+
+ oldcontext = MemoryContextSwitchTo(entry_context);
+
+ /* fill entry */
+ memcpy(&entry->key, hashkey, sizeof(SQLFunctionPlanKey));
+
+ entry->entry_ctx = entry_context;
+
+ /* Some generated data, like pinfo, should be reparented */
+ MemoryContextSetParent(alianable_context, entry->entry_ctx);
+
+ entry->pinfo = pinfo;
+
+ /* Preserve list in long-lived context */
+ if (plansource_list)
+ entry->plansource_list = list_copy(plansource_list);
+ else
+ entry->plansource_list = NULL;
+
+ entry->result_tlist = copyObject(result_tlist);
+
+ entry->returnsTuple = returnsTuple;
+
+ /* Fill fields needed to invalidate cache entry */
+ entry->fn_xmin = HeapTupleHeaderGetRawXmin(procedureTuple->t_data);
+ entry->fn_tid = procedureTuple->t_self;
+
+ /* Save plans */
+ foreach(lc, entry->plansource_list)
+ {
+ CachedPlanSource *plansource = (CachedPlanSource *) lfirst(lc);
+
+ SaveCachedPlan(plansource);
+ }
+ MemoryContextSwitchTo(oldcontext);
+
+}
+
+/*
+ * Remove plan from cache
+ */
+static void
+delete_cached_plan_entry(SQLFunctionPlanEntry * entry)
+{
+ ListCell *lc;
+ bool found;
+
+ /* Release plans */
+ foreach(lc, entry->plansource_list)
+ {
+ CachedPlanSource *plansource = (CachedPlanSource *) lfirst(lc);
+
+ DropCachedPlan(plansource);
+ }
+ MemoryContextDelete(entry->entry_ctx);
+
+ hash_search(sql_plan_cache_htab, &entry->key, HASH_REMOVE, &found);
+ Assert(found);
+}
+
+/*
+ * Determine if TargetEntry is compatible to specified type
+ */
+static bool
+target_entry_has_compatible_type(TargetEntry *tle, Oid res_type, int32 res_typmod)
+{
+ Var *var;
+ Node *cast_result;
+ bool result = true;
+
+ /* Are types equivalent? */
+ var = makeVarFromTargetEntry(1, tle);
+
+ cast_result = coerce_to_target_type(NULL,
+ (Node *) var,
+ var->vartype,
+ res_type, res_typmod,
+ COERCION_ASSIGNMENT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+
+ /*
+ * If conversion is not possible or requires a cast, entry is incompatible
+ * with the type.
+ */
+ if (cast_result == NULL || cast_result != (Node *) var)
+ result = false;
+
+ if (cast_result && cast_result != (Node *) var)
+ pfree(cast_result);
+ pfree(var);
+
+ return result;
+}
+
+/*
+ * Check if result tlist would be changed by check_sql_fn_retval()
+ */
+static bool
+check_sql_fn_retval_matches(List *tlist, Oid rettype, TupleDesc rettupdesc, char prokind)
+{
+ char fn_typtype;
+ int tlistlen;
+
+ /*
+ * Count the non-junk entries in the result targetlist.
+ */
+ tlistlen = ExecCleanTargetListLength(tlist);
+
+ fn_typtype = get_typtype(rettype);
+
+ if (fn_typtype == TYPTYPE_BASE ||
+ fn_typtype == TYPTYPE_DOMAIN ||
+ fn_typtype == TYPTYPE_ENUM ||
+ fn_typtype == TYPTYPE_RANGE ||
+ fn_typtype == TYPTYPE_MULTIRANGE)
+ {
+ TargetEntry *tle;
+
+ /* Something unexpected, invalidate cached plan */
+ if (tlistlen != 1)
+ return false;
+
+ tle = (TargetEntry *) linitial(tlist);
+
+ return target_entry_has_compatible_type(tle, rettype, -1);
+ }
+ else if (fn_typtype == TYPTYPE_COMPOSITE || rettype == RECORDOID)
+ {
+ ListCell *lc;
+ int colindex;
+ int tupnatts;
+
+ if (tlistlen == 1 && prokind != PROKIND_PROCEDURE)
+ {
+ TargetEntry *tle = (TargetEntry *) linitial(tlist);
+
+ return target_entry_has_compatible_type(tle, rettype, -1);
+ }
+
+ /* We consider results comnpatible if there's no tupledesc */
+ if (rettupdesc == NULL)
+ return true;
+
+ /*
+ * Verify that saved targetlist matches the return tuple type.
+ */
+ tupnatts = rettupdesc->natts;
+ colindex = 0;
+ foreach(lc, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr;
+
+ /* resjunk columns can simply be ignored */
+ if (tle->resjunk)
+ continue;
+
+ do
+ {
+ colindex++;
+ if (colindex > tupnatts)
+ return false;
+
+ attr = TupleDescAttr(rettupdesc, colindex - 1);
+ } while (attr->attisdropped);
+
+ if (!target_entry_has_compatible_type(tle, attr->atttypid, attr->atttypmod))
+ return false;
+ }
+
+ /* remaining columns in rettupdesc had better all be dropped */
+ for (colindex++; colindex <= tupnatts; colindex++)
+ {
+ if (!TupleDescCompactAttr(rettupdesc, colindex - 1)->attisdropped)
+ return false;
+ }
+ }
+ return true;
+}
+
/*
* Initialize the SQLFunctionCache for a SQL function
*/
@@ -626,6 +944,10 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
Datum tmp;
bool isNull;
List *plansource_list;
+ SQLFunctionPlanEntry *cached_plan_entry = NULL;
+ SQLFunctionPlanKey plan_cache_entry_key;
+ bool use_plan_cache;
+ bool plan_cache_entry_valid;
/*
* Create memory context that holds all the SQLFunctionCache data. It
@@ -683,15 +1005,6 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
fcache->readonly_func =
(procedureStruct->provolatile != PROVOLATILE_VOLATILE);
- /*
- * We need the actual argument types to pass to the parser. Also make
- * sure that parameter symbols are considered to have the function's
- * resolved input collation.
- */
- fcache->pinfo = prepare_sql_fn_parse_info(procedureTuple,
- finfo->fn_expr,
- collation);
-
/*
* And of course we need the function body text.
*/
@@ -704,123 +1017,201 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
Anum_pg_proc_prosqlbody,
&isNull);
+
+ use_plan_cache = true;
+ plan_cache_entry_valid = false;
+
/*
- * Parse and rewrite the queries in the function text. Use sublists to
- * keep track of the original query boundaries.
- *
- * Note: since parsing and planning is done in fcontext, we will generate
- * a lot of cruft that lives as long as the fcache does. This is annoying
- * but we'll not worry about it until the module is rewritten to use
- * plancache.c.
+ * If function is trigger, we can see different rowtypes or transition
+ * table names. So don't use cache for such plans.
*/
- queryTree_list = NIL;
- plansource_list = NIL;
- if (!isNull)
- {
- Node *n;
- List *stored_query_list;
+ if (CALLED_AS_TRIGGER(fcinfo) || CALLED_AS_EVENT_TRIGGER(fcinfo))
+ use_plan_cache = false;
- n = stringToNode(TextDatumGetCString(tmp));
- if (IsA(n, List))
- stored_query_list = linitial_node(List, castNode(List, n));
- else
- stored_query_list = list_make1(n);
+ if (use_plan_cache)
+ {
+ compute_plan_entry_key(&plan_cache_entry_key, fcinfo, procedureStruct);
- foreach(lc, stored_query_list)
+ cached_plan_entry = get_cached_plan_entry(&plan_cache_entry_key);
+ if (cached_plan_entry)
{
- Query *parsetree = lfirst_node(Query, lc);
- List *queryTree_sublist;
- CachedPlanSource *plansource;
-
- AcquireRewriteLocks(parsetree, true, false);
-
- plansource = CreateCachedPlanForQuery(parsetree, fcache->src, CreateCommandTag((Node *) parsetree));
- plansource_list = lappend(plansource_list, plansource);
-
- queryTree_sublist = pg_rewrite_query(parsetree);
- queryTree_list = lappend(queryTree_list, queryTree_sublist);
+ if (cached_plan_entry->fn_xmin == HeapTupleHeaderGetRawXmin(procedureTuple->t_data) &&
+ ItemPointerEquals(&cached_plan_entry->fn_tid, &procedureTuple->t_self))
+ {
+ /*
+ * Avoid using plan if returned result type doesn't match the
+ * expected one. check_sql_fn_retval() in this case would
+ * change query to match expected result type. But we've
+ * already planned query, possibly modified to match another
+ * result type. So discard the cached entry and replan.
+ */
+ if (check_sql_fn_retval_matches(cached_plan_entry->result_tlist, rettype, rettupdesc, procedureStruct->prokind))
+ plan_cache_entry_valid = true;
+ }
+ if (!plan_cache_entry_valid)
+ delete_cached_plan_entry(cached_plan_entry);
}
}
+
+ if (plan_cache_entry_valid)
+ {
+ plansource_list = cached_plan_entry->plansource_list;
+ resulttlist = copyObject(cached_plan_entry->result_tlist);
+ fcache->returnsTuple = cached_plan_entry->returnsTuple;
+ fcache->pinfo = cached_plan_entry->pinfo;
+ }
else
{
- List *raw_parsetree_list;
+ MemoryContext alianable_context = fcontext;
+
+ /* We need to preserve parse info */
+ if (use_plan_cache)
+ {
+ alianable_context = AllocSetContextCreate(CurrentMemoryContext,
+ "SQL function plan entry alianable context",
+ ALLOCSET_DEFAULT_SIZES);
+
+ MemoryContextSwitchTo(alianable_context);
+ }
- raw_parsetree_list = pg_parse_query(fcache->src);
+ /*
+ * We need the actual argument types to pass to the parser. Also make
+ * sure that parameter symbols are considered to have the function's
+ * resolved input collation.
+ */
+ fcache->pinfo = prepare_sql_fn_parse_info(procedureTuple,
+ finfo->fn_expr,
+ collation);
+
+ if (use_plan_cache)
+ MemoryContextSwitchTo(fcontext);
- foreach(lc, raw_parsetree_list)
+ /*
+ * Parse and rewrite the queries in the function text. Use sublists
+ * to keep track of the original query boundaries.
+ *
+ * Note: since parsing and planning is done in fcontext, we will
+ * generate a lot of cruft that lives as long as the fcache does. This
+ * is annoying but we'll not worry about it until the module is
+ * rewritten to use plancache.c.
+ */
+
+ plansource_list = NIL;
+
+ queryTree_list = NIL;
+ if (!isNull)
{
- RawStmt *parsetree = lfirst_node(RawStmt, lc);
- List *queryTree_sublist;
- CachedPlanSource *plansource;
-
- plansource = CreateCachedPlan(parsetree, fcache->src, CreateCommandTag(parsetree->stmt));
- plansource_list = lappend(plansource_list, plansource);
-
- queryTree_sublist = pg_analyze_and_rewrite_withcb(parsetree,
- fcache->src,
- (ParserSetupHook) sql_fn_parser_setup,
- fcache->pinfo,
- NULL);
- queryTree_list = lappend(queryTree_list, queryTree_sublist);
+ Node *n;
+ List *stored_query_list;
+
+ n = stringToNode(TextDatumGetCString(tmp));
+ if (IsA(n, List))
+ stored_query_list = linitial_node(List, castNode(List, n));
+ else
+ stored_query_list = list_make1(n);
+
+ foreach(lc, stored_query_list)
+ {
+ Query *parsetree = lfirst_node(Query, lc);
+ List *queryTree_sublist;
+ CachedPlanSource *plansource;
+
+ AcquireRewriteLocks(parsetree, true, false);
+
+ plansource = CreateCachedPlanForQuery(parsetree, fcache->src, CreateCommandTag((Node *) parsetree));
+ plansource_list = lappend(plansource_list, plansource);
+
+ queryTree_sublist = pg_rewrite_query(parsetree);
+ queryTree_list = lappend(queryTree_list, queryTree_sublist);
+ }
}
- }
+ else
+ {
+ List *raw_parsetree_list;
- /*
- * Check that there are no statements we don't want to allow.
- */
- check_sql_fn_statements(queryTree_list);
+ raw_parsetree_list = pg_parse_query(fcache->src);
- /*
- * Check that the function returns the type it claims to. Although in
- * simple cases this was already done when the function was defined, we
- * have to recheck because database objects used in the function's queries
- * might have changed type. We'd have to recheck anyway if the function
- * had any polymorphic arguments. Moreover, check_sql_fn_retval takes
- * care of injecting any required column type coercions. (But we don't
- * ask it to insert nulls for dropped columns; the junkfilter handles
- * that.)
- *
- * Note: we set fcache->returnsTuple according to whether we are returning
- * the whole tuple result or just a single column. In the latter case we
- * clear returnsTuple because we need not act different from the scalar
- * result case, even if it's a rowtype column. (However, we have to force
- * lazy eval mode in that case; otherwise we'd need extra code to expand
- * the rowtype column into multiple columns, since we have no way to
- * notify the caller that it should do that.)
- */
- fcache->returnsTuple = check_sql_fn_retval(queryTree_list,
- rettype,
- rettupdesc,
- procedureStruct->prokind,
- false,
- &resulttlist);
+ foreach(lc, raw_parsetree_list)
+ {
+ RawStmt *parsetree = lfirst_node(RawStmt, lc);
+ List *queryTree_sublist;
+ CachedPlanSource *plansource;
+
+ plansource = CreateCachedPlan(parsetree, fcache->src, CreateCommandTag(parsetree->stmt));
+ plansource_list = lappend(plansource_list, plansource);
+
+ queryTree_sublist = pg_analyze_and_rewrite_withcb(parsetree,
+ fcache->src,
+ (ParserSetupHook) sql_fn_parser_setup,
+ fcache->pinfo,
+ NULL);
+ queryTree_list = lappend(queryTree_list, queryTree_sublist);
+ }
+ }
- /*
- * 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;
+ /*
+ * Check that there are no statements we don't want to allow.
+ */
+ check_sql_fn_statements(queryTree_list);
+
+ /*
+ * Check that the function returns the type it claims to. Although in
+ * simple cases this was already done when the function was defined,
+ * we have to recheck because database objects used in the function's
+ * queries might have changed type. We'd have to recheck anyway if
+ * the function had any polymorphic arguments. Moreover,
+ * check_sql_fn_retval takes care of injecting any required column
+ * type coercions. (But we don't ask it to insert nulls for dropped
+ * columns; the junkfilter handles that.)
+ *
+ * Note: we set fcache->returnsTuple according to whether we are
+ * returning the whole tuple result or just a single column. In the
+ * latter case we clear returnsTuple because we need not act different
+ * from the scalar result case, even if it's a rowtype column.
+ * (However, we have to force lazy eval mode in that case; otherwise
+ * we'd need extra code to expand the rowtype column into multiple
+ * columns, since we have no way to notify the caller that it should
+ * do that.)
+ */
- forboth(qlc, queryTree_list, plc, plansource_list)
+ fcache->returnsTuple = check_sql_fn_retval(queryTree_list,
+ rettype,
+ rettupdesc,
+ procedureStruct->prokind,
+ 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)
{
- 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);
+ 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);
+ }
}
+
+ /* If we can possibly use cached plan entry, save it. */
+ if (use_plan_cache)
+ save_cached_plan_entry(&plan_cache_entry_key, procedureTuple, plansource_list, resulttlist, fcache->returnsTuple, fcache->pinfo, alianable_context);
}
/*
@@ -1118,9 +1509,6 @@ release_plans(List *cplans)
ReleaseCachedPlan(cplan, cplan->is_saved ? CurrentResourceOwner : NULL);
}
-
- /* Cleanup the list itself */
- list_free(cplans);
}
/*
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index d5388a1fecf..72bae1bf254 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -651,7 +651,7 @@ LINE 1: SELECT public.dep_req2() || ' req3b'
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT public.dep_req2() || ' req3b'
-CONTEXT: SQL function "dep_req3b" during startup
+CONTEXT: SQL function "dep_req3b" statement 1
DROP EXTENSION test_ext_req_schema3;
ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2; -- now ok
SELECT test_s_dep2.dep_req1();
--
2.43.0
From 5b6a47843604188981249a93b3ea11df4296d48b Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Thu, 23 Jan 2025 14:34:17 +0300
Subject: [PATCH 2/4] Use custom plan machinery for SQL function
---
src/backend/executor/functions.c | 219 +++++++++++++++++-----
src/backend/utils/cache/plancache.c | 103 ++++++++--
src/backend/utils/misc/guc_tables.c | 1 +
src/include/utils/plancache.h | 5 +
src/test/regress/expected/rowsecurity.out | 51 +++++
src/test/regress/expected/rules.out | 35 ++++
src/test/regress/sql/rowsecurity.sql | 41 ++++
src/test/regress/sql/rules.sql | 24 +++
8 files changed, 417 insertions(+), 62 deletions(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 12565d0a7e0..d9eb49d9f68 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -33,10 +33,10 @@
#include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/plancache.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
-
/*
* Specialized DestReceiver for collecting query output in a SQL function
*/
@@ -112,6 +112,12 @@ typedef struct
JunkFilter *junkFilter; /* will be NULL if function returns VOID */
+ /* Cached plans support */
+ List *plansource_list; /* list of plansource */
+ List *cplan_list; /* list of cached plans */
+ int planning_stmt_number; /* the number of statement we are
+ * currently planning */
+
/*
* func_state is a List of execution_state records, each of which is the
* first for its original parsetree, with any additional records chained
@@ -122,6 +128,8 @@ typedef struct
MemoryContext fcontext; /* memory context holding this struct and all
* subsidiary data */
+ MemoryContext planning_context; /* memory context which is used for
+ * planning */
LocalTransactionId lxid; /* lxid in which cache was made */
SubTransactionId subxid; /* subxid in which cache was made */
@@ -138,10 +146,9 @@ static Node *sql_fn_make_param(SQLFunctionParseInfoPtr pinfo,
int paramno, int location);
static Node *sql_fn_resolve_param_name(SQLFunctionParseInfoPtr pinfo,
const char *paramname, int location);
-static List *init_execution_state(List *queryTree_list,
- SQLFunctionCachePtr fcache,
+static List *init_execution_state(SQLFunctionCachePtr fcache,
bool lazyEvalOK);
-static void init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK);
+static void init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK);
static void postquel_start(execution_state *es, SQLFunctionCachePtr fcache);
static bool postquel_getnext(execution_state *es, SQLFunctionCachePtr fcache);
static void postquel_end(execution_state *es);
@@ -495,50 +502,57 @@ check_planned_stmt(PlannedStmt *stmt, SQLFunctionCachePtr fcache)
* querytrees. The sublist structure denotes the original query boundaries.
*/
static List *
-init_execution_state(List *queryTree_list,
- SQLFunctionCachePtr fcache,
+init_execution_state(SQLFunctionCachePtr fcache,
bool lazyEvalOK)
{
List *eslist = NIL;
+ List *cplan_list = NIL;
execution_state *lasttages = NULL;
- ListCell *lc1;
- foreach(lc1, queryTree_list)
+ /* We use lc1 index, not lc1 itself, so mark it unused */
+ ListCell *lc1 pg_attribute_unused();
+ MemoryContext oldcontext;
+
+ /*
+ * Invalidate func_state prior to resetting - otherwise error callback can
+ * access it
+ */
+ fcache->func_state = NIL;
+ MemoryContextReset(fcache->planning_context);
+
+ oldcontext = MemoryContextSwitchTo(fcache->planning_context);
+
+ foreach(lc1, fcache->plansource_list)
{
- List *qtlist = lfirst_node(List, lc1);
execution_state *firstes = NULL;
execution_state *preves = NULL;
ListCell *lc2;
+ CachedPlan *cplan;
+ CachedPlanSource *plansource = (CachedPlanSource *) lfirst(lc1);
+
+ /* Save statement number for error reporting */
+ fcache->planning_stmt_number = foreach_current_index(lc1) + 1 /* cur_idx starts with 0 */ ;
- foreach(lc2, qtlist)
+ /*
+ * Get plan for the query. If paramLI is set, we can get custom plan
+ */
+ cplan = GetCachedPlan(plansource, fcache->paramLI, plansource->is_saved ? CurrentResourceOwner : 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)
{
- Query *queryTree = lfirst_node(Query, lc2);
- PlannedStmt *stmt;
+ PlannedStmt *stmt = lfirst(lc2);
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
- 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);
/* OK, build the execution_state for this query */
newes = (execution_state *) palloc(sizeof(execution_state));
+
if (preves)
preves->next = newes;
else
@@ -551,7 +565,7 @@ init_execution_state(List *queryTree_list,
newes->stmt = stmt;
newes->qd = NULL;
- if (queryTree->canSetTag)
+ if (stmt->canSetTag)
lasttages = newes;
preves = newes;
@@ -583,6 +597,11 @@ init_execution_state(List *queryTree_list,
fcache->lazyEval = lasttages->lazyEval = true;
}
+ /* We've finished planning, reset planning statement number */
+ fcache->planning_stmt_number = 0;
+ fcache->cplan_list = cplan_list;
+
+ MemoryContextSwitchTo(oldcontext);
return eslist;
}
@@ -590,7 +609,7 @@ init_execution_state(List *queryTree_list,
* Initialize the SQLFunctionCache for a SQL function
*/
static void
-init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
+init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool *lazyEvalOK)
{
FmgrInfo *finfo = fcinfo->flinfo;
Oid foid = finfo->fn_oid;
@@ -606,6 +625,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
@@ -624,6 +644,10 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
*/
fcache = (SQLFunctionCachePtr) palloc0(sizeof(SQLFunctionCache));
fcache->fcontext = fcontext;
+ /* Create separate context for planning */
+ fcache->planning_context = AllocSetContextCreate(fcache->fcontext,
+ "SQL language functions planning context",
+ ALLOCSET_SMALL_SIZES);
finfo->fn_extra = fcache;
/*
@@ -690,6 +714,7 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
* plancache.c.
*/
queryTree_list = NIL;
+ plansource_list = NIL;
if (!isNull)
{
Node *n;
@@ -705,8 +730,13 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
{
Query *parsetree = lfirst_node(Query, lc);
List *queryTree_sublist;
+ CachedPlanSource *plansource;
AcquireRewriteLocks(parsetree, true, false);
+
+ plansource = CreateCachedPlanForQuery(parsetree, fcache->src, CreateCommandTag((Node *) parsetree));
+ plansource_list = lappend(plansource_list, plansource);
+
queryTree_sublist = pg_rewrite_query(parsetree);
queryTree_list = lappend(queryTree_list, queryTree_sublist);
}
@@ -721,6 +751,10 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
{
RawStmt *parsetree = lfirst_node(RawStmt, lc);
List *queryTree_sublist;
+ CachedPlanSource *plansource;
+
+ plansource = CreateCachedPlan(parsetree, fcache->src, CreateCommandTag(parsetree->stmt));
+ plansource_list = lappend(plansource_list, plansource);
queryTree_sublist = pg_analyze_and_rewrite_withcb(parsetree,
fcache->src,
@@ -761,6 +795,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
@@ -802,13 +864,10 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK)
* materialize mode, but to add more smarts in init_execution_state
* about this, we'd probably need a three-way flag instead of bool.
*/
- lazyEvalOK = true;
+ *lazyEvalOK = true;
}
- /* Finally, plan the queries */
- fcache->func_state = init_execution_state(queryTree_list,
- fcache,
- lazyEvalOK);
+ fcache->plansource_list = plansource_list;
/* Mark fcache with time of creation to show it's valid */
fcache->lxid = MyProc->vxid.lxid;
@@ -979,7 +1038,12 @@ 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.
+ */
+ prm->pflags = PARAM_FLAG_CONST;
+
prm->ptype = argtypes[i];
}
}
@@ -1032,6 +1096,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, cplan->is_saved ? CurrentResourceOwner : NULL);
+ }
+
+ /* Cleanup the list itself */
+ list_free(cplans);
+}
+
/*
* fmgr_sql: function call manager for SQL functions
*/
@@ -1050,6 +1141,7 @@ fmgr_sql(PG_FUNCTION_ARGS)
Datum result;
List *eslist;
ListCell *eslc;
+ bool build_cached_plans = false;
/*
* Setup error traceback support for ereport()
@@ -1105,7 +1197,7 @@ fmgr_sql(PG_FUNCTION_ARGS)
if (fcache == NULL)
{
- init_sql_fcache(fcinfo, PG_GET_COLLATION(), lazyEvalOK);
+ init_sql_fcache(fcinfo, PG_GET_COLLATION(), &lazyEvalOK);
fcache = (SQLFunctionCachePtr) fcinfo->flinfo->fn_extra;
}
@@ -1139,12 +1231,37 @@ fmgr_sql(PG_FUNCTION_ARGS)
break;
}
+ /*
+ * We skip actual planning for initial run, so 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 */
+ fcache->func_state = init_execution_state(fcache,
+ lazyEvalOK);
+ /* restore execution state and eslist-related variables */
+ eslist = fcache->func_state;
+ /* find the first non-NULL execution state */
+ foreach(eslc, eslist)
+ {
+ es = (execution_state *) lfirst(eslc);
+
+ if (es)
+ break;
+ }
+ }
+ }
/*
* Build tuplestore to hold results, if we don't have one already. Note
@@ -1399,6 +1516,10 @@ fmgr_sql(PG_FUNCTION_ARGS)
es = es->next;
}
}
+
+ /* Release plans when functions stops executing */
+ release_plans(fcache->cplan_list);
+ fcache->cplan_list = NULL;
}
error_context_stack = sqlerrcontext.previous;
@@ -1438,13 +1559,19 @@ sql_exec_error_callback(void *arg)
}
/*
- * Try to determine where in the function we failed. If there is a query
- * with non-null QueryDesc, finger it. (We check this rather than looking
- * for F_EXEC_RUN state, so that errors during ExecutorStart or
+ * Try to determine where in the function we failed. If failure happens
+ * while building plans, look at planning_stmt_number. Else if there is a
+ * query with non-null QueryDesc, finger it. (We check this rather than
+ * looking for F_EXEC_RUN state, so that errors during ExecutorStart or
* ExecutorEnd are blamed on the appropriate query; see postquel_start and
* postquel_end.)
*/
- if (fcache->func_state)
+ if (fcache->planning_stmt_number)
+ {
+ errcontext("SQL function \"%s\" statement %d",
+ fcache->fname, fcache->planning_stmt_number);
+ }
+ else if (fcache->func_state)
{
execution_state *es;
int query_num;
@@ -1530,6 +1657,10 @@ ShutdownSQLFunction(Datum arg)
tuplestore_end(fcache->tstore);
fcache->tstore = NULL;
+ /* Release plans when functions stops executing */
+ release_plans(fcache->cplan_list);
+ fcache->cplan_list = NULL;
+
/* execUtils will deregister the callback... */
fcache->shutdown_reg = false;
}
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 55db8f53705..da65a6010bd 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -63,6 +63,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/lmgr.h"
#include "tcop/pquery.h"
#include "tcop/utility.h"
@@ -74,18 +75,6 @@
#include "utils/syscache.h"
-/*
- * We must skip "overhead" operations that involve database access when the
- * cached plan's subject statement is a transaction control command or one
- * that requires a snapshot not to be set yet (such as SET or LOCK). More
- * generally, statements that do not require parse analysis/rewrite/plan
- * activity never need to be revalidated, so we can treat them all like that.
- * For the convenience of postgres.c, treat empty statements that way too.
- */
-#define StmtPlanRequiresRevalidation(plansource) \
- ((plansource)->raw_parse_tree != NULL && \
- stmt_requires_parse_analysis((plansource)->raw_parse_tree))
-
/*
* This is the head of the backend's list of "saved" CachedPlanSources (i.e.,
* those that are in long-lived storage and are examined for sinval events).
@@ -130,6 +119,46 @@ static const ResourceOwnerDesc planref_resowner_desc =
.DebugPrint = NULL /* the default message is fine */
};
+/*
+ * We must skip "overhead" operations that involve database access when the
+ * cached plan's subject statement is a transaction control command or one
+ * that requires a snapshot not to be set yet (such as SET or LOCK). More
+ * generally, statements that do not require parse analysis/rewrite/plan
+ * activity never need to be revalidated, so we can treat them all like that.
+ * For the convenience of postgres.c, treat empty statements that way too.
+ * If plansource doesn't have raw_parse_tree, look at query_list to find out
+ * if there are any non-utility statements. Also some utility statements
+ * can require revalidation. The logic is the same as in stmt_requires_parse_analysis().
+ */
+static inline bool
+StmtPlanRequiresRevalidation(CachedPlanSource *plansource)
+{
+ if (plansource->raw_parse_tree != NULL)
+ {
+ return stmt_requires_parse_analysis(plansource->raw_parse_tree);
+ }
+ else if (plansource->analyzed_parse_tree)
+ {
+ Query *query = plansource->analyzed_parse_tree;
+
+ if (query->commandType != CMD_UTILITY)
+ return true;
+
+ /* should match stmt_requires_parse_analysis() */
+ switch (nodeTag(query->utilityStmt))
+ {
+ case T_DeclareCursorStmt:
+ case T_ExplainStmt:
+ case T_CreateTableAsStmt:
+ case T_CallStmt:
+ return true;
+ default:
+ break;
+ }
+ }
+ return false;
+}
+
/* Convenience wrappers over ResourceOwnerRemember/Forget */
static inline void
ResourceOwnerRememberPlanCacheRef(ResourceOwner owner, CachedPlan *plan)
@@ -184,7 +213,8 @@ InitPlanCache(void)
* Once constructed, the cached plan can be made longer-lived, if needed,
* by calling SaveCachedPlan.
*
- * raw_parse_tree: output of raw_parser(), or NULL if empty query
+ * raw_parse_tree: output of raw_parser(), or NULL if empty query, can
+ * also be NULL if plansource->analyzed_parse_tree is set instead
* query_string: original query text
* commandTag: command tag for query, or UNKNOWN if empty query
*/
@@ -219,6 +249,7 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
plansource = (CachedPlanSource *) palloc0(sizeof(CachedPlanSource));
plansource->magic = CACHEDPLANSOURCE_MAGIC;
plansource->raw_parse_tree = copyObject(raw_parse_tree);
+ plansource->analyzed_parse_tree = NULL;
plansource->query_string = pstrdup(query_string);
MemoryContextSetIdentifier(source_context, plansource->query_string);
plansource->commandTag = commandTag;
@@ -254,6 +285,27 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
return plansource;
}
+/*
+ * CreateCachedPlanForQuery: initially create a plan cache entry
+ * for parsed and analyzed query. Unlike CreateCachedPlan(),
+ * it preserves analyzed parse tree, not raw parse tree.
+ */
+CachedPlanSource *
+CreateCachedPlanForQuery(Query *analyzed_parse_tree,
+ const char *query_string,
+ CommandTag commandTag)
+{
+ CachedPlanSource *plansource;
+ MemoryContext oldcxt;
+
+ plansource = CreateCachedPlan(NULL, query_string, commandTag);
+ oldcxt = MemoryContextSwitchTo(plansource->context);
+ plansource->analyzed_parse_tree = copyObject(analyzed_parse_tree);
+ MemoryContextSwitchTo(oldcxt);
+
+ return plansource;
+}
+
/*
* CreateOneShotCachedPlan: initially create a one-shot plan cache entry.
*
@@ -708,7 +760,20 @@ RevalidateCachedQuery(CachedPlanSource *plansource,
*/
rawtree = copyObject(plansource->raw_parse_tree);
if (rawtree == NULL)
- tlist = NIL;
+ {
+ /* Working on pre-analyzed query */
+ if (plansource->analyzed_parse_tree)
+ {
+ /* Copy analyzed_parse_tree prevent its corruption */
+ Query *analyzed_tree = copyObject(plansource->analyzed_parse_tree);
+
+ AcquireRewriteLocks(analyzed_tree, true, false);
+ tlist = pg_rewrite_query(analyzed_tree);
+ }
+ else
+ /* Utility command */
+ tlist = NIL;
+ }
else if (plansource->parserSetup != NULL)
tlist = pg_analyze_and_rewrite_withcb(rawtree,
plansource->query_string,
@@ -945,12 +1010,13 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
/*
* If a snapshot is already set (the normal case), we can just use that
- * for planning. But if it isn't, and we need one, install one.
+ * for planning. But if it isn't, and we need one, install one. If
+ * plansource has raw_parse_tree set, we check if it requires parse
+ * analyze, which is exactly the case when it requires snapshot. If
+ * raw_parse_tree is not set, check the same conditions for query_list.
*/
snapshot_set = false;
- if (!ActiveSnapshotSet() &&
- plansource->raw_parse_tree &&
- analyze_requires_snapshot(plansource->raw_parse_tree))
+ if (!ActiveSnapshotSet() && StmtPlanRequiresRevalidation(plansource))
{
PushActiveSnapshot(GetTransactionSnapshot());
snapshot_set = true;
@@ -1559,6 +1625,7 @@ CopyCachedPlan(CachedPlanSource *plansource)
newsource = (CachedPlanSource *) palloc0(sizeof(CachedPlanSource));
newsource->magic = CACHEDPLANSOURCE_MAGIC;
newsource->raw_parse_tree = copyObject(plansource->raw_parse_tree);
+ newsource->analyzed_parse_tree = copyObject(plansource->analyzed_parse_tree);
newsource->query_string = pstrdup(plansource->query_string);
MemoryContextSetIdentifier(source_context, newsource->query_string);
newsource->commandTag = plansource->commandTag;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 38cb9e970d5..f5f5abed595 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"
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 46072d311b1..1493f726649 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -25,6 +25,7 @@
/* Forward declaration, to avoid including parsenodes.h here */
struct RawStmt;
+struct Query;
/* possible values for plan_cache_mode */
typedef enum
@@ -97,6 +98,7 @@ typedef struct CachedPlanSource
{
int magic; /* should equal CACHEDPLANSOURCE_MAGIC */
struct RawStmt *raw_parse_tree; /* output of raw_parser(), or NULL */
+ struct Query *analyzed_parse_tree; /* analyzed parse tree or NULL */
const char *query_string; /* source text of query */
CommandTag commandTag; /* 'nuff said */
Oid *param_types; /* array of parameter type OIDs, or NULL */
@@ -193,6 +195,9 @@ extern void ReleaseAllPlanCacheRefsInOwner(ResourceOwner owner);
extern CachedPlanSource *CreateCachedPlan(struct RawStmt *raw_parse_tree,
const char *query_string,
CommandTag commandTag);
+extern CachedPlanSource *CreateCachedPlanForQuery(struct Query *analyzed_parse_tree,
+ const char *query_string,
+ CommandTag commandTag);
extern CachedPlanSource *CreateOneShotCachedPlan(struct RawStmt *raw_parse_tree,
const char *query_string,
CommandTag commandTag);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index fd5654df35e..9c26ad67fdf 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4666,6 +4666,57 @@ RESET ROLE;
DROP FUNCTION rls_f();
DROP VIEW rls_v;
DROP TABLE rls_t;
+-- RLS changes invalidate cached function plans
+create table rls_t (c text);
+create table test_t (c text);
+insert into rls_t values ('a'), ('b'), ('c'), ('d');
+insert into test_t values ('a'), ('b');
+alter table rls_t enable row level security;
+grant select on rls_t to regress_rls_alice;
+grant select on test_t to regress_rls_alice;
+create policy p1 on rls_t for select to regress_rls_alice using (c = current_setting('rls_test.blah'));
+-- Function changes row_security setting and so invalidates plan
+create or replace function rls_f(text)
+ RETURNS text
+ LANGUAGE sql
+BEGIN ATOMIC
+ select set_config('rls_test.blah', $1, true) || set_config('row_security', 'false', true) || string_agg(c, ',' order by c) from rls_t;
+END;
+-- Table owner bypasses RLS
+select rls_f(c) from test_t order by rls_f;
+ rls_f
+-------------
+ aoffa,b,c,d
+ boffa,b,c,d
+(2 rows)
+
+set role regress_rls_alice;
+-- For casual user changes in row_security setting lead
+-- to error during query rewrite
+select rls_f(c) from test_t order by rls_f;
+ERROR: query would be affected by row-level security policy for table "rls_t"
+CONTEXT: SQL function "rls_f" statement 1
+reset role;
+set plan_cache_mode to force_generic_plan;
+-- Table owner bypasses RLS, but cached plan invalidates
+select rls_f(c) from test_t order by rls_f;
+ rls_f
+-------------
+ aoffa,b,c,d
+ boffa,b,c,d
+(2 rows)
+
+-- For casual user changes in row_security setting lead
+-- to plan invalidation and error during query rewrite
+set role regress_rls_alice;
+select rls_f(c) from test_t order by rls_f;
+ERROR: query would be affected by row-level security policy for table "rls_t"
+CONTEXT: SQL function "rls_f" statement 1
+reset role;
+reset plan_cache_mode;
+reset rls_test.blah;
+drop function rls_f;
+drop table rls_t, test_t;
--
-- Clean up objects
--
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 3361f6a69c9..03960d9e696 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3880,3 +3880,38 @@ DROP TABLE ruletest_t3;
DROP TABLE ruletest_t2;
DROP TABLE ruletest_t1;
DROP USER regress_rule_user1;
+-- Test that SQL functions correctly handle DO NOTHING rule
+CREATE TABLE some_data (i int, data text);
+CREATE TABLE some_data_values (i int, data text);
+CREATE FUNCTION insert_data(i int, data text)
+RETURNS INT
+AS $$
+INSERT INTO some_data VALUES ($1, $2);
+SELECT 1;
+$$ LANGUAGE SQL;
+INSERT INTO some_data_values SELECT i , 'data'|| i FROM generate_series(1, 10) i;
+CREATE RULE some_data_noinsert AS ON INSERT TO some_data DO INSTEAD NOTHING;
+SELECT insert_data(i, data) FROM some_data_values;
+ insert_data
+-------------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(10 rows)
+
+SELECT * FROM some_data ORDER BY i;
+ i | data
+---+------
+(0 rows)
+
+DROP RULE some_data_noinsert ON some_data;
+DROP TABLE some_data_values;
+DROP TABLE some_data;
+DROP FUNCTION insert_data(int, text);
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index cf09f62eaba..418d8ea95fb 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2280,6 +2280,47 @@ DROP FUNCTION rls_f();
DROP VIEW rls_v;
DROP TABLE rls_t;
+-- RLS changes invalidate cached function plans
+create table rls_t (c text);
+create table test_t (c text);
+
+insert into rls_t values ('a'), ('b'), ('c'), ('d');
+insert into test_t values ('a'), ('b');
+alter table rls_t enable row level security;
+grant select on rls_t to regress_rls_alice;
+grant select on test_t to regress_rls_alice;
+create policy p1 on rls_t for select to regress_rls_alice using (c = current_setting('rls_test.blah'));
+
+-- Function changes row_security setting and so invalidates plan
+create or replace function rls_f(text)
+ RETURNS text
+ LANGUAGE sql
+BEGIN ATOMIC
+ select set_config('rls_test.blah', $1, true) || set_config('row_security', 'false', true) || string_agg(c, ',' order by c) from rls_t;
+END;
+
+-- Table owner bypasses RLS
+select rls_f(c) from test_t order by rls_f;
+set role regress_rls_alice;
+-- For casual user changes in row_security setting lead
+-- to error during query rewrite
+select rls_f(c) from test_t order by rls_f;
+reset role;
+
+set plan_cache_mode to force_generic_plan;
+-- Table owner bypasses RLS, but cached plan invalidates
+select rls_f(c) from test_t order by rls_f;
+-- For casual user changes in row_security setting lead
+-- to plan invalidation and error during query rewrite
+set role regress_rls_alice;
+select rls_f(c) from test_t order by rls_f;
+reset role;
+reset plan_cache_mode;
+reset rls_test.blah;
+
+drop function rls_f;
+drop table rls_t, test_t;
+
--
-- Clean up objects
--
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index fdd3ff1d161..505449452ee 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1432,3 +1432,27 @@ DROP TABLE ruletest_t2;
DROP TABLE ruletest_t1;
DROP USER regress_rule_user1;
+
+-- Test that SQL functions correctly handle DO NOTHING rule
+CREATE TABLE some_data (i int, data text);
+CREATE TABLE some_data_values (i int, data text);
+
+CREATE FUNCTION insert_data(i int, data text)
+RETURNS INT
+AS $$
+INSERT INTO some_data VALUES ($1, $2);
+SELECT 1;
+$$ LANGUAGE SQL;
+
+INSERT INTO some_data_values SELECT i , 'data'|| i FROM generate_series(1, 10) i;
+
+CREATE RULE some_data_noinsert AS ON INSERT TO some_data DO INSTEAD NOTHING;
+
+SELECT insert_data(i, data) FROM some_data_values;
+
+SELECT * FROM some_data ORDER BY i;
+
+DROP RULE some_data_noinsert ON some_data;
+DROP TABLE some_data_values;
+DROP TABLE some_data;
+DROP FUNCTION insert_data(int, text);
--
2.43.0
From 381e81e4a525296dc11c5e4cc3ffd6bbca91e8c0 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Wed, 29 Jan 2025 16:23:30 +0300
Subject: [PATCH 1/4] Split out SQL functions checks from
init_execution_state()
---
src/backend/executor/functions.c | 62 ++++++++++++++++++--------------
1 file changed, 36 insertions(+), 26 deletions(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 757f8068e21..12565d0a7e0 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -454,6 +454,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.
*
@@ -500,32 +534,8 @@ init_execution_state(List *queryTree_list,
CURSOR_OPT_PARALLEL_OK,
NULL);
- /*
- * 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))));
+ /* Check that stmt is valid for SQL function */
+ check_planned_stmt(stmt, fcache);
/* OK, build the execution_state for this query */
newes = (execution_state *) palloc(sizeof(execution_state));
--
2.43.0
