v1-0005-Cache-JSON-type-information-in-various-SQL-callab.patch

From a79ab58fc4249de1af6c6805622bc10e8413a71f Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 2 Jul 2026 10:36:13 -0400
Subject: [PATCH v1 5/5] Cache JSON type information in various SQL-callable
 functions.

to_json, json_build_array, json_build_object, to_jsonb,
jsonb_build_array, and jsonb_build_object now cache any needed
JsonTypeCategory and associated FmgrInfo in fn_extra, rather than
recomputing it for every call.

This may significantly improve performance in queries which
execute these functions repeatedly.
---
 src/backend/utils/adt/json.c      |  46 ++++-----
 src/backend/utils/adt/jsonb.c     |  44 ++++-----
 src/backend/utils/adt/jsontypes.c | 153 ++++++++++++++++++++++++++++++
 src/include/utils/jsontypes.h     |  15 +++
 src/tools/pgindent/typedefs.list  |   1 +
 5 files changed, 207 insertions(+), 52 deletions(-)

diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 24b41f0a376..e1ff6d9f94b 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -676,18 +676,26 @@ to_json(PG_FUNCTION_ARGS)
 {
 	Datum		val = PG_GETARG_DATUM(0);
 	Oid			val_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
-	JsonTypeCategory tcategory;
-	FmgrInfo	outflinfo;
+	JsonTypeCache *jcache;
 
 	if (val_type == InvalidOid)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("could not determine input data type")));
 
-	json_categorize_type(val_type, false,
-						 &tcategory, &outflinfo);
+	if (fcinfo->flinfo->fn_extra == NULL)
+	{
+		MemoryContext oldcontext;
 
-	PG_RETURN_DATUM(datum_to_json(val, tcategory, &outflinfo));
+		oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+		jcache = json_build_type_cache(false, 1, &val_type);
+		fcinfo->flinfo->fn_extra = jcache;
+		MemoryContextSwitchTo(oldcontext);
+	}
+	else
+		jcache = fcinfo->flinfo->fn_extra;
+
+	PG_RETURN_DATUM(datum_to_json(val, jcache->categories[0], &jcache->flinfos[0]));
 }
 
 /*
@@ -1272,23 +1280,16 @@ json_build_object(PG_FUNCTION_ARGS)
 {
 	Datum	   *args;
 	bool	   *nulls;
-	Oid		   *types;
 	int			nargs;
 	JsonTypeCategory *categories;
 	FmgrInfo   *outflinfos;
 
-	/* build argument values to build the object */
-	nargs = extract_variadic_args(fcinfo, 0, true,
-								  &args, &types, &nulls);
-
+	nargs = json_extract_variadic_args(false, fcinfo, &args, &nulls,
+									   &categories, &outflinfos);
 	if (nargs < 0)
 		PG_RETURN_NULL();
-
-	categories = palloc_array(JsonTypeCategory, nargs);
-	outflinfos = palloc0_array(FmgrInfo, nargs);
-	for (int i = 0; i < nargs; i++)
-		json_categorize_type(types[i], false,
-							 &categories[i], &outflinfos[i]);
+	if (nargs == 0)
+		PG_RETURN_TEXT_P(cstring_to_text_with_len("{}", 2));
 
 	PG_RETURN_DATUM(json_build_object_worker(nargs, args, nulls,
 											 categories, outflinfos,
@@ -1341,23 +1342,18 @@ json_build_array(PG_FUNCTION_ARGS)
 {
 	Datum	   *args;
 	bool	   *nulls;
-	Oid		   *types;
 	int			nargs;
 	JsonTypeCategory *categories;
 	FmgrInfo   *outflinfos;
 
 	/* build argument values to build the array */
-	nargs = extract_variadic_args(fcinfo, 0, true,
-								  &args, &types, &nulls);
+	nargs = json_extract_variadic_args(false, fcinfo, &args, &nulls,
+									   &categories, &outflinfos);
 
 	if (nargs < 0)
 		PG_RETURN_NULL();
-
-	categories = palloc_array(JsonTypeCategory, nargs);
-	outflinfos = palloc0_array(FmgrInfo, nargs);
-	for (int i = 0; i < nargs; i++)
-		json_categorize_type(types[i], false,
-							 &categories[i], &outflinfos[i]);
+	if (nargs == 0)
+		PG_RETURN_TEXT_P(cstring_to_text_with_len("[]", 2));
 
 	PG_RETURN_DATUM(json_build_array_worker(nargs, args, nulls,
 											categories, outflinfos,
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 56e94acd2a8..6876ef22680 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1059,18 +1059,26 @@ to_jsonb(PG_FUNCTION_ARGS)
 {
 	Datum		val = PG_GETARG_DATUM(0);
 	Oid			val_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
-	JsonTypeCategory tcategory;
-	FmgrInfo	outflinfo;
+	JsonTypeCache *jcache;
 
 	if (val_type == InvalidOid)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("could not determine input data type")));
 
-	json_categorize_type(val_type, true,
-						 &tcategory, &outflinfo);
+	if (fcinfo->flinfo->fn_extra == NULL)
+	{
+		MemoryContext oldcontext;
 
-	PG_RETURN_DATUM(datum_to_jsonb(val, tcategory, &outflinfo));
+		oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+		jcache = json_build_type_cache(true, 1, &val_type);
+		fcinfo->flinfo->fn_extra = jcache;
+		MemoryContextSwitchTo(oldcontext);
+	}
+	else
+		jcache = fcinfo->flinfo->fn_extra;
+
+	PG_RETURN_DATUM(datum_to_jsonb(val, jcache->categories[0], &jcache->flinfos[0]));
 }
 
 /*
@@ -1157,24 +1165,15 @@ jsonb_build_object(PG_FUNCTION_ARGS)
 {
 	Datum	   *args;
 	bool	   *nulls;
-	Oid		   *types;
 	int			nargs;
 	JsonTypeCategory *categories;
 	FmgrInfo   *outflinfos;
 
-	/* build argument values to build the object */
-	nargs = extract_variadic_args(fcinfo, 0, true,
-								  &args, &types, &nulls);
-
+	nargs = json_extract_variadic_args(true, fcinfo, &args, &nulls,
+									   &categories, &outflinfos);
 	if (nargs < 0)
 		PG_RETURN_NULL();
 
-	categories = palloc_array(JsonTypeCategory, nargs);
-	outflinfos = palloc0_array(FmgrInfo, nargs);
-	for (int i = 0; i < nargs; i++)
-		json_categorize_type(types[i], true,
-							 &categories[i], &outflinfos[i]);
-
 	PG_RETURN_DATUM(jsonb_build_object_worker(nargs, args, nulls,
 											  categories, outflinfos,
 											  false, false));
@@ -1230,24 +1229,15 @@ jsonb_build_array(PG_FUNCTION_ARGS)
 {
 	Datum	   *args;
 	bool	   *nulls;
-	Oid		   *types;
 	int			nargs;
 	JsonTypeCategory *categories;
 	FmgrInfo   *outflinfos;
 
-	/* build argument values to build the array */
-	nargs = extract_variadic_args(fcinfo, 0, true,
-								  &args, &types, &nulls);
-
+	nargs = json_extract_variadic_args(true, fcinfo, &args, &nulls,
+									   &categories, &outflinfos);
 	if (nargs < 0)
 		PG_RETURN_NULL();
 
-	categories = palloc_array(JsonTypeCategory, nargs);
-	outflinfos = palloc0_array(FmgrInfo, nargs);
-	for (int i = 0; i < nargs; i++)
-		json_categorize_type(types[i], true,
-							 &categories[i], &outflinfos[i]);
-
 	PG_RETURN_DATUM(jsonb_build_array_worker(nargs, args, nulls,
 											 categories, outflinfos,
 											 false));
diff --git a/src/backend/utils/adt/jsontypes.c b/src/backend/utils/adt/jsontypes.c
index adbaceebc8a..8af1f43d09d 100644
--- a/src/backend/utils/adt/jsontypes.c
+++ b/src/backend/utils/adt/jsontypes.c
@@ -16,8 +16,10 @@
 #include "access/transam.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
+#include "fmgr.h"
 #include "miscadmin.h"
 #include "parser/parse_coerce.h"
+#include "utils/array.h"
 #include "utils/jsontypes.h"
 #include "utils/lsyscache.h"
 #include "utils/typcache.h"
@@ -242,3 +244,154 @@ get_json_cast_for_type(Oid typoid)
 	}
 	return InvalidOid;
 }
+
+/*
+ * Run json_categorize_type() over an array of type OIDs and build a cache
+ * from the results.
+ */
+JsonTypeCache *
+json_build_type_cache(bool is_jsonb, int nargs, Oid *types)
+{
+	JsonTypeCache *jcache = palloc_object(JsonTypeCache);
+
+	jcache->nargs = nargs;
+	jcache->categories = palloc_array(JsonTypeCategory, nargs);
+	jcache->flinfos = palloc0_array(FmgrInfo, nargs);
+
+	for (int i = 0; i < nargs; ++i)
+		json_categorize_type(types[i], is_jsonb, &jcache->categories[i],
+							 &jcache->flinfos[i]);
+
+	return jcache;
+}
+
+/*
+ * Extract all the arguments in a possibly-variadic FunctionCallInfo, producing
+ * arrays of datums and null flags, and also categorize those arguments using
+ * json_categorize_type, caching state in fcinfo->fn_extra to improve performance.
+ *
+ * The return value is the number of arguments that the caller should process, or
+ * -1 if the function was called using the VARIADIC syntax with a NULL array. If
+ * the return value is >0, *args and *nulls are set to palloc'd arrays of the
+ * extracted arguments, and *categories and *outflinfos are set to arrays which
+ * hold the results of json_categorize_type for the corresponding argument position.
+ */
+int
+json_extract_variadic_args(bool is_jsonb,
+						   FunctionCallInfo fcinfo,
+						   Datum **args, bool **nulls,
+						   JsonTypeCategory **categories,
+						   FmgrInfo **outflinfos)
+{
+	int		nargs;
+
+	if (!get_fn_expr_variadic(fcinfo->flinfo))
+	{
+		JsonTypeCache *jcache;
+
+		nargs = PG_NARGS();
+		if (nargs == 0)
+			return 0;
+
+		/* Build a cache on first call, to speed up future calls. */
+		if (fcinfo->flinfo->fn_extra == NULL)
+		{
+			MemoryContext oldcontext;
+			Oid		   *types;
+
+			types = palloc_array(Oid, nargs);
+			for (int i = 0; i < nargs; ++i)
+				types[i] = get_fn_expr_argtype(fcinfo->flinfo, i);
+
+			oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+			jcache = json_build_type_cache(is_jsonb, nargs, types);
+			fcinfo->flinfo->fn_extra = jcache;
+			MemoryContextSwitchTo(oldcontext);
+		}
+		else
+			jcache = fcinfo->flinfo->fn_extra;
+
+		/* Get category and flinfo arrays from cache. */
+		*categories = jcache->categories;
+		*outflinfos = jcache->flinfos;
+
+		/* Extract arguments and isnull flags. */
+		*args = palloc_array(Datum, nargs);
+		*nulls = palloc_array(bool, nargs);
+		for (int i = 0; i < nargs; ++i)
+		{
+			if (PG_ARGISNULL(i))
+			{
+				(*nulls)[i] = true;
+				(*args)[i] = (Datum) 0;
+			}
+			else
+			{
+				(*nulls)[i] = false;
+				(*args)[i] = PG_GETARG_DATUM(i);
+			}
+		}
+	}
+	else if (PG_ARGISNULL(0))
+	{
+		/* Special case: VARIADIC NULL::sometype[] */
+		nargs = -1;
+	}
+	else
+	{
+		ArrayType  *variadic_array = PG_GETARG_ARRAYTYPE_P(0);
+		Oid			variadic_element_type = ARR_ELEMTYPE(variadic_array);
+		bool		typbyval;
+		char		typalign;
+		int16		typlen;
+		JsonTypeCache *jcache;
+
+		/* Deconstruct the array. */
+		Assert(PG_NARGS() == 1);
+		get_typlenbyvalalign(variadic_element_type, &typlen, &typbyval, &typalign);
+		deconstruct_array(variadic_array, variadic_element_type, typlen, typbyval,
+						  typalign, args, nulls, &nargs);
+		if (nargs == 0)
+			return 0;
+
+		/* Build cache for the array element type on first pass. */
+		if (fcinfo->flinfo->fn_extra == NULL)
+		{
+			MemoryContext oldcontext;
+
+			oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt);
+			jcache = json_build_type_cache(is_jsonb, 1, &variadic_element_type);
+			fcinfo->flinfo->fn_extra = jcache;
+			MemoryContextSwitchTo(oldcontext);
+		}
+		else
+			jcache = fcinfo->flinfo->fn_extra;
+
+		/*
+		 * When a VARIADIC array is passed, there's only one argument data type,
+		 * but the caller expects category and flinfo arrays of the same length as
+		 * the number of arguments, and the number of arguments can vary on every
+		 * call. To gain as much performance as we can without complicating code
+		 * elsewhere, save the single data type in the cache and then build out
+		 * arrays of the requisite length by copying.
+		 */
+		*categories = palloc_array(JsonTypeCategory, nargs);
+		*outflinfos = palloc0_array(FmgrInfo, nargs);
+		if (OidIsValid(jcache->flinfos[0].fn_oid))
+		{
+			for (int i = 0; i < nargs; ++i)
+			{
+				(*categories)[i] = jcache->categories[0];
+				fmgr_info_copy(&(*outflinfos)[i], &jcache->flinfos[0],
+							   CurrentMemoryContext);
+			}
+		}
+		else
+		{
+			for (int i = 0; i < nargs; ++i)
+				(*categories)[i] = jcache->categories[0];
+		}
+	}
+
+	return nargs;
+}
diff --git a/src/include/utils/jsontypes.h b/src/include/utils/jsontypes.h
index 4fe3911f69c..0372bf96418 100644
--- a/src/include/utils/jsontypes.h
+++ b/src/include/utils/jsontypes.h
@@ -33,9 +33,24 @@ typedef enum
 	JSONTYPE_OTHER,				/* all else */
 } JsonTypeCategory;
 
+typedef struct
+{
+	int			nargs;
+	JsonTypeCategory *categories;
+	FmgrInfo   *flinfos;
+} JsonTypeCache;
+
 extern void json_categorize_type(Oid typoid, bool is_jsonb,
 								 JsonTypeCategory *tcategory,
 								 FmgrInfo *outflinfo);
 extern void json_check_mutability(Oid typoid, bool *has_mutable);
+extern JsonTypeCache *json_build_type_cache(bool is_jsonb,
+											int nargs,
+											Oid *types);
+extern int json_extract_variadic_args(bool is_jsonb,
+									  FunctionCallInfo fcinfo,
+									  Datum **args, bool **nulls,
+									  JsonTypeCategory **categories,
+									  FmgrInfo **outflinfos);
 
 #endif							/* JSONTYPES_H */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3a2720fb5f9..ca9726c4931 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1509,6 +1509,7 @@ JsonTablePlanState
 JsonTableSiblingJoin
 JsonTokenType
 JsonTransformStringValuesAction
+JsonTypeCache
 JsonTypeCategory
 JsonUniqueBuilderState
 JsonUniqueCheckState
-- 
2.50.1 (Apple Git-155)