On 4/8/2025 06:23, Michael Paquier wrote:
On Fri, Aug 01, 2025 at 03:07:08PM +0200, Andrei Lepikhov wrote:
I considered the worker_spi.c module, which demonstrates various SPI usage
patterns. It might be more beneficial to use this instead of creating
another test module, isn't it?
worker_spi is a playground for bgworker tests, so I'm not eager to
make it more complex for the case of cached planned. SQL tests have
more benefits here, IMO, and they should be more efficient and more
predictible. worker_spi can only work with TAP.
I began to implement the idea, described above and got stuck on the
issue that we can't explain an SPI plan. It is possible to invent an
'auto_explain' approach (as a TAP test, of course), but it looks wrong.
As an alternative, it is feasible to add to the SPI interface an
SPI_explain_plan routine using the analogy of ExplainExecuteQuery. I
recall hearing some requests for this kind of feature before. Is this a
commitable way?
See the patch attached. Do I understand correctly the 'SPI wrapper'
approach?
--
regards, Andrei Lepikhov
From 0af47577ed00aae884b2cc11d10b1f058a896c2c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepi...@gmail.com>
Date: Fri, 22 Aug 2025 13:39:04 +0200
Subject: [PATCH v0] Basic routines for testing saved SPI plans
---
src/test/regress/expected/misc_functions.out | 12 ++
src/test/regress/regress.c | 199 +++++++++++++++++++
src/test/regress/sql/misc_functions.sql | 15 ++
src/test/regress/sql/plancache.sql | 19 ++
4 files changed, 245 insertions(+)
diff --git a/src/test/regress/expected/misc_functions.out
b/src/test/regress/expected/misc_functions.out
index c3b2b9d8603..0ce8b37a769 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -914,6 +914,18 @@ SELECT test_relpath();
(1 row)
+CREATE FUNCTION prepare_spi_plan(text, text, VARIADIC text[])
+RETURNS bigint
+ AS :'regresslib', 'prepare_spi_plan'
+ LANGUAGE C;
+CREATE FUNCTION execute_spi_plan(bigint, text, "any")
+RETURNS SETOF record
+ AS :'regresslib', 'execute_spi_plan'
+ LANGUAGE C STRICT;
+CREATE FUNCTION free_spi_plan(bigint)
+RETURNS void
+ AS :'regresslib', 'free_spi_plan'
+ LANGUAGE C STRICT;
-- pg_replication_origin.roname limit
SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
ERROR: replication origin name is too long
diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c
index 465ac148ac9..f19d544e601 100644
--- a/src/test/regress/regress.c
+++ b/src/test/regress/regress.c
@@ -42,9 +42,11 @@
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/geo_decls.h"
+#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/typcache.h"
+#include "utils/varlena.h"
#define EXPECT_TRUE(expr) \
do { \
@@ -1028,3 +1030,200 @@ test_relpath(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+static int
+setup_cursor_options(const char *options_string)
+{
+ List *optionLst = NIL;
+ ListCell *lc;
+ int cursorOptions = 0;
+
+ if (!SplitIdentifierString(pstrdup(options_string), ',', &optionLst))
+ /* syntax error in option list */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("parameter \"%s\" must be a list of
parameters",
+ "cursor_options")));
+
+ foreach(lc, optionLst)
+ {
+ const char *option = (const char *) lfirst(lc);
+
+ if (pg_strcasecmp(option, "fast_plan") == 0)
+ cursorOptions |= CURSOR_OPT_FAST_PLAN;
+ else if (pg_strcasecmp(option, "generic_plan") == 0)
+ cursorOptions |= CURSOR_OPT_GENERIC_PLAN;
+ else if (pg_strcasecmp(option, "custom_plan") == 0)
+ cursorOptions |= CURSOR_OPT_CUSTOM_PLAN;
+ else if (pg_strcasecmp(option, "parallel_ok") == 0)
+ cursorOptions |= CURSOR_OPT_PARALLEL_OK;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unknown cursor option %s", option)));
+ }
+
+ list_free(optionLst);
+ return cursorOptions;
+}
+
+ /*
+ * Prepare a plan cache entry for incoming parameterised query with specific
+ * set of cursor options.
+ *
+ * cursor_options string may contain any combination of the following values:
+ * 'fast_plan', 'generic_plan', 'custom_plan', or 'parallel_ok'.
+ *
+ * Returns internal pointer to saved SPI plan.
+ */
+PG_FUNCTION_INFO_V1(prepare_spi_plan);
+Datum
+prepare_spi_plan(PG_FUNCTION_ARGS)
+{
+ const char *query_text;
+ int cursorOptions = 0;
+ ArrayType *arr;
+ Datum *elements = NULL;
+ bool *nulls = NULL;
+ Oid element_type = InvalidOid;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int nitems;
+ int i;
+ Oid *argtypes;
+ SPIPlanPtr result;
+
+ Assert(PG_NARGS() == 3 && get_fn_expr_variadic(fcinfo->flinfo));
+
+ /* Check supplied arguments */
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("query text cannot be null")));
+ query_text = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ if (PG_ARGISNULL(2))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("list of parameters canot be null")));
+
+ if (!PG_ARGISNULL(1))
+ {
+ char *options = text_to_cstring(PG_GETARG_TEXT_PP(1));
+
+ cursorOptions = setup_cursor_options(options);
+ }
+
+ /* Prepare the Oid array of the query parameters */
+
+ arr = PG_GETARG_ARRAYTYPE_P(2);
+ element_type = ARR_ELEMTYPE(arr);
+ get_typlenbyvalalign(element_type, &elmlen, &elmbyval, &elmalign);
+
+ /* Extract all array elements */
+ deconstruct_array(arr, element_type, elmlen, elmbyval, elmalign,
+ &elements, &nulls, &nitems);
+
+ argtypes = palloc(nitems * sizeof(Oid));
+ for (i = 0; i < nitems; i++)
+ {
+ const char *typname;
+
+ if (nulls[i])
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("type name cannot be NULL")));
+
+ typname = TextDatumGetCString(elements[i]);
+ argtypes[i] = DirectFunctionCall1Coll(regtypein,
+
InvalidOid,
+
CStringGetDatum(typname));
+ }
+
+ /* Create plancache entry and save the plan */
+ SPI_connect();
+ result = SPI_prepare_cursor(query_text, nitems, argtypes,
cursorOptions);
+ result = SPI_saveplan(result);
+ SPI_finish();
+ PG_RETURN_POINTER(result);
+}
+
+PG_FUNCTION_INFO_V1(execute_spi_plan);
+Datum
+execute_spi_plan(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ SPIPlanPtr plan = (SPIPlanPtr) PG_GETARG_POINTER(0);
+ int nargs = PG_NARGS();
+ Datum *values;
+ char *Nulls;
+ int spirc;
+ int i;
+
+ if (!SPI_plan_is_valid(plan))
+ PG_RETURN_NULL();
+
+ Assert(!get_fn_expr_variadic(fcinfo->flinfo));
+
+ /*
+ * Caller wants to change cursor options. Update it for each statement.
+ */
+ if (!PG_ARGISNULL(1))
+ {
+ char *options = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ List *plansources = SPI_plan_get_plan_sources(plan);
+ int cursorOptions = 0;
+ ListCell *lc;
+
+ cursorOptions = setup_cursor_options(options);
+
+ foreach(lc, plansources)
+ {
+ CachedPlanSource *src = lfirst(lc);
+
+ src->cursor_options = cursorOptions;
+ }
+ }
+
+ values = palloc((nargs - 2) * sizeof(Datum));
+ Nulls = palloc((nargs - 2) * sizeof(char *));
+ for (i = 2; i < nargs; i++)
+ {
+ if (!PG_ARGISNULL(i))
+ {
+ Nulls[i - 2] = ' ';
+ values[i - 2] = PG_GETARG_DATUM(i);
+ }
+ else
+ Nulls[i - 2] = 'n';
+ }
+
+ SPI_connect();
+ spirc = SPI_execute_plan(plan, values, Nulls, false, 0);
+ if (spirc <= 0)
+ elog(ERROR, "failed to execute the SPI plan %d", spirc);
+
+ rsinfo->expectedDesc = SPI_tuptable->tupdesc;
+ InitMaterializedSRF(fcinfo, MAT_SRF_USE_EXPECTED_DESC | MAT_SRF_BLESS);
+
+ for (i = 0; i < SPI_processed; i++)
+ {
+ tuplestore_puttuple(rsinfo->setResult, SPI_tuptable->vals[i]);
+ }
+
+ SPI_finish();
+
+ return (Datum) 0;
+}
+
+PG_FUNCTION_INFO_V1(free_spi_plan);
+Datum
+free_spi_plan(PG_FUNCTION_ARGS)
+{
+ SPIPlanPtr plan = (SPIPlanPtr) PG_GETARG_POINTER(0);
+
+ SPI_connect();
+ SPI_freeplan(plan);
+ SPI_finish();
+ PG_RETURN_VOID();
+}
diff --git a/src/test/regress/sql/misc_functions.sql
b/src/test/regress/sql/misc_functions.sql
index 23792c4132a..8b2c69c51c4 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -412,5 +412,20 @@ CREATE FUNCTION test_relpath()
LANGUAGE C;
SELECT test_relpath();
+CREATE FUNCTION prepare_spi_plan(text, text, VARIADIC text[])
+RETURNS bigint
+ AS :'regresslib', 'prepare_spi_plan'
+ LANGUAGE C;
+
+CREATE FUNCTION execute_spi_plan(bigint, text, "any")
+RETURNS SETOF record
+ AS :'regresslib', 'execute_spi_plan'
+ LANGUAGE C STRICT;
+
+CREATE FUNCTION free_spi_plan(bigint)
+RETURNS void
+ AS :'regresslib', 'free_spi_plan'
+ LANGUAGE C STRICT;
+
-- pg_replication_origin.roname limit
SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
diff --git a/src/test/regress/sql/plancache.sql
b/src/test/regress/sql/plancache.sql
index 4b2f11dcc64..7b52a5ac343 100644
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -223,3 +223,22 @@ select name, generic_plans, custom_plans from
pg_prepared_statements
where name = 'test_mode_pp';
drop table test_mode;
+
+-- Check the interference between plan_cache_mode and cursor_options
+-- EXPLAIN (COSTS OFF, GENERIC_PLAN)
+
+SELECT prepare_spi_plan(NULL, NULL, NULL); -- ERROR
+SELECT prepare_spi_plan(
+ 'EXPLAIN (COSTS OFF) SELECT * FROM pcachetest WHERE q1 = $1',
+ NULL, 'integer') AS p1 \gset
+SELECT prepare_spi_plan(
+ 'SELECT * FROM pcachetest WHERE q1 = $1 OR q1 = $2',
+ NULL, 'integer', NULL); --ERROR
+SELECT prepare_spi_plan(
+ 'SELECT * FROM pcachetest WHERE q1 = $1 OR q1 = 3',
+ NULL, 'integer', 'numeric') AS p2 \gset
+
+SELECT execute_spi_plan(:p1, 'generic_plan', 42);
+
+SELECT free_spi_plan(:p1);
+SELECT free_spi_plan(:p2);
--
2.51.0
