Change SQL-language functions to use the plan cache.

In the historical implementation of SQL functions (if they don't get
inlined), we built plans for all the contained queries at first call
within an outer query, and then re-used those plans for the duration
of the outer query, and then forgot everything.  This was not ideal,
not least because the plans could not be customized to specific values
of the function's parameters.  Our plancache infrastructure seems
mature enough to be used here.  That will solve both the problem with
not being able to build custom plans and the problem with not being
able to share work across successive outer queries.

Aside from those performance concerns, this change fixes a
longstanding bugaboo with SQL functions: you could not write DDL that
would affect later statements in the same function.  That's mostly
still true with new-style SQL functions, since the results of parse
analysis are baked into the stored query trees (and protected by
dependency records).  But for old-style SQL functions, it will now
work much as it does with PL/pgSQL functions, because we delay parse
analysis and planning of each query until we're ready to run it.
Some edge cases that require replanning are now handled better too;
see for example the new rowsecurity test, where we now detect an RLS
context change that was previously missed.

One other edge-case change that might be worthy of a release note
is that we now insist that a SQL function's result be generated
by the physically-last query within it.  Previously, if the last
original query was deleted by a DO INSTEAD NOTHING rule, we'd be
willing to take the result from the preceding query instead.
This behavior was undocumented except in source-code comments,
and it seems hard to believe that anyone's relying on it.

Along the way to this feature, we needed a few infrastructure changes:

* The plancache can now take either a raw parse tree or an
analyzed-but-not-rewritten Query as the starting point for a
CachedPlanSource.  If given a Query, it is caller's responsibility
that nothing will happen to invalidate that form of the query.
We use this for new-style SQL functions, where what's in pg_proc is
serialized Query(s) and we trust the dependency mechanism to disallow
DDL that would break those.

* The plancache now offers a way to invoke a post-rewrite callback
to examine/modify the rewritten parse tree when it is rebuilding
the parse trees after a cache invalidation.  We need this because
SQL functions sometimes adjust the parse tree to make its output
exactly match the declared result type; if the plan gets rebuilt,
that has to be re-done.

* There is a new backend module utils/cache/funccache.c that
abstracts the idea of caching data about a specific function
usage (a particular function and set of input data types).
The code in it is moved almost verbatim from PL/pgSQL, which
has done that for a long time.  We use that logic now for
SQL-language functions too, and maybe other PLs will have use
for it in the future.

Author: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Co-authored-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://postgr.es/m/8216639.NyiUUSuA9g@aivenlaptop

2 files changed, 161 insertions, 4 deletions

diff --git a/src/include/utils/funccache.h b/src/include/utils/funccache.h
new file mode 100644
index 00000000000..e0112ebfa11
--- /dev/null
+++ b/src/include/utils/funccache.h
@@ -0,0 +1,134 @@
+/*-------------------------------------------------------------------------
+ *
+ * funccache.h
+ *	  Function cache definitions.
+ *
+ * See funccache.c for comments.
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/utils/funccache.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef FUNCCACHE_H
+#define FUNCCACHE_H
+
+#include "access/htup_details.h"
+#include "fmgr.h"
+#include "storage/itemptr.h"
+
+struct CachedFunctionHashKey;	/* forward references */
+struct CachedFunction;
+
+/*
+ * Callback that cached_function_compile() invokes when it's necessary to
+ * compile a cached function.  The callback must fill in *function (except
+ * for the fields of struct CachedFunction), or throw an error if trouble.
+ *	fcinfo: current call information
+ *	procTup: function's pg_proc row from catcache
+ *	hashkey: hash key that will be used for the function
+ *	function: pre-zeroed workspace, of size passed to cached_function_compile()
+ *	forValidator: passed through from cached_function_compile()
+ */
+typedef void (*CachedFunctionCompileCallback) (FunctionCallInfo fcinfo,
+											   HeapTuple procTup,
+											   const struct CachedFunctionHashKey *hashkey,
+											   struct CachedFunction *function,
+											   bool forValidator);
+
+/*
+ * Callback called when discarding a cache entry.  Free any free-able
+ * subsidiary data of cfunc, but not the struct CachedFunction itself.
+ */
+typedef void (*CachedFunctionDeleteCallback) (struct CachedFunction *cfunc);
+
+/*
+ * Hash lookup key for functions.  This must account for all aspects
+ * of a specific call that might lead to different data types or
+ * collations being used within the function.
+ */
+typedef struct CachedFunctionHashKey
+{
+	Oid			funcOid;
+
+	bool		isTrigger;		/* true if called as a DML trigger */
+	bool		isEventTrigger; /* true if called as an event trigger */
+
+	/* be careful that pad bytes in this struct get zeroed! */
+
+	/*
+	 * We include the language-specific size of the function's cache entry in
+	 * the cache key.  This covers the case where CREATE OR REPLACE FUNCTION
+	 * is used to change the implementation language, and the new language
+	 * also uses funccache.c but needs a different-sized cache entry.
+	 */
+	Size		cacheEntrySize;
+
+	/*
+	 * For a trigger function, the OID of the trigger is part of the hash key
+	 * --- we want to compile the trigger function separately for each trigger
+	 * it is used with, in case the rowtype or transition table names are
+	 * different.  Zero if not called as a DML trigger.
+	 */
+	Oid			trigOid;
+
+	/*
+	 * We must include the input collation as part of the hash key too,
+	 * because we have to generate different plans (with different Param
+	 * collations) for different collation settings.
+	 */
+	Oid			inputCollation;
+
+	/* Number of arguments (counting input arguments only, ie pronargs) */
+	int			nargs;
+
+	/* If you change anything below here, fix hashing code in funccache.c! */
+
+	/*
+	 * If relevant, the result descriptor for a function returning composite.
+	 */
+	TupleDesc	callResultType;
+
+	/*
+	 * Input argument types, with any polymorphic types resolved to actual
+	 * types.  Only the first nargs entries are valid.
+	 */
+	Oid			argtypes[FUNC_MAX_ARGS];
+} CachedFunctionHashKey;
+
+/*
+ * Representation of a compiled function.  This struct contains just the
+ * fields that funccache.c needs to deal with.  It will typically be
+ * embedded in a larger struct containing function-language-specific data.
+ */
+typedef struct CachedFunction
+{
+	/* back-link to hashtable entry, or NULL if not in hash table */
+	CachedFunctionHashKey *fn_hashkey;
+	/* xmin and ctid of function's pg_proc row; used to detect invalidation */
+	TransactionId fn_xmin;
+	ItemPointerData fn_tid;
+	/* deletion callback */
+	CachedFunctionDeleteCallback dcallback;
+
+	/* this field changes when the function is used: */
+	uint64		use_count;
+} CachedFunction;
+
+extern CachedFunction *cached_function_compile(FunctionCallInfo fcinfo,
+											   CachedFunction *function,
+											   CachedFunctionCompileCallback ccallback,
+											   CachedFunctionDeleteCallback dcallback,
+											   Size cacheEntrySize,
+											   bool includeResultType,
+											   bool forValidator);
+extern void cfunc_resolve_polymorphic_argtypes(int numargs,
+											   Oid *argtypes,
+											   char *argmodes,
+											   Node *call_expr,
+											   bool forValidator,
+											   const char *proname);
+
+#endif							/* FUNCCACHE_H */
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 199cc323a28..07ec5318db7 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -25,7 +25,8 @@
 #include "utils/resowner.h"
 
 
-/* Forward declaration, to avoid including parsenodes.h here */
+/* Forward declarations, to avoid including parsenodes.h here */
+struct Query;
 struct RawStmt;
 
 /* possible values for plan_cache_mode */
@@ -39,18 +40,31 @@ typedef enum
 /* GUC parameter */
 extern PGDLLIMPORT int plan_cache_mode;
 
+/* Optional callback to editorialize on rewritten parse trees */
+typedef void (*PostRewriteHook) (List *querytree_list, void *arg);
+
 #define CACHEDPLANSOURCE_MAGIC		195726186
 #define CACHEDPLAN_MAGIC			953717834
 #define CACHEDEXPR_MAGIC			838275847
 
 /*
  * CachedPlanSource (which might better have been called CachedQuery)
- * represents a SQL query that we expect to use multiple times.  It stores
- * the query source text, the raw parse tree, and the analyzed-and-rewritten
+ * represents a SQL query that we expect to use multiple times.  It stores the
+ * query source text, the source parse tree, and the analyzed-and-rewritten
  * query tree, as well as adjunct data.  Cache invalidation can happen as a
  * result of DDL affecting objects used by the query.  In that case we discard
  * the analyzed-and-rewritten query tree, and rebuild it when next needed.
  *
+ * There are two ways in which the source query can be represented: either
+ * as a raw parse tree, or as an analyzed-but-not-rewritten parse tree.
+ * In the latter case we expect that cache invalidation need not affect
+ * the parse-analysis results, only the rewriting and planning steps.
+ * Only one of raw_parse_tree and analyzed_parse_tree can be non-NULL.
+ * (If both are NULL, the CachedPlanSource represents an empty query.)
+ * Note that query_string is typically just an empty string when the
+ * source query is an analyzed parse tree; also, param_types, num_params,
+ * parserSetup, and parserSetupArg will not be used.
+ *
  * An actual execution plan, represented by CachedPlan, is derived from the
  * CachedPlanSource when we need to execute the query.  The plan could be
  * either generic (usable with any set of plan parameters) or custom (for a
@@ -78,7 +92,7 @@ extern PGDLLIMPORT int plan_cache_mode;
  * though it may be useful if the CachedPlan can be discarded early.)
  *
  * A CachedPlanSource has two associated memory contexts: one that holds the
- * struct itself, the query source text and the raw parse tree, and another
+ * struct itself, the query source text and the source parse tree, and another
  * context that holds the rewritten query tree and associated data.  This
  * allows the query tree to be discarded easily when it is invalidated.
  *
@@ -94,12 +108,15 @@ 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;		/* command tag for query */
 	Oid		   *param_types;	/* array of parameter type OIDs, or NULL */
 	int			num_params;		/* length of param_types array */
 	ParserSetupHook parserSetup;	/* alternative parameter spec method */
 	void	   *parserSetupArg;
+	PostRewriteHook postRewrite;	/* see SetPostRewriteHook */
+	void	   *postRewriteArg;
 	int			cursor_options; /* cursor options used for planning */
 	bool		fixed_result;	/* disallow change in result tupdesc? */
 	TupleDesc	resultDesc;		/* result type; NULL = doesn't return tuples */
@@ -196,6 +213,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);
@@ -208,6 +228,9 @@ extern void CompleteCachedPlan(CachedPlanSource *plansource,
 							   void *parserSetupArg,
 							   int cursor_options,
 							   bool fixed_result);
+extern void SetPostRewriteHook(CachedPlanSource *plansource,
+							   PostRewriteHook postRewrite,
+							   void *postRewriteArg);
 
 extern void SaveCachedPlan(CachedPlanSource *plansource);
 extern void DropCachedPlan(CachedPlanSource *plansource);