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

1 files changed, 134 insertions, 0 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 */