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 --- src/include/executor/spi.h | 13 +++++++++++++ src/include/executor/tstoreReceiver.h | 4 +++- 2 files changed, 16 insertions(+), 1 deletion(-) (limited to 'src/include/executor') diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h index 06de20ada5e..896ec0a2ad8 100644 --- a/src/include/executor/spi.h +++ b/src/include/executor/spi.h @@ -90,6 +90,10 @@ extern int SPI_execute_plan(SPIPlanPtr plan, Datum *Values, const char *Nulls, extern int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, ParamListInfo params, bool read_only, long tcount); +extern int SPI_execute_plan_with_receiver(SPIPlanPtr plan, + ParamListInfo params, + bool read_only, long tcount, + DestReceiver *dest); extern int SPI_exec(const char *src, long tcount); extern int SPI_execp(SPIPlanPtr plan, Datum *Values, const char *Nulls, long tcount); @@ -102,6 +106,10 @@ extern int SPI_execute_with_args(const char *src, int nargs, Oid *argtypes, Datum *Values, const char *Nulls, bool read_only, long tcount); +extern int SPI_execute_with_receiver(const char *src, + ParamListInfo params, + bool read_only, long tcount, + DestReceiver *dest); extern SPIPlanPtr SPI_prepare(const char *src, int nargs, Oid *argtypes); extern SPIPlanPtr SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes, int cursorOptions); @@ -150,6 +158,11 @@ extern Portal SPI_cursor_open_with_args(const char *name, bool read_only, int cursorOptions); extern Portal SPI_cursor_open_with_paramlist(const char *name, SPIPlanPtr plan, ParamListInfo params, bool read_only); +extern Portal SPI_cursor_parse_open_with_paramlist(const char *name, + const char *src, + ParamListInfo params, + bool read_only, + int cursorOptions); extern Portal SPI_cursor_find(const char *name); extern void SPI_cursor_fetch(Portal portal, bool forward, long count); extern void SPI_cursor_move(Portal portal, bool forward, long count); diff --git a/src/include/executor/tstoreReceiver.h b/src/include/executor/tstoreReceiver.h index b2390c4a4d0..e9461cf9146 100644 --- a/src/include/executor/tstoreReceiver.h +++ b/src/include/executor/tstoreReceiver.h @@ -24,6 +24,8 @@ extern DestReceiver *CreateTuplestoreDestReceiver(void); extern void SetTuplestoreDestReceiverParams(DestReceiver *self, Tuplestorestate *tStore, MemoryContext tContext, - bool detoast); + bool detoast, + TupleDesc target_tupdesc, + const char *map_failure_msg); #endif /* TSTORE_RECEIVER_H */ -- cgit v1.2.3