From 2f48ede080f42b97b594fb14102c82ca1001b80c Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Fri, 12 Jun 2020 12:14:32 -0400
Subject: Avoid using a cursor in plpgsql's RETURN QUERY statement.
plpgsql has always executed the query given in a RETURN QUERY command
by opening it as a cursor and then fetching a few rows at a time,
which it turns around and dumps into the function's result tuplestore.
The point of this was to keep from blowing out memory with an oversized
SPITupleTable result (note that while a tuplestore can spill tuples
to disk, SPITupleTable cannot). However, it's rather inefficient, both
because of extra data copying and because of executor entry/exit
overhead. In recent versions, a new performance problem has emerged:
use of a cursor prevents use of a parallel plan for the executed query.
We can improve matters by skipping use of a cursor and having the
executor push result tuples directly into the function's result
tuplestore. However, a moderate amount of new infrastructure is needed
to make that idea work:
* We can use the existing tstoreReceiver.c DestReceiver code to funnel
executor output to the tuplestore, but it has to be extended to support
plpgsql's requirement for possibly applying a tuple conversion map.
* SPI needs to be extended to allow use of a caller-supplied
DestReceiver instead of its usual receiver that puts tuples into
a SPITupleTable. Two new API calls are needed to handle both the
RETURN QUERY and RETURN QUERY EXECUTE cases.
I also felt that I didn't want these new API calls to use the legacy
method of specifying query parameter values with "char" null flags
(the old ' '/'n' convention); rather they should accept ParamListInfo
objects containing the parameter type and value info. This required
a bit of additional new infrastructure since we didn't yet have any
parse analysis callback that would interpret $N parameter symbols
according to type data supplied in a ParamListInfo. There seems to be
no harm in letting makeParamList install that callback by default,
rather than leaving a new ParamListInfo's parserSetup hook as NULL.
(Indeed, as of HEAD, I couldn't find anyplace that was using the
parserSetup field at all; plpgsql was using parserSetupArg for its
own purposes, but parserSetup seemed to be write-only.)
We can actually get plpgsql out of the business of using legacy null
flags altogether, and using ParamListInfo instead of its ad-hoc
PreparedParamsData structure; but this requires inventing one more
SPI API call that can replace SPI_cursor_open_with_args. That seems
worth doing, though.
SPI_execute_with_args and SPI_cursor_open_with_args are now unused
anywhere in the core PG distribution. Perhaps someday we could
deprecate/remove them. But cleaning up the crufty bits of the SPI
API is a task for a different patch.
Per bug #16040 from Jeremy Smith. This is unfortunately too invasive to
consider back-patching. Patch by me; thanks to Hamid Akhtar for review.
Discussion: https://postgr.es/m/16040-eaacad11fecfb198@postgresql.org
---
doc/src/sgml/spi.sgml | 349 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 349 insertions(+)
(limited to 'doc/src/sgml')
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
index 3199141b52f..7752de0a4d8 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -785,6 +785,133 @@ int SPI_execute_with_args(const char *command,
+
+ SPI_execute_with_receiver
+
+
+ SPI_execute_with_receiver
+ 3
+
+
+
+ SPI_execute_with_receiver
+ execute a command with out-of-line parameters
+
+
+
+
+ int SPI_execute_with_receiver(const char *command,
+ ParamListInfo params,
+ bool read_only,
+ long count,
+ DestReceiver *dest)
+
+
+
+
+ Description
+
+
+ SPI_execute_with_receiver executes a command that might
+ include references to externally supplied parameters. The command text
+ refers to a parameter as $n,
+ and the params object provides values and type
+ information for each such symbol.
+ read_only and count have
+ the same interpretation as in SPI_execute.
+
+
+
+ If dest is not NULL, then result tuples are passed
+ to that object as they are generated by the executor, instead of being
+ accumulated in SPI_tuptable. Using a
+ caller-supplied DestReceiver object is particularly
+ helpful for queries that might generate many tuples, since the data can
+ be processed on-the-fly instead of being accumulated in memory.
+
+
+
+ The params object should normally mark each
+ parameter with the PARAM_FLAG_CONST flag, since
+ a one-shot plan is always used for the query.
+
+
+
+
+ Arguments
+
+
+
+ const char * command
+
+
+ command string
+
+
+
+
+
+ ParamListInfo params
+
+
+ data structure containing parameter types and values; NULL if none
+
+
+
+
+
+ bool read_only
+
+ true for read-only execution
+
+
+
+
+ long count
+
+
+ maximum number of rows to return,
+ or 0 for no limit
+
+
+
+
+
+ DestReceiver * dest
+
+
+ DestReceiver object that will receive any tuples
+ emitted by the query; if NULL, tuples are returned
+ in SPI_tuptable
+
+
+
+
+
+
+
+ Return Value
+
+
+ The return value is the same as for SPI_execute.
+
+
+
+ When dest is NULL,
+ SPI_processed and
+ SPI_tuptable are set as in
+ SPI_execute.
+ When dest is not NULL,
+ SPI_processed is set to zero and
+ SPI_tuptable is set to NULL. If a tuple count
+ is required, the caller's DestReceiver object must
+ calculate it.
+
+
+
+
+
+
SPI_prepare
@@ -1564,6 +1691,120 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan,
+
+ SPI_execute_plan_with_receiver
+
+
+ SPI_execute_plan_with_receiver
+ 3
+
+
+
+ SPI_execute_plan_with_receiver
+ execute a statement prepared by SPI_prepare
+
+
+
+
+int SPI_execute_plan_with_receiver(SPIPlanPtr plan,
+ ParamListInfo params,
+ bool read_only,
+ long count,
+ DestReceiver *dest)
+
+
+
+
+ Description
+
+
+ SPI_execute_plan_with_receiver executes a statement
+ prepared by SPI_prepare. This function is
+ equivalent to SPI_execute_plan_with_paramlist
+ except that, instead of always accumulating the result tuples into a
+ SPI_tuptable structure, tuples can be passed to a
+ caller-supplied DestReceiver object as they are
+ generated by the executor. This is particularly helpful for queries
+ that might generate many tuples, since the data can be processed
+ on-the-fly instead of being accumulated in memory.
+
+
+
+
+ Arguments
+
+
+
+ SPIPlanPtr plan
+
+
+ prepared statement (returned by SPI_prepare)
+
+
+
+
+
+ ParamListInfo params
+
+
+ data structure containing parameter types and values; NULL if none
+
+
+
+
+
+ bool read_only
+
+ true for read-only execution
+
+
+
+
+ long count
+
+
+ maximum number of rows to return,
+ or 0 for no limit
+
+
+
+
+
+ DestReceiver * dest
+
+
+ DestReceiver object that will receive any tuples
+ emitted by the query; if NULL, this function is exactly equivalent to
+ SPI_execute_plan_with_paramlist
+
+
+
+
+
+
+
+ Return Value
+
+
+ The return value is the same as for SPI_execute_plan.
+
+
+
+ When dest is NULL,
+ SPI_processed and
+ SPI_tuptable are set as in
+ SPI_execute_plan.
+ When dest is not NULL,
+ SPI_processed is set to zero and
+ SPI_tuptable is set to NULL. If a tuple count
+ is required, the caller's DestReceiver object must
+ calculate it.
+
+
+
+
+
+
SPI_execp
@@ -2041,6 +2282,114 @@ Portal SPI_cursor_open_with_paramlist(const char *name,
+
+ SPI_cursor_parse_open_with_paramlist
+
+
+ SPI_cursor_parse_open_with_paramlist
+ 3
+
+
+
+ SPI_cursor_parse_open_with_paramlist
+ set up a cursor using a query and parameters
+
+
+
+
+Portal SPI_cursor_parse_open_with_paramlist(const char *name,
+ const char *command,
+ ParamListInfo params,
+ bool read_only,
+ int cursorOptions)
+
+
+
+
+ Description
+
+
+ SPI_cursor_parse_open_with_paramlist sets up a cursor
+ (internally, a portal) that will execute the specified query. This
+ function is equivalent to SPI_cursor_open_with_args
+ except that any parameters referenced by the query are provided by
+ a ParamListInfo object, rather than in ad-hoc arrays.
+
+
+
+ The params object should normally mark each
+ parameter with the PARAM_FLAG_CONST flag, since
+ a one-shot plan is always used for the query.
+
+
+
+ The passed-in parameter data will be copied into the cursor's portal, so it
+ can be freed while the cursor still exists.
+
+
+
+
+ Arguments
+
+
+
+ const char * name
+
+
+ name for portal, or NULL to let the system
+ select a name
+
+
+
+
+
+ const char * command
+
+
+ command string
+
+
+
+
+
+ ParamListInfo params
+
+
+ data structure containing parameter types and values; NULL if none
+
+
+
+
+
+ bool read_only
+
+ true for read-only execution
+
+
+
+
+ int cursorOptions
+
+
+ integer bit mask of cursor options; zero produces default behavior
+
+
+
+
+
+
+
+ Return Value
+
+
+ Pointer to portal containing the cursor. Note there is no error
+ return convention; any error will be reported via elog.
+
+
+
+
+
+
SPI_cursor_find
--
cgit v1.2.3