Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jiRrt-0004oy-KU for pgsql-bugs@arkaria.postgresql.org; Tue, 09 Jun 2020 00:11:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jiRrs-0006yH-Ds for pgsql-bugs@arkaria.postgresql.org; Tue, 09 Jun 2020 00:11:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jiRrs-0006yA-3L for pgsql-bugs@lists.postgresql.org; Tue, 09 Jun 2020 00:11:52 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jiRro-0004ga-4Y for pgsql-bugs@lists.postgresql.org; Tue, 09 Jun 2020 00:11:51 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0590BhDK2135214; Mon, 8 Jun 2020 20:11:44 -0400 From: Tom Lane To: jeremy@musicsmith.net cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan In-reply-to: <1741.1584847383@sss.pgh.pa.us> References: <16040-eaacad11fecfb198@postgresql.org> <1741.1584847383@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sat, 21 Mar 2020 23:23:03 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2135186.1591661454.0@sss.pgh.pa.us> Date: Mon, 08 Jun 2020 20:11:43 -0400 Message-ID: <2135213.1591661503@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2135186.1591661454.1@sss.pgh.pa.us> I wrote: > ... attached is a draft patch to enable that. By getting rid of the > intermediate SPITupleTable, this should improve the performance of > RETURN QUERY somewhat even without considering the possibility of > parallelizing the source query. I've not tried to measure that though. > I've also not looked for other places that could use this new > infrastructure, but there may well be some. > One thing I'm not totally pleased about with this is adding another > SPI interface routine using the old parameter-values API (that is, > null flags as char ' '/'n'). That was the path of least resistance > given the other moving parts in pl_exec.c and spi.c, but maybe we > should try to modernize that before we set it in stone. Here's a revised patch that does the additional legwork needed to use ParamListInfo throughout the newly-added code. I was able to get pl_exec.c out of the business of using old-style null flags entirely, which seems like a nice improvement. > Another thing standing between this patch and committability is suitable > additions to the SPI documentation. But I saw no value in writing that > before the previous point is settled. Took care of that too. I looked around for other places that could use this infrastructure. It turns out that most places that are fetching via SPITupleTables don't really have much of an issue, because they are only expecting to get one or so tuples anyway. There are a few where it might be worth changing, but it's hard to get really excited because they all have other constraints on the max amount of data. As an example, the various table-to-xml thingies in utils/adt/xml.c could be converted, but they're still funneling their output into an XML string. As long as that has a hard limit at 1GB, it's not very realistic to expect that you can shove huge tables into it. A different sort of cleanup we could undertake is to deprecate and eventually remove some of the SPI API functions. As of this patch, for example, SPI_cursor_open_with_args and SPI_execute_with_args are unused anywhere in our code. But since we document them, it's hard to guess whether any external code is relying on them. I suppose deprecation would be a multi-year project in any case. I think this is committable now. Any objections? regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="plpgsql-return-query-results-directly-2.patch"; charset="us-ascii" Content-ID: <2135186.1591661454.2@sss.pgh.pa.us> Content-Description: plpgsql-return-query-results-directly-2.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 3b2a614929..5d97ea6f25 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -785,6 +785,127 @@ int SPI_execute_with_args(const char *com= mand, = = + + 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 tex= t + refers to a parameter as $n, + and the dest 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 pas= sed + to that object as they are generated by the executor, instead of being + accumulated in SPI_tuptable. + + + + 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<= /term> + + + command string + + + + + + ParamListInfo params<= /term> + + + 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 +1685,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 stateme= nt + 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<= /term> + + + 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 +2276,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 *comman= d, + ParamListInfo para= ms, + bool read_only, + int cursorOptions<= /parameter>) + + + + + Description + + + SPI_cursor_parse_open_with_paramlist sets up a cu= rsor + (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 array= s. + + + + 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<= /term> + + + command string + + + + + + ParamListInfo params<= /term> + + + 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 = diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/port= alcmds.c index 6a2c233615..e4b7483e32 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -383,7 +383,9 @@ PersistHoldablePortal(Portal portal) SetTuplestoreDestReceiverParams(queryDesc->dest, portal->holdStore, portal->holdContext, - true); + true, + NULL, + NULL); = /* Fetch the result set into the tuplestore */ ExecutorRun(queryDesc, ForwardScanDirection, 0L, false); diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index b108168821..055ebb77ae 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -60,7 +60,8 @@ static void _SPI_prepare_oneshot_plan(const char *src, S= PIPlanPtr plan); = static int _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, Snapshot snapshot, Snapshot crosscheck_snapshot, - bool read_only, bool fire_triggers, uint64 tcount); + bool read_only, bool fire_triggers, uint64 tcount, + DestReceiver *caller_dest); = static ParamListInfo _SPI_convert_params(int nargs, Oid *argtypes, Datum *Values, const char *Nulls); @@ -513,7 +514,7 @@ SPI_execute(const char *src, bool read_only, long tcou= nt) = res =3D _SPI_execute_plan(&plan, NULL, InvalidSnapshot, InvalidSnapshot, - read_only, true, tcount); + read_only, true, tcount, NULL); = _SPI_end_call(true); return res; @@ -547,7 +548,7 @@ SPI_execute_plan(SPIPlanPtr plan, Datum *Values, const= char *Nulls, _SPI_convert_params(plan->nargs, plan->argtypes, Values, Nulls), InvalidSnapshot, InvalidSnapshot, - read_only, true, tcount); + read_only, true, tcount, NULL); = _SPI_end_call(true); return res; @@ -576,7 +577,36 @@ SPI_execute_plan_with_paramlist(SPIPlanPtr plan, Para= mListInfo params, = res =3D _SPI_execute_plan(plan, params, InvalidSnapshot, InvalidSnapshot, - read_only, true, tcount); + read_only, true, tcount, NULL); + + _SPI_end_call(true); + return res; +} + +/* + * Execute a previously prepared plan. If dest isn't NULL, we send resul= t + * tuples to the caller-supplied DestReceiver rather than through the usu= al + * SPI output arrangements. If dest is NULL this is equivalent to + * SPI_execute_plan_with_paramlist. + */ +int +SPI_execute_plan_with_receiver(SPIPlanPtr plan, + ParamListInfo params, + bool read_only, long tcount, + DestReceiver *dest) +{ + int res; + + if (plan =3D=3D NULL || plan->magic !=3D _SPI_PLAN_MAGIC || tcount < 0) + return SPI_ERROR_ARGUMENT; + + res =3D _SPI_begin_call(true); + if (res < 0) + return res; + + res =3D _SPI_execute_plan(plan, params, + InvalidSnapshot, InvalidSnapshot, + read_only, true, tcount, dest); = _SPI_end_call(true); return res; @@ -617,7 +647,7 @@ SPI_execute_snapshot(SPIPlanPtr plan, _SPI_convert_params(plan->nargs, plan->argtypes, Values, Nulls), snapshot, crosscheck_snapshot, - read_only, fire_triggers, tcount); + read_only, fire_triggers, tcount, NULL); = _SPI_end_call(true); return res; @@ -664,7 +694,50 @@ SPI_execute_with_args(const char *src, = res =3D _SPI_execute_plan(&plan, paramLI, InvalidSnapshot, InvalidSnapshot, - read_only, true, tcount); + read_only, true, tcount, NULL); + + _SPI_end_call(true); + return res; +} + +/* + * SPI_execute_with_receiver -- plan and execute a query with arguments + * + * This is the same as SPI_execute_with_args except that parameters are + * supplied through a ParamListInfo, and (if dest isn't NULL) we send + * result tuples to the caller-supplied DestReceiver rather than through + * the usual SPI output arrangements. + */ +int +SPI_execute_with_receiver(const char *src, + ParamListInfo params, + bool read_only, long tcount, + DestReceiver *dest) +{ + int res; + _SPI_plan plan; + + if (src =3D=3D NULL || tcount < 0) + return SPI_ERROR_ARGUMENT; + + res =3D _SPI_begin_call(true); + if (res < 0) + return res; + + memset(&plan, 0, sizeof(_SPI_plan)); + plan.magic =3D _SPI_PLAN_MAGIC; + plan.cursor_options =3D CURSOR_OPT_PARALLEL_OK; + if (params) + { + plan.parserSetup =3D params->parserSetup; + plan.parserSetupArg =3D params->parserSetupArg; + } + + _SPI_prepare_oneshot_plan(src, &plan); + + res =3D _SPI_execute_plan(&plan, params, + InvalidSnapshot, InvalidSnapshot, + read_only, true, tcount, dest); = _SPI_end_call(true); return res; @@ -1303,6 +1376,49 @@ SPI_cursor_open_with_paramlist(const char *name, SP= IPlanPtr plan, return SPI_cursor_open_internal(name, plan, params, read_only); } = +/* + * SPI_cursor_parse_open_with_paramlist() + * + * Same as SPI_cursor_open_with_args except that parameters (if any) are = passed + * as a ParamListInfo, which supports dynamic parameter set determination + */ +Portal +SPI_cursor_parse_open_with_paramlist(const char *name, + const char *src, + ParamListInfo params, + bool read_only, int cursorOptions) +{ + Portal result; + _SPI_plan plan; + + if (src =3D=3D NULL) + elog(ERROR, "SPI_cursor_parse_open_with_paramlist called with invalid a= rguments"); + + SPI_result =3D _SPI_begin_call(true); + if (SPI_result < 0) + elog(ERROR, "SPI_cursor_parse_open_with_paramlist called while not conn= ected"); + + memset(&plan, 0, sizeof(_SPI_plan)); + plan.magic =3D _SPI_PLAN_MAGIC; + plan.cursor_options =3D cursorOptions; + if (params) + { + plan.parserSetup =3D params->parserSetup; + plan.parserSetupArg =3D params->parserSetupArg; + } + + _SPI_prepare_plan(src, &plan); + + /* We needn't copy the plan; SPI_cursor_open_internal will do so */ + + result =3D SPI_cursor_open_internal(name, &plan, params, read_only); + + /* And clean up */ + _SPI_end_call(true); + + return result; +} + = /* * SPI_cursor_open_internal() @@ -2090,11 +2206,13 @@ _SPI_prepare_oneshot_plan(const char *src, SPIPlan= Ptr plan) * fire_triggers: true to fire AFTER triggers at end of query (normal cas= e); * false means any AFTER triggers are postponed to end of outer query * tcount: execution tuple-count limit, or 0 for none + * caller_dest: DestReceiver to receive output, or NULL for normal SPI ou= tput */ static int _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, Snapshot snapshot, Snapshot crosscheck_snapshot, - bool read_only, bool fire_triggers, uint64 tcount) + bool read_only, bool fire_triggers, uint64 tcount, + DestReceiver *caller_dest) { int my_res =3D 0; uint64 my_processed =3D 0; @@ -2228,6 +2346,12 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo pa= ramLI, bool canSetTag =3D stmt->canSetTag; DestReceiver *dest; = + /* + * Reset output state. (Note that if a non-SPI receiver is used, + * _SPI_current->processed will stay zero, and that's what we'll + * report to the caller. It's the receiver's job to count tuples + * in that case.) + */ _SPI_current->processed =3D 0; _SPI_current->tuptable =3D NULL; = @@ -2267,7 +2391,16 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo pa= ramLI, UpdateActiveSnapshotCommandId(); } = - dest =3D CreateDestReceiver(canSetTag ? DestSPI : DestNone); + /* + * Select appropriate tuple receiver. Output from non-canSetTag + * subqueries always goes to the bit bucket. + */ + if (!canSetTag) + dest =3D CreateDestReceiver(DestNone); + else if (caller_dest) + dest =3D caller_dest; + else + dest =3D CreateDestReceiver(DestSPI); = if (stmt->utilityStmt =3D=3D NULL) { @@ -2373,7 +2506,13 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo pa= ramLI, SPI_freetuptable(_SPI_current->tuptable); _SPI_current->tuptable =3D NULL; } - /* we know that the receiver doesn't need a destroy call */ + + /* + * We don't issue a destroy call to the receiver. The SPI and + * None receivers would ignore it anyway, while if the caller + * supplied a receiver, it's not our job to destroy it. + */ + if (res < 0) { my_res =3D res; @@ -2465,7 +2604,7 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers= , uint64 tcount) switch (operation) { case CMD_SELECT: - if (queryDesc->dest->mydest !=3D DestSPI) + if (queryDesc->dest->mydest =3D=3D DestNone) { /* Don't return SPI_OK_SELECT if we're discarding result */ res =3D SPI_OK_UTILITY; diff --git a/src/backend/executor/tstoreReceiver.c b/src/backend/executor/= tstoreReceiver.c index 6c2dfbc1a6..e8172bedd0 100644 --- a/src/backend/executor/tstoreReceiver.c +++ b/src/backend/executor/tstoreReceiver.c @@ -8,6 +8,8 @@ * toasted values. This is to support cursors WITH HOLD, which must reta= in * data even if the underlying table is dropped. * + * Also optionally, we can apply a tuple conversion map before storing. + * * * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California @@ -21,6 +23,7 @@ #include "postgres.h" = #include "access/detoast.h" +#include "access/tupconvert.h" #include "executor/tstoreReceiver.h" = = @@ -31,14 +34,19 @@ typedef struct Tuplestorestate *tstore; /* where to put the data */ MemoryContext cxt; /* context containing tstore */ bool detoast; /* were we told to detoast? */ + TupleDesc target_tupdesc; /* target tupdesc, or NULL if none */ + const char *map_failure_msg; /* tupdesc mapping failure message */ /* workspace: */ Datum *outvalues; /* values array for result tuple */ Datum *tofree; /* temp values to be pfree'd */ + TupleConversionMap *tupmap; /* conversion map, if needed */ + TupleTableSlot *mapslot; /* slot for mapped tuples */ } TStoreState; = = static bool tstoreReceiveSlot_notoast(TupleTableSlot *slot, DestReceiver = *self); static bool tstoreReceiveSlot_detoast(TupleTableSlot *slot, DestReceiver = *self); +static bool tstoreReceiveSlot_tupmap(TupleTableSlot *slot, DestReceiver *= self); = = /* @@ -69,27 +77,46 @@ tstoreStartupReceiver(DestReceiver *self, int operatio= n, TupleDesc typeinfo) } } = + /* Check if tuple conversion is needed */ + if (myState->target_tupdesc) + myState->tupmap =3D convert_tuples_by_position(typeinfo, + myState->target_tupdesc, + myState->map_failure_msg); + else + myState->tupmap =3D NULL; + /* Set up appropriate callback */ if (needtoast) { + Assert(!myState->tupmap); myState->pub.receiveSlot =3D tstoreReceiveSlot_detoast; /* Create workspace */ myState->outvalues =3D (Datum *) MemoryContextAlloc(myState->cxt, natts * sizeof(Datum)); myState->tofree =3D (Datum *) MemoryContextAlloc(myState->cxt, natts * sizeof(Datum)); + myState->mapslot =3D NULL; + } + else if (myState->tupmap) + { + myState->pub.receiveSlot =3D tstoreReceiveSlot_tupmap; + myState->outvalues =3D NULL; + myState->tofree =3D NULL; + myState->mapslot =3D MakeSingleTupleTableSlot(myState->target_tupdesc, + &TTSOpsVirtual); } else { myState->pub.receiveSlot =3D tstoreReceiveSlot_notoast; myState->outvalues =3D NULL; myState->tofree =3D NULL; + myState->mapslot =3D NULL; } } = /* * Receive a tuple from the executor and store it in the tuplestore. - * This is for the easy case where we don't have to detoast. + * This is for the easy case where we don't have to detoast nor map anyth= ing. */ static bool tstoreReceiveSlot_notoast(TupleTableSlot *slot, DestReceiver *self) @@ -157,6 +184,21 @@ tstoreReceiveSlot_detoast(TupleTableSlot *slot, DestR= eceiver *self) return true; } = +/* + * Receive a tuple from the executor and store it in the tuplestore. + * This is for the case where we must apply a tuple conversion map. + */ +static bool +tstoreReceiveSlot_tupmap(TupleTableSlot *slot, DestReceiver *self) +{ + TStoreState *myState =3D (TStoreState *) self; + + execute_attr_map_slot(myState->tupmap->attrMap, slot, myState->mapslot); + tuplestore_puttupleslot(myState->tstore, myState->mapslot); + + return true; +} + /* * Clean up at end of an executor run */ @@ -172,6 +214,12 @@ tstoreShutdownReceiver(DestReceiver *self) if (myState->tofree) pfree(myState->tofree); myState->tofree =3D NULL; + if (myState->tupmap) + free_conversion_map(myState->tupmap); + myState->tupmap =3D NULL; + if (myState->mapslot) + ExecDropSingleTupleTableSlot(myState->mapslot); + myState->mapslot =3D NULL; } = /* @@ -204,17 +252,32 @@ CreateTuplestoreDestReceiver(void) = /* * Set parameters for a TuplestoreDestReceiver + * + * tStore: where to store the tuples + * tContext: memory context containing tStore + * detoast: forcibly detoast contained data? + * target_tupdesc: if not NULL, forcibly convert tuples to this rowtype + * map_failure_msg: error message to use if mapping to target_tupdesc fai= ls + * + * We don't currently support both detoast and target_tupdesc at the same + * time, just because no existing caller needs that combination. */ void SetTuplestoreDestReceiverParams(DestReceiver *self, Tuplestorestate *tStore, MemoryContext tContext, - bool detoast) + bool detoast, + TupleDesc target_tupdesc, + const char *map_failure_msg) { TStoreState *myState =3D (TStoreState *) self; = + Assert(!(detoast && target_tupdesc)); + Assert(myState->pub.mydest =3D=3D DestTuplestore); myState->tstore =3D tStore; myState->cxt =3D tContext; myState->detoast =3D detoast; + myState->target_tupdesc =3D target_tupdesc; + myState->map_failure_msg =3D map_failure_msg; } diff --git a/src/backend/nodes/params.c b/src/backend/nodes/params.c index ed2ee6a975..1719119fc2 100644 --- a/src/backend/nodes/params.c +++ b/src/backend/nodes/params.c @@ -17,19 +17,27 @@ = #include "access/xact.h" #include "mb/stringinfo_mb.h" -#include "nodes/bitmapset.h" #include "nodes/params.h" +#include "parser/parse_node.h" #include "storage/shmem.h" #include "utils/datum.h" #include "utils/lsyscache.h" #include "utils/memutils.h" = = +static void paramlist_parser_setup(ParseState *pstate, void *arg); +static Node *paramlist_param_ref(ParseState *pstate, ParamRef *pref); + + /* * Allocate and initialize a new ParamListInfo structure. * * To make a new structure for the "dynamic" way (with hooks), pass 0 for * numParams and set numParams manually. + * + * A default parserSetup function is supplied automatically. Callers may + * override it if they choose. (Note that most use-cases for ParamListIn= fos + * will never use the parserSetup function anyway.) */ ParamListInfo makeParamList(int numParams) @@ -45,8 +53,8 @@ makeParamList(int numParams) retval->paramFetchArg =3D NULL; retval->paramCompile =3D NULL; retval->paramCompileArg =3D NULL; - retval->parserSetup =3D NULL; - retval->parserSetupArg =3D NULL; + retval->parserSetup =3D paramlist_parser_setup; + retval->parserSetupArg =3D (void *) retval; retval->paramValuesStr =3D NULL; retval->numParams =3D numParams; = @@ -102,6 +110,55 @@ copyParamList(ParamListInfo from) return retval; } = + +/* + * Set up to parse a query containing references to parameters + * sourced from a ParamListInfo. + */ +static void +paramlist_parser_setup(ParseState *pstate, void *arg) +{ + pstate->p_paramref_hook =3D paramlist_param_ref; + /* no need to use p_coerce_param_hook */ + pstate->p_ref_hook_state =3D arg; +} + +/* + * Transform a ParamRef using parameter type data from a ParamListInfo. + */ +static Node * +paramlist_param_ref(ParseState *pstate, ParamRef *pref) +{ + ParamListInfo paramLI =3D (ParamListInfo) pstate->p_ref_hook_state; + int paramno =3D pref->number; + ParamExternData *prm; + ParamExternData prmdata; + Param *param; + + /* check parameter number is valid */ + if (paramno <=3D 0 || paramno > paramLI->numParams) + return NULL; + + /* give hook a chance in case parameter is dynamic */ + if (paramLI->paramFetch !=3D NULL) + prm =3D paramLI->paramFetch(paramLI, paramno, false, &prmdata); + else + prm =3D ¶mLI->params[paramno - 1]; + + if (!OidIsValid(prm->ptype)) + return NULL; + + param =3D makeNode(Param); + param->paramkind =3D PARAM_EXTERN; + param->paramid =3D paramno; + param->paramtype =3D prm->ptype; + param->paramtypmod =3D -1; + param->paramcollid =3D get_typcollation(param->paramtype); + param->location =3D pref->location; + + return (Node *) param; +} + /* * Estimate the amount of space required to serialize a ParamListInfo. */ diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5781fb2e55..96ea74f118 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -996,7 +996,9 @@ FillPortalStore(Portal portal, bool isTopLevel) SetTuplestoreDestReceiverParams(treceiver, portal->holdStore, portal->holdContext, - false); + false, + NULL, + NULL); = switch (portal->strategy) { diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h index 06de20ada5..896ec0a2ad 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 *Val= ues, 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 *arg= types, int cursorOptions); @@ -150,6 +158,11 @@ extern Portal SPI_cursor_open_with_args(const char *n= ame, 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 b2390c4a4d..e9461cf914 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 */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 9a87cd70f1..92d930fec8 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -27,6 +27,7 @@ #include "executor/execExpr.h" #include "executor/spi.h" #include "executor/spi_priv.h" +#include "executor/tstoreReceiver.h" #include "funcapi.h" #include "mb/stringinfo_mb.h" #include "miscadmin.h" @@ -51,14 +52,6 @@ #include "utils/syscache.h" #include "utils/typcache.h" = -typedef struct -{ - int nargs; /* number of arguments */ - Oid *types; /* types of arguments */ - Datum *values; /* evaluated argument values */ - char *nulls; /* null markers (' '/'n' style) */ -} PreparedParamsData; - /* * All plpgsql function executions within a single transaction share the = same * executor EState for evaluating "simple" expressions. Each function ca= ll @@ -441,15 +434,15 @@ static void assign_text_var(PLpgSQL_execstate *estat= e, PLpgSQL_var *var, const char *str); static void assign_record_var(PLpgSQL_execstate *estate, PLpgSQL_rec *rec= , ExpandedRecordHeader *erh); -static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *esta= te, - List *params); +static ParamListInfo exec_eval_using_params(PLpgSQL_execstate *estate, + List *params); static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery, List *params, const char *portalname, int cursorOptions); static char *format_expr_params(PLpgSQL_execstate *estate, const PLpgSQL_expr *expr); static char *format_preparedparamsdata(PLpgSQL_execstate *estate, - const PreparedParamsData *ppd); + ParamListInfo paramLI); = = /* ---------- @@ -3513,9 +3506,11 @@ static int exec_stmt_return_query(PLpgSQL_execstate *estate, PLpgSQL_stmt_return_query *stmt) { - Portal portal; - uint64 processed =3D 0; - TupleConversionMap *tupmap; + int64 tcount; + DestReceiver *treceiver; + int rc; + uint64 processed; + MemoryContext stmt_mcontext =3D get_stmt_mcontext(estate); MemoryContext oldcontext; = if (!estate->retisset) @@ -3525,60 +3520,99 @@ exec_stmt_return_query(PLpgSQL_execstate *estate, = if (estate->tuple_store =3D=3D NULL) exec_init_tuple_store(estate); + /* There might be some tuples in the tuplestore already */ + tcount =3D tuplestore_tuple_count(estate->tuple_store); + + /* + * Set up DestReceiver to transfer results directly to tuplestore, + * converting rowtype if necessary. DestReceiver lives in mcontext. + */ + oldcontext =3D MemoryContextSwitchTo(stmt_mcontext); + treceiver =3D CreateDestReceiver(DestTuplestore); + SetTuplestoreDestReceiverParams(treceiver, + estate->tuple_store, + estate->tuple_store_cxt, + false, + estate->tuple_store_desc, + gettext_noop("structure of query does not match function result = type")); + MemoryContextSwitchTo(oldcontext); = if (stmt->query !=3D NULL) { /* static query */ - exec_run_select(estate, stmt->query, 0, &portal); - } - else - { - /* RETURN QUERY EXECUTE */ - Assert(stmt->dynquery !=3D NULL); - portal =3D exec_dynquery_with_params(estate, stmt->dynquery, - stmt->params, NULL, - 0); - } + PLpgSQL_expr *expr =3D stmt->query; + ParamListInfo paramLI; = - /* Use eval_mcontext for tuple conversion work */ - oldcontext =3D MemoryContextSwitchTo(get_eval_mcontext(estate)); + /* + * On the first call for this expression generate the plan. + */ + if (expr->plan =3D=3D NULL) + exec_prepare_plan(estate, expr, CURSOR_OPT_PARALLEL_OK, true); = - tupmap =3D convert_tuples_by_position(portal->tupDesc, - estate->tuple_store_desc, - gettext_noop("structure of query does not match function result= type")); + /* + * Set up ParamListInfo to pass to executor + */ + paramLI =3D setup_param_list(estate, expr); = - while (true) + /* + * Execute the query + */ + rc =3D SPI_execute_plan_with_receiver(expr->plan, paramLI, + estate->readonly_func, 0, + treceiver); + if (rc !=3D SPI_OK_SELECT) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("query \"%s\" is not a SELECT", expr->query))); + } + else { - uint64 i; - - SPI_cursor_fetch(portal, true, 50); + /* RETURN QUERY EXECUTE */ + Datum query; + bool isnull; + Oid restype; + int32 restypmod; + char *querystr; = - /* SPI will have changed CurrentMemoryContext */ - MemoryContextSwitchTo(get_eval_mcontext(estate)); + /* + * Evaluate the string expression after the EXECUTE keyword. Its + * result is the querystring we have to execute. + */ + Assert(stmt->dynquery !=3D NULL); + query =3D exec_eval_expr(estate, stmt->dynquery, + &isnull, &restype, &restypmod); + if (isnull) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("query string argument of EXECUTE is null"))); = - if (SPI_processed =3D=3D 0) - break; + /* Get the C-String representation */ + querystr =3D convert_value_to_string(estate, query, restype); = - for (i =3D 0; i < SPI_processed; i++) - { - HeapTuple tuple =3D SPI_tuptable->vals[i]; + /* copy it into the stmt_mcontext before we clean up */ + querystr =3D MemoryContextStrdup(stmt_mcontext, querystr); = - if (tupmap) - tuple =3D execute_attr_map_tuple(tuple, tupmap); - tuplestore_puttuple(estate->tuple_store, tuple); - if (tupmap) - heap_freetuple(tuple); - processed++; - } + exec_eval_cleanup(estate); = - SPI_freetuptable(SPI_tuptable); + /* Execute query, passing params if necessary */ + rc =3D SPI_execute_with_receiver(querystr, + exec_eval_using_params(estate, + stmt->params), + estate->readonly_func, + 0, + treceiver); + if (rc < 0) + elog(ERROR, "SPI_execute_with_receiver failed executing query \"%s\": = %s", + querystr, SPI_result_code_string(rc)); } = - SPI_freetuptable(SPI_tuptable); - SPI_cursor_close(portal); - - MemoryContextSwitchTo(oldcontext); + /* Clean up */ + treceiver->rDestroy(treceiver); exec_eval_cleanup(estate); + MemoryContextReset(stmt_mcontext); + + /* Count how many tuples we got */ + processed =3D tuplestore_tuple_count(estate->tuple_store) - tcount; = estate->eval_processed =3D processed; exec_set_found(estate, processed !=3D 0); @@ -4344,7 +4378,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, int32 restypmod; char *querystr; int exec_res; - PreparedParamsData *ppd =3D NULL; + ParamListInfo paramLI; MemoryContext stmt_mcontext =3D get_stmt_mcontext(estate); = /* @@ -4368,16 +4402,9 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, /* * Execute the query without preparing a saved plan. */ - if (stmt->params) - { - ppd =3D exec_eval_using_params(estate, stmt->params); - exec_res =3D SPI_execute_with_args(querystr, - ppd->nargs, ppd->types, - ppd->values, ppd->nulls, - estate->readonly_func, 0); - } - else - exec_res =3D SPI_execute(querystr, estate->readonly_func, 0); + paramLI =3D exec_eval_using_params(estate, stmt->params); + exec_res =3D SPI_execute_with_receiver(querystr, paramLI, + estate->readonly_func, 0, NULL); = switch (exec_res) { @@ -4429,7 +4456,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, break; = default: - elog(ERROR, "SPI_execute failed executing query \"%s\": %s", + elog(ERROR, "SPI_execute_with_receiver failed executing query \"%s\": = %s", querystr, SPI_result_code_string(exec_res)); break; } @@ -4465,7 +4492,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, char *errdetail; = if (estate->func->print_strict_params) - errdetail =3D format_preparedparamsdata(estate, ppd); + errdetail =3D format_preparedparamsdata(estate, paramLI); else errdetail =3D NULL; = @@ -4484,7 +4511,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, char *errdetail; = if (estate->func->print_strict_params) - errdetail =3D format_preparedparamsdata(estate, ppd); + errdetail =3D format_preparedparamsdata(estate, paramLI); else errdetail =3D NULL; = @@ -6308,9 +6335,9 @@ exec_eval_simple_expr(PLpgSQL_execstate *estate, /* * Create a ParamListInfo to pass to SPI * - * We use a single ParamListInfo struct for all SPI calls made from this - * estate; it contains no per-param data, just hook functions, so it's - * effectively read-only for SPI. + * We use a single ParamListInfo struct for all SPI calls made to evaluat= e + * PLpgSQL_exprs in this estate. It contains no per-param data, just hoo= k + * functions, so it's effectively read-only for SPI. * * An exception from pure read-only-ness is that the parserSetupArg point= s * to the specific PLpgSQL_expr being evaluated. This is not an issue fo= r @@ -8575,65 +8602,68 @@ assign_record_var(PLpgSQL_execstate *estate, PLpgS= QL_rec *rec, * The result data structure is created in the stmt_mcontext, and should * be freed by resetting that context. */ -static PreparedParamsData * +static ParamListInfo exec_eval_using_params(PLpgSQL_execstate *estate, List *params) { - PreparedParamsData *ppd; - MemoryContext stmt_mcontext =3D get_stmt_mcontext(estate); + ParamListInfo paramLI; int nargs; + MemoryContext stmt_mcontext; + MemoryContext oldcontext; int i; ListCell *lc; = - ppd =3D (PreparedParamsData *) - MemoryContextAlloc(stmt_mcontext, sizeof(PreparedParamsData)); - nargs =3D list_length(params); + /* Fast path for no parameters: we can just return NULL */ + if (params =3D=3D NIL) + return NULL; = - ppd->nargs =3D nargs; - ppd->types =3D (Oid *) - MemoryContextAlloc(stmt_mcontext, nargs * sizeof(Oid)); - ppd->values =3D (Datum *) - MemoryContextAlloc(stmt_mcontext, nargs * sizeof(Datum)); - ppd->nulls =3D (char *) - MemoryContextAlloc(stmt_mcontext, nargs * sizeof(char)); + nargs =3D list_length(params); + stmt_mcontext =3D get_stmt_mcontext(estate); + oldcontext =3D MemoryContextSwitchTo(stmt_mcontext); + paramLI =3D makeParamList(nargs); + MemoryContextSwitchTo(oldcontext); = i =3D 0; foreach(lc, params) { PLpgSQL_expr *param =3D (PLpgSQL_expr *) lfirst(lc); - bool isnull; + ParamExternData *prm =3D ¶mLI->params[i]; int32 ppdtypmod; - MemoryContext oldcontext; = - ppd->values[i] =3D exec_eval_expr(estate, param, - &isnull, - &ppd->types[i], - &ppdtypmod); - ppd->nulls[i] =3D isnull ? 'n' : ' '; + /* + * Always mark params as const, since we only use the result with + * one-shot plans. + */ + prm->pflags =3D PARAM_FLAG_CONST; + + prm->value =3D exec_eval_expr(estate, param, + &prm->isnull, + &prm->ptype, + &ppdtypmod); = oldcontext =3D MemoryContextSwitchTo(stmt_mcontext); = - if (ppd->types[i] =3D=3D UNKNOWNOID) + if (prm->ptype =3D=3D UNKNOWNOID) { /* * Treat 'unknown' parameters as text, since that's what most - * people would expect. SPI_execute_with_args can coerce unknown - * constants in a more intelligent way, but not unknown Params. - * This code also takes care of copying into the right context. - * Note we assume 'unknown' has the representation of C-string. + * people would expect. SPI_execute* can coerce unknown constants + * in a more intelligent way, but not unknown Params. This code + * also takes care of copying into the right context. Note we + * assume 'unknown' has the representation of C-string. */ - ppd->types[i] =3D TEXTOID; - if (!isnull) - ppd->values[i] =3D CStringGetTextDatum(DatumGetCString(ppd->values[i]= )); + prm->ptype =3D TEXTOID; + if (!prm->isnull) + prm->value =3D CStringGetTextDatum(DatumGetCString(prm->value)); } /* pass-by-ref non null values must be copied into stmt_mcontext */ - else if (!isnull) + else if (!prm->isnull) { int16 typLen; bool typByVal; = - get_typlenbyval(ppd->types[i], &typLen, &typByVal); + get_typlenbyval(prm->ptype, &typLen, &typByVal); if (!typByVal) - ppd->values[i] =3D datumCopy(ppd->values[i], typByVal, typLen); + prm->value =3D datumCopy(prm->value, typByVal, typLen); } = MemoryContextSwitchTo(oldcontext); @@ -8643,7 +8673,7 @@ exec_eval_using_params(PLpgSQL_execstate *estate, Li= st *params) i++; } = - return ppd; + return paramLI; } = /* @@ -8689,30 +8719,15 @@ exec_dynquery_with_params(PLpgSQL_execstate *estat= e, = /* * Open an implicit cursor for the query. We use - * SPI_cursor_open_with_args even when there are no params, because this - * avoids making and freeing one copy of the plan. + * SPI_cursor_parse_open_with_paramlist even when there are no params, + * because this avoids making and freeing one copy of the plan. */ - if (params) - { - PreparedParamsData *ppd; - - ppd =3D exec_eval_using_params(estate, params); - portal =3D SPI_cursor_open_with_args(portalname, - querystr, - ppd->nargs, ppd->types, - ppd->values, ppd->nulls, - estate->readonly_func, - cursorOptions); - } - else - { - portal =3D SPI_cursor_open_with_args(portalname, - querystr, - 0, NULL, - NULL, NULL, - estate->readonly_func, - cursorOptions); - } + portal =3D SPI_cursor_parse_open_with_paramlist(portalname, + querystr, + exec_eval_using_params(estate, + params), + estate->readonly_func, + cursorOptions); = if (portal =3D=3D NULL) elog(ERROR, "could not open implicit cursor for query \"%s\": %s", @@ -8782,37 +8797,44 @@ format_expr_params(PLpgSQL_execstate *estate, } = /* - * Return a formatted string with information about PreparedParamsData, o= r NULL - * if there are no parameters. + * Return a formatted string with information about the parameter values, + * or NULL if there are no parameters. * The result is in the eval_mcontext. */ static char * format_preparedparamsdata(PLpgSQL_execstate *estate, - const PreparedParamsData *ppd) + ParamListInfo paramLI) { int paramno; StringInfoData paramstr; MemoryContext oldcontext; = - if (!ppd) + if (!paramLI) return NULL; = oldcontext =3D MemoryContextSwitchTo(get_eval_mcontext(estate)); = initStringInfo(¶mstr); - for (paramno =3D 0; paramno < ppd->nargs; paramno++) + for (paramno =3D 0; paramno < paramLI->numParams; paramno++) { + ParamExternData *prm =3D ¶mLI->params[paramno]; + + /* + * Note: for now, this is only used on ParamListInfos produced by + * exec_eval_using_params(), so we don't worry about invoking the + * paramFetch hook or skipping unused parameters. + */ appendStringInfo(¶mstr, "%s$%d =3D ", paramno > 0 ? ", " : "", paramno + 1); = - if (ppd->nulls[paramno] =3D=3D 'n') + if (prm->isnull) appendStringInfoString(¶mstr, "NULL"); else appendStringInfoStringQuoted(¶mstr, convert_value_to_string(estate, - ppd->values[paramno], - ppd->types[paramno]), + prm->value, + prm->ptype), -1); } = ------- =_aaaaaaaaaa0--