public inbox for [email protected]help / color / mirror / Atom feed
Re: dynamic result sets support in extended query protocol 5+ messages / 3 participants [nested] [flat]
* Re: dynamic result sets support in extended query protocol @ 2023-01-30 13:06 Alvaro Herrera <[email protected]> 2023-01-31 11:07 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Alvaro Herrera @ 2023-01-30 13:06 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On 2022-Nov-22, Peter Eisentraut wrote: > I added tests using the new psql \bind command to test this functionality in > the extended query protocol, which showed that this got broken since I first > wrote this patch. This "blame" is on the pipeline mode in libpq patch > (acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659). I need to spend more time on > this and figure out how to repair it. Applying this patch, your test queries seem to work correctly. This is quite WIP, especially because there's a couple of scenarios uncovered by tests that I'd like to ensure correctness about, but if you would like to continue adding tests for extended query and dynamic result sets, it may be helpful. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "How strange it is to find the words "Perl" and "saner" in such close proximity, with no apparent sense of irony. I doubt that Larry himself could have managed it." (ncm, http://lwn.net/Articles/174769/) diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index ec62550e38..b530c51ccd 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -2109,19 +2109,19 @@ PQgetResult(PGconn *conn) break; case PGASYNC_READY: + res = pqPrepareAsyncResult(conn); /* - * For any query type other than simple query protocol, we advance - * the command queue here. This is because for simple query - * protocol we can get the READY state multiple times before the - * command is actually complete, since the command string can - * contain many queries. In simple query protocol, the queue - * advance is done by fe-protocol3 when it receives ReadyForQuery. + * When an error has occurred, we consume one command from the + * queue for each result we return. (Normally, the command would + * be consumed as each result is read from the server.) */ if (conn->cmd_queue_head && - conn->cmd_queue_head->queryclass != PGQUERY_SIMPLE) + (conn->error_result || + (conn->result != NULL && + conn->result->resultStatus == PGRES_FATAL_ERROR))) pqCommandQueueAdvance(conn); - res = pqPrepareAsyncResult(conn); + if (conn->pipelineStatus != PQ_PIPELINE_OFF) { /* diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..2ed74aa0f1 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -205,6 +205,10 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + if (conn->cmd_queue_head && + conn->cmd_queue_head->queryclass != PGQUERY_SIMPLE) + pqCommandQueueAdvance(conn); + if (conn->result) strlcpy(conn->result->cmdStatus, conn->workBuffer.data, CMDSTATUS_LEN); @@ -231,6 +235,7 @@ pqParseInput3(PGconn *conn) else { conn->pipelineStatus = PQ_PIPELINE_ON; + pqCommandQueueAdvance(conn); conn->asyncStatus = PGASYNC_READY; } } @@ -257,6 +262,7 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + /* XXX should we advance the command queue here? */ conn->asyncStatus = PGASYNC_READY; break; case '1': /* Parse Complete */ @@ -274,6 +280,7 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + pqCommandQueueAdvance(conn); conn->asyncStatus = PGASYNC_READY; } break; @@ -324,6 +331,10 @@ pqParseInput3(PGconn *conn) * really possible with the current backend.) We stop * parsing until the application accepts the current * result. + * + * Note that we must have already read one 'T' message + * previously for the same command, so we do not + * advance the command queue here. */ conn->asyncStatus = PGASYNC_READY; return; @@ -355,6 +366,7 @@ pqParseInput3(PGconn *conn) } } conn->asyncStatus = PGASYNC_READY; + pqCommandQueueAdvance(conn); } break; case 't': /* Parameter Description */ @@ -593,14 +605,20 @@ getRowDescriptions(PGconn *conn, int msgLength) /* * If we're doing a Describe, we're done, and ready to pass the result * back to the client. + * + * XXX this coding here is a bit suspicious ... */ - if ((!conn->cmd_queue_head) || - (conn->cmd_queue_head && - conn->cmd_queue_head->queryclass == PGQUERY_DESCRIBE)) + if (!conn->cmd_queue_head) { conn->asyncStatus = PGASYNC_READY; return 0; } + else if (conn->cmd_queue_head->queryclass == PGQUERY_DESCRIBE) + { + conn->asyncStatus = PGASYNC_READY; + pqCommandQueueAdvance(conn); + return 0; + } /* * We could perform additional setup for the new result set here, but for Attachments: [text/plain] libpq-fix.patch.txt (3.7K, ../../[email protected]/2-libpq-fix.patch.txt) download | inline diff: diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index ec62550e38..b530c51ccd 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -2109,19 +2109,19 @@ PQgetResult(PGconn *conn) break; case PGASYNC_READY: + res = pqPrepareAsyncResult(conn); /* - * For any query type other than simple query protocol, we advance - * the command queue here. This is because for simple query - * protocol we can get the READY state multiple times before the - * command is actually complete, since the command string can - * contain many queries. In simple query protocol, the queue - * advance is done by fe-protocol3 when it receives ReadyForQuery. + * When an error has occurred, we consume one command from the + * queue for each result we return. (Normally, the command would + * be consumed as each result is read from the server.) */ if (conn->cmd_queue_head && - conn->cmd_queue_head->queryclass != PGQUERY_SIMPLE) + (conn->error_result || + (conn->result != NULL && + conn->result->resultStatus == PGRES_FATAL_ERROR))) pqCommandQueueAdvance(conn); - res = pqPrepareAsyncResult(conn); + if (conn->pipelineStatus != PQ_PIPELINE_OFF) { /* diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..2ed74aa0f1 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -205,6 +205,10 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + if (conn->cmd_queue_head && + conn->cmd_queue_head->queryclass != PGQUERY_SIMPLE) + pqCommandQueueAdvance(conn); + if (conn->result) strlcpy(conn->result->cmdStatus, conn->workBuffer.data, CMDSTATUS_LEN); @@ -231,6 +235,7 @@ pqParseInput3(PGconn *conn) else { conn->pipelineStatus = PQ_PIPELINE_ON; + pqCommandQueueAdvance(conn); conn->asyncStatus = PGASYNC_READY; } } @@ -257,6 +262,7 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + /* XXX should we advance the command queue here? */ conn->asyncStatus = PGASYNC_READY; break; case '1': /* Parse Complete */ @@ -274,6 +280,7 @@ pqParseInput3(PGconn *conn) pqSaveErrorResult(conn); } } + pqCommandQueueAdvance(conn); conn->asyncStatus = PGASYNC_READY; } break; @@ -324,6 +331,10 @@ pqParseInput3(PGconn *conn) * really possible with the current backend.) We stop * parsing until the application accepts the current * result. + * + * Note that we must have already read one 'T' message + * previously for the same command, so we do not + * advance the command queue here. */ conn->asyncStatus = PGASYNC_READY; return; @@ -355,6 +366,7 @@ pqParseInput3(PGconn *conn) } } conn->asyncStatus = PGASYNC_READY; + pqCommandQueueAdvance(conn); } break; case 't': /* Parameter Description */ @@ -593,14 +605,20 @@ getRowDescriptions(PGconn *conn, int msgLength) /* * If we're doing a Describe, we're done, and ready to pass the result * back to the client. + * + * XXX this coding here is a bit suspicious ... */ - if ((!conn->cmd_queue_head) || - (conn->cmd_queue_head && - conn->cmd_queue_head->queryclass == PGQUERY_DESCRIBE)) + if (!conn->cmd_queue_head) { conn->asyncStatus = PGASYNC_READY; return 0; } + else if (conn->cmd_queue_head->queryclass == PGQUERY_DESCRIBE) + { + conn->asyncStatus = PGASYNC_READY; + pqCommandQueueAdvance(conn); + return 0; + } /* * We could perform additional setup for the new result set here, but for ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic result sets support in extended query protocol 2023-01-30 13:06 Re: dynamic result sets support in extended query protocol Alvaro Herrera <[email protected]> @ 2023-01-31 11:07 ` Peter Eisentraut <[email protected]> 2023-02-20 12:58 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Peter Eisentraut @ 2023-01-31 11:07 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: pgsql-hackers On 30.01.23 14:06, Alvaro Herrera wrote: > On 2022-Nov-22, Peter Eisentraut wrote: > >> I added tests using the new psql \bind command to test this functionality in >> the extended query protocol, which showed that this got broken since I first >> wrote this patch. This "blame" is on the pipeline mode in libpq patch >> (acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659). I need to spend more time on >> this and figure out how to repair it. > > Applying this patch, your test queries seem to work correctly. Great! > This is quite WIP, especially because there's a couple of scenarios > uncovered by tests that I'd like to ensure correctness about, but if you > would like to continue adding tests for extended query and dynamic > result sets, it may be helpful. I should note that it is debatable whether my patch extends the extended query protocol or just uses it within its existing spec but in new ways. It just happened to work in old libpq versions without any changes. So you should keep that in mind as you refine your patch, since the way the protocol has been extended/creatively-used is still subject to review. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic result sets support in extended query protocol 2023-01-30 13:06 Re: dynamic result sets support in extended query protocol Alvaro Herrera <[email protected]> 2023-01-31 11:07 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> @ 2023-02-20 12:58 ` Peter Eisentraut <[email protected]> 2023-02-24 11:26 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Peter Eisentraut @ 2023-02-20 12:58 UTC (permalink / raw) To: pgsql-hackers; +Cc: Alvaro Herrera <[email protected]> On 31.01.23 12:07, Peter Eisentraut wrote: >> Applying this patch, your test queries seem to work correctly. > > Great! > >> This is quite WIP, especially because there's a couple of scenarios >> uncovered by tests that I'd like to ensure correctness about, but if you >> would like to continue adding tests for extended query and dynamic >> result sets, it may be helpful. > > I should note that it is debatable whether my patch extends the extended > query protocol or just uses it within its existing spec but in new ways. > It just happened to work in old libpq versions without any changes. So > you should keep that in mind as you refine your patch, since the way the > protocol has been extended/creatively-used is still subject to review. After some consideration, I have an idea how to proceed with this. I have split my original patch into two incremental patches. The first patch implements the original feature, but just for the simple query protocol. (The simple query protocol already supports multiple result sets.) Attempting to return dynamic result sets using the extended query protocol will result in an error. The second patch then adds the extended query protocol support back in, but it still has the issues with libpq that we are discussing. I think this way we could have a chance to get the first part into PG16 or early into PG17, and then the second part can be worked on with less stress. This would also allow us to consider a minor protocol version bump, and the handling of binary format for dynamic result sets (like https://commitfest.postgresql.org/42/3777/), and maybe some other issues. The attached patches are the same as before, rebased over master and split up as described. I haven't done any significant work on the contents, but I will try to get the 0001 patch into a more polished state soon. From 1e22417c8cfa6aa230a21a6aa25b166b3b4bbecb Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Mon, 20 Feb 2023 12:09:24 +0100 Subject: [PATCH v7 1/2] Dynamic result sets from procedures Declaring a cursor WITH RETURN in a procedure makes the cursor's data be returned as a result of the CALL invocation. The procedure needs to be declared with the DYNAMIC RESULT SETS attribute. Discussion: https://www.postgresql.org/message-id/flat/[email protected] --- doc/src/sgml/catalogs.sgml | 10 ++ doc/src/sgml/information_schema.sgml | 3 +- doc/src/sgml/plpgsql.sgml | 27 ++++- doc/src/sgml/ref/alter_procedure.sgml | 12 +++ doc/src/sgml/ref/create_procedure.sgml | 14 +++ doc/src/sgml/ref/declare.sgml | 34 +++++- src/backend/catalog/information_schema.sql | 2 +- src/backend/catalog/pg_aggregate.c | 3 +- src/backend/catalog/pg_proc.c | 4 +- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/functioncmds.c | 79 ++++++++++++-- src/backend/commands/portalcmds.c | 23 ++++ src/backend/commands/typecmds.c | 12 ++- src/backend/parser/gram.y | 18 +++- src/backend/tcop/postgres.c | 37 ++++++- src/backend/utils/errcodes.txt | 1 + src/backend/utils/mmgr/portalmem.c | 48 +++++++++ src/bin/pg_dump/pg_dump.c | 16 ++- src/include/catalog/pg_proc.h | 6 +- src/include/commands/defrem.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 2 + src/include/utils/portal.h | 12 +++ src/pl/plpgsql/src/expected/plpgsql_call.out | 78 ++++++++++++++ src/pl/plpgsql/src/pl_exec.c | 6 ++ src/pl/plpgsql/src/pl_gram.y | 58 ++++++++-- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 2 + src/pl/plpgsql/src/sql/plpgsql_call.sql | 46 ++++++++ .../regress/expected/create_procedure.out | 100 +++++++++++++++++- src/test/regress/sql/create_procedure.sql | 65 +++++++++++- 30 files changed, 685 insertions(+), 37 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054..5baec4dc3a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6041,6 +6041,16 @@ <title><structname>pg_proc</structname> Columns</title> </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>prodynres</structfield> <type>int4</type> + </para> + <para> + For procedures, this records the maximum number of dynamic result sets + the procedure may create. Otherwise zero. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>pronargs</structfield> <type>int2</type> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 350c75bc31..5fc9dc22ae 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -5885,7 +5885,8 @@ <title><structname>routines</structname> Columns</title> <structfield>max_dynamic_result_sets</structfield> <type>cardinal_number</type> </para> <para> - Applies to a feature not available in <productname>PostgreSQL</productname> + For a procedure, the maximum number of dynamic result sets. Otherwise + zero. </para></entry> </row> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 8897a5450a..0c0d77b0e6 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3128,7 +3128,7 @@ <title>Declaring Cursor Variables</title> Another way is to use the cursor declaration syntax, which in general is: <synopsis> -<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; +<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> <optional> WITH RETURN </optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; </synopsis> (<literal>FOR</literal> can be replaced by <literal>IS</literal> for <productname>Oracle</productname> compatibility.) @@ -3136,6 +3136,10 @@ <title>Declaring Cursor Variables</title> scrolling backward; if <literal>NO SCROLL</literal> is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. + If <literal>WITH RETURN</literal> is specified, the results of the + cursor, after it is opened, will be returned as a dynamic result set; see + <xref linkend="sql-declare"/> for details. (<literal>WITHOUT + RETURN</literal> can also be specified but has no effect.) <replaceable>arguments</replaceable>, if specified, is a comma-separated list of pairs <literal><replaceable>name</replaceable> <replaceable>datatype</replaceable></literal> that define names to be @@ -3215,7 +3219,7 @@ <title>Opening Cursors</title> <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> <synopsis> -OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>; +OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> <optional> WITH RETURN </optional> FOR <replaceable>query</replaceable>; </synopsis> <para> @@ -3233,8 +3237,9 @@ <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> substituted is the one it has at the time of the <command>OPEN</command>; subsequent changes to the variable will not affect the cursor's behavior. - The <literal>SCROLL</literal> and <literal>NO SCROLL</literal> - options have the same meanings as for a bound cursor. + The options <literal>SCROLL</literal>, <literal>NO SCROLL</literal>, + and <literal>WITH RETURN</literal> have the same meanings as for a + bound cursor. </para> <para> @@ -3612,6 +3617,20 @@ <title>Returning Cursors</title> COMMIT; </programlisting> </para> + + <note> + <para> + Returning a cursor from a function as described here is a separate + mechanism from declaring a cursor <literal>WITH RETURN</literal>, + which automatically produces a result set for the client if the + cursor is left open when returning from the procedure. Both + mechanisms can be used to achieve similar effects. The differences + are mainly how the client application prefers to manage the cursors. + Furthermore, other SQL implementations have other programming models + that might map more easily to one or the other mechanism when doing a + migration. + </para> + </note> </sect3> </sect2> diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index a4737a3439..2cdda7730e 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -34,6 +34,7 @@ <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> + DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT } SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT @@ -158,6 +159,17 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Changes the dynamic result sets setting of the procedure. See <xref + linkend="sql-createprocedure"/> for more information. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term> <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term> diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 03a14c8684..1c99b00eef 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -24,6 +24,7 @@ CREATE [ OR REPLACE ] PROCEDURE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] ) { LANGUAGE <replaceable class="parameter">lang_name</replaceable> + | DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } @@ -176,6 +177,19 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Specifies how many dynamic result sets the procedure returns (see + <literal><link linkend="sql-declare">DECLARE</link> WITH + RETURN</literal>). The default is 0. If a procedure returns more + result sets than declared, a warning is raised. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term> diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 5712825314..206ed760d9 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -32,7 +32,8 @@ <refsynopsisdiv> <synopsis> DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] - CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> + CURSOR [ { WITH | WITHOUT } HOLD ] [ { WITH | WITHOUT } RETURN ] + FOR <replaceable class="parameter">query</replaceable> </synopsis> </refsynopsisdiv> @@ -138,6 +139,22 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>WITH RETURN</literal></term> + <term><literal>WITHOUT RETURN</literal></term> + <listitem> + <para> + This option is only valid for cursors defined inside a procedure. + <literal>WITH RETURN</literal> specifies that the cursor's result rows + will be provided as a result set of the procedure invocation. To + accomplish that, the cursor must be left open at the end of the + procedure. If multiple <literal>WITH RETURN</literal> cursors are + declared, then their results will be returned in the order they were + created. <literal>WITHOUT RETURN</literal> is the default. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="parameter">query</replaceable></term> <listitem> @@ -339,6 +356,21 @@ <title>Examples</title> See <xref linkend="sql-fetch"/> for more examples of cursor usage. </para> + + <para> + This example shows how to return multiple result sets from a procedure: +<programlisting> +CREATE PROCEDURE test() +LANGUAGE SQL +AS $$ +DECLARE a CURSOR WITH RETURN FOR SELECT * FROM tbl1; +DECLARE b CURSOR WITH RETURN FOR SELECT * FROM tbl2; +$$; + +CALL test(); +</programlisting> + The results of the two cursors will be returned in order from this call. + </para> </refsect1> <refsect1> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..871a27b84b 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1593,7 +1593,7 @@ CREATE VIEW routines AS CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, CAST('YES' AS yes_or_no) AS schema_level_routine, - CAST(0 AS cardinal_number) AS max_dynamic_result_sets, + CAST(p.prodynres AS cardinal_number) AS max_dynamic_result_sets, CAST(null AS yes_or_no) AS is_user_defined_cast, CAST(null AS yes_or_no) AS is_implicitly_invocable, CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type, diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index ebc4454743..a633bb7501 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -640,7 +640,8 @@ AggregateCreate(const char *aggName, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* no prosupport */ 1, /* procost */ - 0); /* prorows */ + 0, /* prorows */ + 0); /* prodynres */ procOid = myself.objectId; /* diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 14d552fe2d..620fb80a9c 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -95,7 +95,8 @@ ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows) + float4 prorows, + int dynres) { Oid retval; int parameterCount; @@ -314,6 +315,7 @@ ProcedureCreate(const char *procedureName, values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet); values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility); values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel); + values[Anum_pg_proc_prodynres - 1] = Int32GetDatum(dynres); values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount); values[Anum_pg_proc_pronargdefaults - 1] = UInt16GetDatum(list_length(parameterDefaults)); values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType); diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 3766762ae3..c12fc07cd0 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -486,7 +486,7 @@ T433 Multiargument GROUPING function YES T434 GROUP BY DISTINCT YES T441 ABS and MOD functions YES T461 Symmetric BETWEEN predicate YES -T471 Result sets return value NO +T471 Result sets return value NO partially supported T472 DESCRIBE CURSOR NO T491 LATERAL derived table YES T495 Combined data change and retrieval NO different syntax diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 69f66dfe7d..5200a92fa9 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -72,6 +72,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/portal.h" #include "utils/rel.h" #include "utils/snapmgr.h" #include "utils/syscache.h" @@ -513,7 +514,8 @@ compute_common_attribute(ParseState *pstate, DefElem **cost_item, DefElem **rows_item, DefElem **support_item, - DefElem **parallel_item) + DefElem **parallel_item, + DefElem **dynres_item) { if (strcmp(defel->defname, "volatility") == 0) { @@ -589,12 +591,28 @@ compute_common_attribute(ParseState *pstate, *parallel_item = defel; } + else if (strcmp(defel->defname, "dynamic_result_sets") == 0) + { + if (!is_procedure) + goto function_error; + if (*dynres_item) + errorConflictingDefElem(defel, pstate); + + *dynres_item = defel; + } else return false; /* Recognized an option */ return true; +function_error: + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in function definition"), + parser_errposition(pstate, defel->location))); + return false; + procedure_error: ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), @@ -731,7 +749,8 @@ compute_function_attributes(ParseState *pstate, float4 *procost, float4 *prorows, Oid *prosupport, - char *parallel_p) + char *parallel_p, + int *dynres_p) { ListCell *option; DefElem *as_item = NULL; @@ -747,6 +766,7 @@ compute_function_attributes(ParseState *pstate, DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; foreach(option, options) { @@ -792,7 +812,8 @@ compute_function_attributes(ParseState *pstate, &cost_item, &rows_item, &support_item, - ¶llel_item)) + ¶llel_item, + &dynres_item)) { /* recognized common option */ continue; @@ -840,6 +861,11 @@ compute_function_attributes(ParseState *pstate, *prosupport = interpret_func_support(support_item); if (parallel_item) *parallel_p = interpret_func_parallel(parallel_item); + if (dynres_item) + { + *dynres_p = intVal(dynres_item->arg); + Assert(*dynres_p >= 0); /* enforced by parser */ + } } @@ -1051,6 +1077,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) Form_pg_language languageStruct; List *as_clause; char parallel; + int dynres; /* Convert list of names to a name and namespace */ namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname, @@ -1075,6 +1102,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) prorows = -1; /* indicates not set */ prosupport = InvalidOid; parallel = PROPARALLEL_UNSAFE; + dynres = 0; /* Extract non-default attributes from stmt->options list */ compute_function_attributes(pstate, @@ -1084,7 +1112,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &isWindowFunc, &volatility, &isStrict, &security, &isLeakProof, &proconfig, &procost, &prorows, - &prosupport, ¶llel); + &prosupport, ¶llel, &dynres); if (!language) { @@ -1285,7 +1313,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) PointerGetDatum(proconfig), prosupport, procost, - prorows); + prorows, + dynres); } /* @@ -1362,6 +1391,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; ObjectAddress address; rel = table_open(ProcedureRelationId, RowExclusiveLock); @@ -1405,7 +1435,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) &cost_item, &rows_item, &support_item, - ¶llel_item) == false) + ¶llel_item, + &dynres_item) == false) elog(ERROR, "option \"%s\" not recognized", defel->defname); } @@ -1467,6 +1498,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) } if (parallel_item) procForm->proparallel = interpret_func_parallel(parallel_item); + if (dynres_item) + procForm->prodynres = intVal(dynres_item->arg); if (set_items) { Datum datum; @@ -2144,6 +2177,17 @@ ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic) OidFunctionCall1(laninline, PointerGetDatum(codeblock)); } +static List *procedure_stack; + +Oid +CurrentProcedure(void) +{ + if (!procedure_stack) + return InvalidOid; + else + return llast_oid(procedure_stack); +} + /* * Execute CALL statement * @@ -2183,6 +2227,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver AclResult aclresult; FmgrInfo flinfo; CallContext *callcontext; + int prodynres; EState *estate; ExprContext *econtext; HeapTuple tp; @@ -2223,6 +2268,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef) callcontext->atomic = true; + prodynres = ((Form_pg_proc) GETSTRUCT(tp))->prodynres; + ReleaseSysCache(tp); /* safety check; see ExecInitFunc() */ @@ -2283,7 +2330,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver /* Here we actually call the procedure */ pgstat_init_function_usage(fcinfo, &fcusage); - retval = FunctionCallInvoke(fcinfo); + + procedure_stack = lappend_oid(procedure_stack, fexpr->funcid); + PG_TRY(); + { + retval = FunctionCallInvoke(fcinfo); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + pgstat_end_function_usage(&fcusage, true); /* Handle the procedure's outputs */ @@ -2344,6 +2402,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver fexpr->funcresulttype); FreeExecutorState(estate); + + CloseOtherReturnableCursors(fexpr->funcid); + + if (list_length(GetReturnableCursors()) > prodynres) + ereport(WARNING, + errcode(ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS), + errmsg("attempt to return too many result sets")); } /* diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c index 8a3cf98cce..e73f7bfb22 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -24,6 +24,7 @@ #include <limits.h> #include "access/xact.h" +#include "commands/defrem.h" #include "commands/portalcmds.h" #include "executor/executor.h" #include "executor/tstoreReceiver.h" @@ -140,6 +141,28 @@ PerformCursorOpen(ParseState *pstate, DeclareCursorStmt *cstmt, ParamListInfo pa portal->cursorOptions |= CURSOR_OPT_NO_SCROLL; } + /* + * For returnable cursors, remember the currently active procedure, as + * well as the command ID, so we can sort by creation order later. If + * there is no procedure active, the cursor is marked as WITHOUT RETURN. + * (This is not an error, per SQL standard, subclause "Effect of opening a + * cursor".) + */ + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + Oid procId = CurrentProcedure(); + + if (procId) + { + portal->procId = procId; + portal->createCid = GetCurrentCommandId(true); + } + else + { + portal->cursorOptions &= ~CURSOR_OPT_RETURN; + } + } + /* * Start execution, inserting parameters if any. */ diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 04bddaef81..1e40fcedd3 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1777,7 +1777,8 @@ makeRangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructors internally-dependent on the range type so @@ -1842,7 +1843,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructor internally-dependent on the multirange type so @@ -1886,7 +1888,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); @@ -1924,7 +1927,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a0138382a1..8312fbf2c6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P - DOUBLE_P DROP + DOUBLE_P DROP DYNAMIC EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION @@ -735,7 +735,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESTART RESTRICT RESULT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -8532,6 +8532,10 @@ common_func_opt_item: { $$ = makeDefElem("parallel", (Node *) makeString($2), @1); } + | DYNAMIC RESULT SETS Iconst + { + $$ = makeDefElem("dynamic_result_sets", (Node *)makeInteger($4), @1); + } ; createfunc_opt_item: @@ -12421,6 +12425,12 @@ cursor_options: /*EMPTY*/ { $$ = 0; } opt_hold: /* EMPTY */ { $$ = 0; } | WITH HOLD { $$ = CURSOR_OPT_HOLD; } | WITHOUT HOLD { $$ = 0; } + | WITH HOLD WITH RETURN { $$ = CURSOR_OPT_HOLD | CURSOR_OPT_RETURN; } + | WITHOUT HOLD WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITH HOLD WITHOUT RETURN { $$ = CURSOR_OPT_HOLD; } + | WITHOUT HOLD WITHOUT RETURN { $$ = 0; } + | WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITHOUT RETURN { $$ = 0; } ; /***************************************************************************** @@ -16787,6 +16797,7 @@ unreserved_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ENABLE_P | ENCODING @@ -16932,6 +16943,7 @@ unreserved_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE @@ -17332,6 +17344,7 @@ bare_label_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ELSE | ENABLE_P @@ -17519,6 +17532,7 @@ bare_label_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index cab709b07b..98ac9aa012 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -32,6 +32,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/async.h" +#include "commands/defrem.h" #include "commands/prepare.h" #include "common/pg_prng.h" #include "jit/jit.h" @@ -1073,6 +1074,7 @@ exec_simple_query(const char *query_string) int16 format; const char *cmdtagname; size_t cmdtaglen; + ListCell *lc; pgstat_report_query_id(0, true); @@ -1235,7 +1237,7 @@ exec_simple_query(const char *query_string) MemoryContextSwitchTo(oldcontext); /* - * Run the portal to completion, and then drop it (and the receiver). + * Run the portal to completion, and then drop it. */ (void) PortalRun(portal, FETCH_ALL, @@ -1245,10 +1247,34 @@ exec_simple_query(const char *query_string) receiver, &qc); - receiver->rDestroy(receiver); - PortalDrop(portal, false); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dynportal = lfirst(lc); + + if (dest == DestRemote) + SetRemoteDestReceiverParams(receiver, dynportal); + + PortalRun(dynportal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dynportal, false); + } + + /* + * Drop the receiver. + */ + receiver->rDestroy(receiver); + if (lnext(parsetree_list, parsetree_item) == NULL) { /* @@ -2200,6 +2226,11 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); + if (GetReturnableCursors()) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("dynamic result sets are not yet supported in extended query protocol")); + receiver->rDestroy(receiver); /* Done executing; remove the params error callback */ diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 3d244af130..9b94a5fa92 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -83,6 +83,7 @@ Section: Class 01 - Warning # do not use this class for failure conditions 01000 W ERRCODE_WARNING warning 0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED dynamic_result_sets_returned +0100E W ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS attempt_to_return_too_many_result_sets 01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING implicit_zero_bit_padding 01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION null_value_eliminated_in_set_function 01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED privilege_not_granted diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c index 06dfa85f04..f29a6eabf8 100644 --- a/src/backend/utils/mmgr/portalmem.c +++ b/src/backend/utils/mmgr/portalmem.c @@ -1289,3 +1289,51 @@ ForgetPortalSnapshots(void) elog(ERROR, "portal snapshots (%d) did not account for all active snapshots (%d)", numPortalSnaps, numActiveSnaps); } + +static int +cmp_portals_by_creation(const ListCell *a, const ListCell *b) +{ + Portal pa = lfirst(a); + Portal pb = lfirst(b); + + return pa->createCid - pb->createCid; +} + +List * +GetReturnableCursors(void) +{ + List *ret = NIL; + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN) + ret = lappend(ret, portal); + } + + list_sort(ret, cmp_portals_by_creation); + + return ret; +} + +void +CloseOtherReturnableCursors(Oid procid) +{ + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN && portal->procId != procid) + PortalDrop(portal, false); + } +} diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 527c7651ab..659c5da25a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11647,6 +11647,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) char *prorows; char *prosupport; char *proparallel; + int prodynres; char *lanname; char **configitems = NULL; int nconfigitems = 0; @@ -11714,10 +11715,17 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (fout->remoteVersion >= 140000) appendPQExpBufferStr(query, - "pg_get_function_sqlbody(p.oid) AS prosqlbody\n"); + "pg_get_function_sqlbody(p.oid) AS prosqlbody,\n"); else appendPQExpBufferStr(query, - "NULL AS prosqlbody\n"); + "NULL AS prosqlbody,\n"); + + if (fout->remoteVersion >= 160000) + appendPQExpBufferStr(query, + "prodynres\n"); + else + appendPQExpBufferStr(query, + "0 AS prodynres\n"); appendPQExpBufferStr(query, "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n" @@ -11762,6 +11770,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows")); prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport")); proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel")); + prodynres = atoi(PQgetvalue(res, 0, PQfnumber(res, "prodynres"))); lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname")); /* @@ -11880,6 +11889,9 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (proisstrict[0] == 't') appendPQExpBufferStr(q, " STRICT"); + if (prodynres > 0) + appendPQExpBuffer(q, " DYNAMIC RESULT SETS %d", prodynres); + if (prosecdef[0] == 't') appendPQExpBufferStr(q, " SECURITY DEFINER"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index e7abe0b497..f4ef8f0ece 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -76,6 +76,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce /* see PROPARALLEL_ categories below */ char proparallel BKI_DEFAULT(s); + /* maximum number of dynamic result sets */ + int32 prodynres BKI_DEFAULT(0); + /* number of arguments */ /* Note: need not be given in pg_proc.dat; genbki.pl will compute it */ int16 pronargs; @@ -211,7 +214,8 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows); + float4 prorows, + int dynres); extern bool function_parse_error_transpose(const char *prosrc); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 4f7f87fc62..fcfe8df78e 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -57,6 +57,7 @@ extern ObjectAddress CreateTransform(CreateTransformStmt *stmt); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, oidvector *proargtypes, Oid nspOid); extern void ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic); +extern Oid CurrentProcedure(void); extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest); extern TupleDesc CallStmtResultDesc(CallStmt *stmt); extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f7d7f10f7d..acae7da708 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3010,6 +3010,7 @@ typedef struct SecLabelStmt #define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */ #define CURSOR_OPT_ASENSITIVE 0x0010 /* ASENSITIVE */ #define CURSOR_OPT_HOLD 0x0020 /* WITH HOLD */ +#define CURSOR_OPT_RETURN 0x0040 /* WITH RETURN */ /* these planner-control flags do not correspond to any SQL grammar: */ #define CURSOR_OPT_FAST_PLAN 0x0100 /* prefer fast-start plan */ #define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index bb36213e6f..60457d21f7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -144,6 +144,7 @@ PG_KEYWORD("document", DOCUMENT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL) @@ -353,6 +354,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("result", RESULT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index aa08b1e0fc..6f04362dfe 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -132,6 +132,16 @@ typedef struct PortalData SubTransactionId activeSubid; /* the last subxact with activity */ int createLevel; /* creating subxact's nesting level */ + /* + * Procedure that created this portal. Used for returnable cursors. + */ + Oid procId; + /* + * Command ID where the portal was created. Used for sorting returnable + * cursors into creation order. + */ + CommandId createCid; + /* The query or queries the portal will execute */ const char *sourceText; /* text of query (as of 8.4, never NULL) */ CommandTag commandTag; /* command tag for original query */ @@ -248,5 +258,7 @@ extern void PortalHashTableDeleteAll(void); extern bool ThereAreNoReadyPortals(void); extern void HoldPinnedPortals(void); extern void ForgetPortalSnapshots(void); +extern List *GetReturnableCursors(void); +extern void CloseOtherReturnableCursors(Oid procid); #endif /* PORTAL_H */ diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out index 1ec6182a8d..1c8872a754 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_call.out +++ b/src/pl/plpgsql/src/expected/plpgsql_call.out @@ -466,3 +466,81 @@ BEGIN END; $$; NOTICE: <NULL> +-- dynamic result sets +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2; + c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; +CALL pdrstest1(1); + ay +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest1(2); + ay +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3; + END IF; +END; +$$; +CALL pdrstest2(1); + ax +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest2(2); + ax +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DROP TABLE cp_test2, cp_test3; diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ffd6d2e3bc..ea11144f6d 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4776,6 +4776,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) elog(ERROR, "could not open cursor: %s", SPI_result_code_string(SPI_result)); + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + portal->procId = estate->func->fn_oid; + portal->createCid = GetCurrentCommandId(true); + } + /* * If cursor variable was NULL, store the generated portal name in it, * after verifying it's okay to assign to. diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index edeb72c380..bff1557005 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -212,7 +212,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <datum> getdiag_target %type <ival> getdiag_item -%type <ival> opt_scrollable +%type <ival> opt_scrollable opt_with_return %type <fetch> opt_fetch_direction %type <ival> opt_transaction_chain @@ -352,6 +352,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_WARNING %token <keyword> K_WHEN %token <keyword> K_WHILE +%token <keyword> K_WITH +%token <keyword> K_WITHOUT %% @@ -529,7 +531,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull plpgsql_ns_additem($4->itemtype, $4->itemno, $1.name); } - | decl_varname opt_scrollable K_CURSOR + | decl_varname opt_scrollable K_CURSOR opt_with_return { plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); } decl_cursor_args decl_is_for decl_cursor_query { @@ -546,12 +548,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull NULL), true); - new->cursor_explicit_expr = $7; - if ($5 == NULL) + new->cursor_explicit_expr = $8; + if ($6 == NULL) new->cursor_explicit_argrow = -1; else - new->cursor_explicit_argrow = $5->dno; - new->cursor_options = CURSOR_OPT_FAST_PLAN | $2; + new->cursor_explicit_argrow = $6->dno; + new->cursor_options = CURSOR_OPT_FAST_PLAN | $2 | $4; } ; @@ -569,6 +571,20 @@ opt_scrollable : } ; +opt_with_return : + { + $$ = 0; + } + | K_WITH K_RETURN + { + $$ = CURSOR_OPT_RETURN; + } + | K_WITHOUT K_RETURN + { + $$ = 0; + } + ; + decl_cursor_query : { $$ = read_sql_stmt(); @@ -1976,6 +1992,10 @@ stmt_execsql : K_IMPORT { $$ = make_execsql_stmt(K_MERGE, @1); } + | K_WITH + { + $$ = make_execsql_stmt(K_WITH, @1); + } | T_WORD { int tok; @@ -2098,6 +2118,30 @@ stmt_open : K_OPEN cursor_variable tok = yylex(); } + /* same for opt_with_return */ + if (tok_is_keyword(tok, &yylval, + K_WITH, "with")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= CURSOR_OPT_RETURN; + tok = yylex(); + } + } + else if (tok_is_keyword(tok, &yylval, + K_WITHOUT, "without")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= 0; + tok = yylex(); + } + } + if (tok != K_FOR) yyerror("syntax error, expected \"FOR\""); @@ -2552,6 +2596,8 @@ unreserved_keyword : | K_USE_VARIABLE | K_VARIABLE_CONFLICT | K_WARNING + | K_WITH + | K_WITHOUT ; %% diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index 466bdc7a20..8a8f8ea47a 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -109,3 +109,5 @@ PG_KEYWORD("use_column", K_USE_COLUMN) PG_KEYWORD("use_variable", K_USE_VARIABLE) PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT) PG_KEYWORD("warning", K_WARNING) +PG_KEYWORD("with", K_WITH) +PG_KEYWORD("without", K_WITHOUT) diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql index 5028398348..9ace9d4e06 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_call.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql @@ -436,3 +436,49 @@ CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL) RAISE NOTICE '%', v_Text; END; $$; + + +-- dynamic result sets + +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); + +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2; + c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; + +CALL pdrstest1(1); +CALL pdrstest1(2); + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3; + END IF; +END; +$$; + +CALL pdrstest2(1); +CALL pdrstest2(2); + +DROP TABLE cp_test2, cp_test3; diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index f2a677fa55..8cc009d1b2 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -375,9 +375,107 @@ ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1; ALTER ROUTINE ptest1(text) RENAME TO ptest1a; ALTER ROUTINE ptest1a RENAME TO ptest1; DROP ROUTINE cp_testfunc1(int); +-- dynamic result sets +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +$$; +CALL pdrstest1(); + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest1() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2; +$$; +CALL pdrstest2(); + a +--- + 1 +(1 row) + +CALL pdrstest2() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +CREATE PROCEDURE pdrstest3(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +SELECT a || a; +$$; +CALL pdrstest3('x'); + a +---- + xx +(1 row) + + a +--- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest3($1) \bind 'y' \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- test the nested error handling +CREATE TABLE cp_test_dummy (a int); +CREATE PROCEDURE pdrstest4a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; +$$; +CREATE PROCEDURE pdrstest4b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest4a(); +$$; +DROP TABLE cp_test_dummy; +CALL pdrstest4b(); +ERROR: relation "cp_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; + +CONTEXT: SQL function "pdrstest4a" during startup +SQL function "pdrstest4b" statement 1 +CALL pdrstest4b() \bind \g +ERROR: relation "cp_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; + +CONTEXT: SQL function "pdrstest4a" during startup +SQL function "pdrstest4b" statement 1 -- cleanup DROP PROCEDURE ptest1; DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; -DROP TABLE cp_test; +DROP TABLE cp_test, cp_test2, cp_test3; DROP USER regress_cp_user1; diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index 35b872779e..b79a312f62 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -242,12 +242,75 @@ CREATE USER regress_cp_user1; DROP ROUTINE cp_testfunc1(int); +-- dynamic result sets + +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); + +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +$$; + +CALL pdrstest1(); +CALL pdrstest1() \bind \g + +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2; +$$; + +CALL pdrstest2(); +CALL pdrstest2() \bind \g + +CREATE PROCEDURE pdrstest3(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +SELECT a || a; +$$; + +CALL pdrstest3('x'); +CALL pdrstest3($1) \bind 'y' \g + +-- test the nested error handling +CREATE TABLE cp_test_dummy (a int); + +CREATE PROCEDURE pdrstest4a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; +$$; + +CREATE PROCEDURE pdrstest4b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest4a(); +$$; + +DROP TABLE cp_test_dummy; + +CALL pdrstest4b(); +CALL pdrstest4b() \bind \g + + -- cleanup DROP PROCEDURE ptest1; DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; -DROP TABLE cp_test; +DROP TABLE cp_test, cp_test2, cp_test3; DROP USER regress_cp_user1; base-commit: 2cb82e2acfba069d00c6bd253d58df03d315672a -- 2.39.2 From 43eab75a74b841459489f5b532d80482a97ab7f2 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Mon, 20 Feb 2023 12:57:33 +0100 Subject: [PATCH v7 2/2] WIP: Dynamic result sets in extended query protocol This is currently broken due to/since acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659. TODO: consider minor protocol version bump (3.1) --- doc/src/sgml/protocol.sgml | 19 +++++++++++ src/backend/tcop/postgres.c | 32 ++++++++++++++++--- src/backend/tcop/pquery.c | 6 ++++ src/include/utils/portal.h | 2 ++ src/interfaces/libpq/fe-protocol3.c | 6 ++-- .../regress/expected/create_procedure.out | 22 +++++++++++-- 6 files changed, 76 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 93fc7167d4..ec605b12b5 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -959,6 +959,25 @@ <title>Extended Query</title> an empty query string), ErrorResponse, or PortalSuspended. </para> + <para> + Executing a portal may give rise to a <firstterm>dynamic result set + sequence</firstterm>. That means the command contained in the portal + created additional result sets beyond what it normally returns. (The + typical example is calling a stored procedure that creates dynamic result + sets.) Dynamic result sets are issued after whatever response the main + command issued. Each dynamic result set begins with a RowDescription + message followed by zero or more DataRow messages. (Since, as explained + above, an Execute message normally does not respond with a RowDescription, + the appearance of the first RowDescription marks the end of the primary + result set of the portal and the beginning of the first dynamic result + set.) The CommandComplete message that concludes the Execute message + response follows <emphasis>after</emphasis> all dynamic result sets. Note + that dynamic result sets cannot, by their nature, be decribed prior to the + execution of the portal. Multiple executions of the same prepared + statement could result in dynamic result sets with different row + descriptions being returned. + </para> + <para> At completion of each series of extended-query messages, the frontend should issue a Sync message. This parameterless message causes the diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 98ac9aa012..89da5c7512 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -2086,6 +2086,7 @@ exec_execute_message(const char *portal_name, long max_rows) const char *sourceText; const char *prepStmtName; ParamListInfo portalParams; + ListCell *lc; bool save_log_statement_stats = log_statement_stats; bool is_xact_command; bool execute_is_fetch; @@ -2226,10 +2227,33 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); - if (GetReturnableCursors()) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("dynamic result sets are not yet supported in extended query protocol")); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dyn_portal = lfirst(lc); + + if (dest == DestRemoteExecute) + SetRemoteDestReceiverParams(receiver, dyn_portal); + + PortalSetResultFormat(dyn_portal, 1, &portal->dynamic_format); + + SendRowDescriptionMessage(&row_description_buf, + dyn_portal->tupDesc, + FetchPortalTargetList(dyn_portal), + dyn_portal->formats); + + PortalRun(dyn_portal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dyn_portal, false); + } receiver->rDestroy(receiver); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5f0248acc5..6469940935 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -641,6 +641,8 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) errmsg("bind message has %d result formats but query has %d columns", nFormats, natts))); memcpy(portal->formats, formats, natts * sizeof(int16)); + + portal->dynamic_format = 0; } else if (nFormats > 0) { @@ -649,12 +651,16 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) for (i = 0; i < natts; i++) portal->formats[i] = format1; + + portal->dynamic_format = format1; } else { /* use default format for all columns */ for (i = 0; i < natts; i++) portal->formats[i] = 0; + + portal->dynamic_format = 0; } } diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index 6f04362dfe..55406b8654 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -170,6 +170,8 @@ typedef struct PortalData TupleDesc tupDesc; /* descriptor for result tuples */ /* and these are the format codes to use for the columns: */ int16 *formats; /* a format code for each column */ + /* Format code for dynamic result sets */ + int16 dynamic_format; /* * Outermost ActiveSnapshot for execution of the portal's queries. For diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..863a09cf74 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -320,10 +320,8 @@ pqParseInput3(PGconn *conn) { /* * A new 'T' message is treated as the start of - * another PGresult. (It is not clear that this is - * really possible with the current backend.) We stop - * parsing until the application accepts the current - * result. + * another PGresult. We stop parsing until the + * application accepts the current result. */ conn->asyncStatus = PGASYNC_READY; return; diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 8cc009d1b2..9cec033efb 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -402,7 +402,14 @@ CALL pdrstest1(); (2 rows) CALL pdrstest1() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) CREATE PROCEDURE pdrstest2() LANGUAGE SQL DYNAMIC RESULT SETS 1 @@ -417,7 +424,11 @@ CALL pdrstest2(); (1 row) CALL pdrstest2() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 +(1 row) + CREATE PROCEDURE pdrstest3(INOUT a text) LANGUAGE SQL DYNAMIC RESULT SETS 1 @@ -439,7 +450,12 @@ CALL pdrstest3('x'); (3 rows) CALL pdrstest3($1) \bind 'y' \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +---- + yy +(1 row) + +server sent data ("D" message) without prior row description ("T" message) -- test the nested error handling CREATE TABLE cp_test_dummy (a int); CREATE PROCEDURE pdrstest4a() -- 2.39.2 Attachments: [text/plain] v7-0001-Dynamic-result-sets-from-procedures.patch (48.8K, ../../[email protected]/2-v7-0001-Dynamic-result-sets-from-procedures.patch) download | inline diff: From 1e22417c8cfa6aa230a21a6aa25b166b3b4bbecb Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Mon, 20 Feb 2023 12:09:24 +0100 Subject: [PATCH v7 1/2] Dynamic result sets from procedures Declaring a cursor WITH RETURN in a procedure makes the cursor's data be returned as a result of the CALL invocation. The procedure needs to be declared with the DYNAMIC RESULT SETS attribute. Discussion: https://www.postgresql.org/message-id/flat/[email protected] --- doc/src/sgml/catalogs.sgml | 10 ++ doc/src/sgml/information_schema.sgml | 3 +- doc/src/sgml/plpgsql.sgml | 27 ++++- doc/src/sgml/ref/alter_procedure.sgml | 12 +++ doc/src/sgml/ref/create_procedure.sgml | 14 +++ doc/src/sgml/ref/declare.sgml | 34 +++++- src/backend/catalog/information_schema.sql | 2 +- src/backend/catalog/pg_aggregate.c | 3 +- src/backend/catalog/pg_proc.c | 4 +- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/functioncmds.c | 79 ++++++++++++-- src/backend/commands/portalcmds.c | 23 ++++ src/backend/commands/typecmds.c | 12 ++- src/backend/parser/gram.y | 18 +++- src/backend/tcop/postgres.c | 37 ++++++- src/backend/utils/errcodes.txt | 1 + src/backend/utils/mmgr/portalmem.c | 48 +++++++++ src/bin/pg_dump/pg_dump.c | 16 ++- src/include/catalog/pg_proc.h | 6 +- src/include/commands/defrem.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 2 + src/include/utils/portal.h | 12 +++ src/pl/plpgsql/src/expected/plpgsql_call.out | 78 ++++++++++++++ src/pl/plpgsql/src/pl_exec.c | 6 ++ src/pl/plpgsql/src/pl_gram.y | 58 ++++++++-- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 2 + src/pl/plpgsql/src/sql/plpgsql_call.sql | 46 ++++++++ .../regress/expected/create_procedure.out | 100 +++++++++++++++++- src/test/regress/sql/create_procedure.sql | 65 +++++++++++- 30 files changed, 685 insertions(+), 37 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054..5baec4dc3a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6041,6 +6041,16 @@ <title><structname>pg_proc</structname> Columns</title> </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>prodynres</structfield> <type>int4</type> + </para> + <para> + For procedures, this records the maximum number of dynamic result sets + the procedure may create. Otherwise zero. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>pronargs</structfield> <type>int2</type> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 350c75bc31..5fc9dc22ae 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -5885,7 +5885,8 @@ <title><structname>routines</structname> Columns</title> <structfield>max_dynamic_result_sets</structfield> <type>cardinal_number</type> </para> <para> - Applies to a feature not available in <productname>PostgreSQL</productname> + For a procedure, the maximum number of dynamic result sets. Otherwise + zero. </para></entry> </row> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 8897a5450a..0c0d77b0e6 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3128,7 +3128,7 @@ <title>Declaring Cursor Variables</title> Another way is to use the cursor declaration syntax, which in general is: <synopsis> -<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; +<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> <optional> WITH RETURN </optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; </synopsis> (<literal>FOR</literal> can be replaced by <literal>IS</literal> for <productname>Oracle</productname> compatibility.) @@ -3136,6 +3136,10 @@ <title>Declaring Cursor Variables</title> scrolling backward; if <literal>NO SCROLL</literal> is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. + If <literal>WITH RETURN</literal> is specified, the results of the + cursor, after it is opened, will be returned as a dynamic result set; see + <xref linkend="sql-declare"/> for details. (<literal>WITHOUT + RETURN</literal> can also be specified but has no effect.) <replaceable>arguments</replaceable>, if specified, is a comma-separated list of pairs <literal><replaceable>name</replaceable> <replaceable>datatype</replaceable></literal> that define names to be @@ -3215,7 +3219,7 @@ <title>Opening Cursors</title> <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> <synopsis> -OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>; +OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> <optional> WITH RETURN </optional> FOR <replaceable>query</replaceable>; </synopsis> <para> @@ -3233,8 +3237,9 @@ <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> substituted is the one it has at the time of the <command>OPEN</command>; subsequent changes to the variable will not affect the cursor's behavior. - The <literal>SCROLL</literal> and <literal>NO SCROLL</literal> - options have the same meanings as for a bound cursor. + The options <literal>SCROLL</literal>, <literal>NO SCROLL</literal>, + and <literal>WITH RETURN</literal> have the same meanings as for a + bound cursor. </para> <para> @@ -3612,6 +3617,20 @@ <title>Returning Cursors</title> COMMIT; </programlisting> </para> + + <note> + <para> + Returning a cursor from a function as described here is a separate + mechanism from declaring a cursor <literal>WITH RETURN</literal>, + which automatically produces a result set for the client if the + cursor is left open when returning from the procedure. Both + mechanisms can be used to achieve similar effects. The differences + are mainly how the client application prefers to manage the cursors. + Furthermore, other SQL implementations have other programming models + that might map more easily to one or the other mechanism when doing a + migration. + </para> + </note> </sect3> </sect2> diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index a4737a3439..2cdda7730e 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -34,6 +34,7 @@ <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> + DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT } SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT @@ -158,6 +159,17 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Changes the dynamic result sets setting of the procedure. See <xref + linkend="sql-createprocedure"/> for more information. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term> <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term> diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 03a14c8684..1c99b00eef 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -24,6 +24,7 @@ CREATE [ OR REPLACE ] PROCEDURE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] ) { LANGUAGE <replaceable class="parameter">lang_name</replaceable> + | DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } @@ -176,6 +177,19 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Specifies how many dynamic result sets the procedure returns (see + <literal><link linkend="sql-declare">DECLARE</link> WITH + RETURN</literal>). The default is 0. If a procedure returns more + result sets than declared, a warning is raised. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term> diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 5712825314..206ed760d9 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -32,7 +32,8 @@ <refsynopsisdiv> <synopsis> DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] - CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> + CURSOR [ { WITH | WITHOUT } HOLD ] [ { WITH | WITHOUT } RETURN ] + FOR <replaceable class="parameter">query</replaceable> </synopsis> </refsynopsisdiv> @@ -138,6 +139,22 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>WITH RETURN</literal></term> + <term><literal>WITHOUT RETURN</literal></term> + <listitem> + <para> + This option is only valid for cursors defined inside a procedure. + <literal>WITH RETURN</literal> specifies that the cursor's result rows + will be provided as a result set of the procedure invocation. To + accomplish that, the cursor must be left open at the end of the + procedure. If multiple <literal>WITH RETURN</literal> cursors are + declared, then their results will be returned in the order they were + created. <literal>WITHOUT RETURN</literal> is the default. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="parameter">query</replaceable></term> <listitem> @@ -339,6 +356,21 @@ <title>Examples</title> See <xref linkend="sql-fetch"/> for more examples of cursor usage. </para> + + <para> + This example shows how to return multiple result sets from a procedure: +<programlisting> +CREATE PROCEDURE test() +LANGUAGE SQL +AS $$ +DECLARE a CURSOR WITH RETURN FOR SELECT * FROM tbl1; +DECLARE b CURSOR WITH RETURN FOR SELECT * FROM tbl2; +$$; + +CALL test(); +</programlisting> + The results of the two cursors will be returned in order from this call. + </para> </refsect1> <refsect1> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..871a27b84b 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1593,7 +1593,7 @@ CREATE VIEW routines AS CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, CAST('YES' AS yes_or_no) AS schema_level_routine, - CAST(0 AS cardinal_number) AS max_dynamic_result_sets, + CAST(p.prodynres AS cardinal_number) AS max_dynamic_result_sets, CAST(null AS yes_or_no) AS is_user_defined_cast, CAST(null AS yes_or_no) AS is_implicitly_invocable, CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type, diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index ebc4454743..a633bb7501 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -640,7 +640,8 @@ AggregateCreate(const char *aggName, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* no prosupport */ 1, /* procost */ - 0); /* prorows */ + 0, /* prorows */ + 0); /* prodynres */ procOid = myself.objectId; /* diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 14d552fe2d..620fb80a9c 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -95,7 +95,8 @@ ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows) + float4 prorows, + int dynres) { Oid retval; int parameterCount; @@ -314,6 +315,7 @@ ProcedureCreate(const char *procedureName, values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet); values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility); values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel); + values[Anum_pg_proc_prodynres - 1] = Int32GetDatum(dynres); values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount); values[Anum_pg_proc_pronargdefaults - 1] = UInt16GetDatum(list_length(parameterDefaults)); values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType); diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 3766762ae3..c12fc07cd0 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -486,7 +486,7 @@ T433 Multiargument GROUPING function YES T434 GROUP BY DISTINCT YES T441 ABS and MOD functions YES T461 Symmetric BETWEEN predicate YES -T471 Result sets return value NO +T471 Result sets return value NO partially supported T472 DESCRIBE CURSOR NO T491 LATERAL derived table YES T495 Combined data change and retrieval NO different syntax diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 69f66dfe7d..5200a92fa9 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -72,6 +72,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/portal.h" #include "utils/rel.h" #include "utils/snapmgr.h" #include "utils/syscache.h" @@ -513,7 +514,8 @@ compute_common_attribute(ParseState *pstate, DefElem **cost_item, DefElem **rows_item, DefElem **support_item, - DefElem **parallel_item) + DefElem **parallel_item, + DefElem **dynres_item) { if (strcmp(defel->defname, "volatility") == 0) { @@ -589,12 +591,28 @@ compute_common_attribute(ParseState *pstate, *parallel_item = defel; } + else if (strcmp(defel->defname, "dynamic_result_sets") == 0) + { + if (!is_procedure) + goto function_error; + if (*dynres_item) + errorConflictingDefElem(defel, pstate); + + *dynres_item = defel; + } else return false; /* Recognized an option */ return true; +function_error: + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in function definition"), + parser_errposition(pstate, defel->location))); + return false; + procedure_error: ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), @@ -731,7 +749,8 @@ compute_function_attributes(ParseState *pstate, float4 *procost, float4 *prorows, Oid *prosupport, - char *parallel_p) + char *parallel_p, + int *dynres_p) { ListCell *option; DefElem *as_item = NULL; @@ -747,6 +766,7 @@ compute_function_attributes(ParseState *pstate, DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; foreach(option, options) { @@ -792,7 +812,8 @@ compute_function_attributes(ParseState *pstate, &cost_item, &rows_item, &support_item, - ¶llel_item)) + ¶llel_item, + &dynres_item)) { /* recognized common option */ continue; @@ -840,6 +861,11 @@ compute_function_attributes(ParseState *pstate, *prosupport = interpret_func_support(support_item); if (parallel_item) *parallel_p = interpret_func_parallel(parallel_item); + if (dynres_item) + { + *dynres_p = intVal(dynres_item->arg); + Assert(*dynres_p >= 0); /* enforced by parser */ + } } @@ -1051,6 +1077,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) Form_pg_language languageStruct; List *as_clause; char parallel; + int dynres; /* Convert list of names to a name and namespace */ namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname, @@ -1075,6 +1102,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) prorows = -1; /* indicates not set */ prosupport = InvalidOid; parallel = PROPARALLEL_UNSAFE; + dynres = 0; /* Extract non-default attributes from stmt->options list */ compute_function_attributes(pstate, @@ -1084,7 +1112,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &isWindowFunc, &volatility, &isStrict, &security, &isLeakProof, &proconfig, &procost, &prorows, - &prosupport, ¶llel); + &prosupport, ¶llel, &dynres); if (!language) { @@ -1285,7 +1313,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) PointerGetDatum(proconfig), prosupport, procost, - prorows); + prorows, + dynres); } /* @@ -1362,6 +1391,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; ObjectAddress address; rel = table_open(ProcedureRelationId, RowExclusiveLock); @@ -1405,7 +1435,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) &cost_item, &rows_item, &support_item, - ¶llel_item) == false) + ¶llel_item, + &dynres_item) == false) elog(ERROR, "option \"%s\" not recognized", defel->defname); } @@ -1467,6 +1498,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) } if (parallel_item) procForm->proparallel = interpret_func_parallel(parallel_item); + if (dynres_item) + procForm->prodynres = intVal(dynres_item->arg); if (set_items) { Datum datum; @@ -2144,6 +2177,17 @@ ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic) OidFunctionCall1(laninline, PointerGetDatum(codeblock)); } +static List *procedure_stack; + +Oid +CurrentProcedure(void) +{ + if (!procedure_stack) + return InvalidOid; + else + return llast_oid(procedure_stack); +} + /* * Execute CALL statement * @@ -2183,6 +2227,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver AclResult aclresult; FmgrInfo flinfo; CallContext *callcontext; + int prodynres; EState *estate; ExprContext *econtext; HeapTuple tp; @@ -2223,6 +2268,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef) callcontext->atomic = true; + prodynres = ((Form_pg_proc) GETSTRUCT(tp))->prodynres; + ReleaseSysCache(tp); /* safety check; see ExecInitFunc() */ @@ -2283,7 +2330,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver /* Here we actually call the procedure */ pgstat_init_function_usage(fcinfo, &fcusage); - retval = FunctionCallInvoke(fcinfo); + + procedure_stack = lappend_oid(procedure_stack, fexpr->funcid); + PG_TRY(); + { + retval = FunctionCallInvoke(fcinfo); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + pgstat_end_function_usage(&fcusage, true); /* Handle the procedure's outputs */ @@ -2344,6 +2402,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver fexpr->funcresulttype); FreeExecutorState(estate); + + CloseOtherReturnableCursors(fexpr->funcid); + + if (list_length(GetReturnableCursors()) > prodynres) + ereport(WARNING, + errcode(ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS), + errmsg("attempt to return too many result sets")); } /* diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c index 8a3cf98cce..e73f7bfb22 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -24,6 +24,7 @@ #include <limits.h> #include "access/xact.h" +#include "commands/defrem.h" #include "commands/portalcmds.h" #include "executor/executor.h" #include "executor/tstoreReceiver.h" @@ -140,6 +141,28 @@ PerformCursorOpen(ParseState *pstate, DeclareCursorStmt *cstmt, ParamListInfo pa portal->cursorOptions |= CURSOR_OPT_NO_SCROLL; } + /* + * For returnable cursors, remember the currently active procedure, as + * well as the command ID, so we can sort by creation order later. If + * there is no procedure active, the cursor is marked as WITHOUT RETURN. + * (This is not an error, per SQL standard, subclause "Effect of opening a + * cursor".) + */ + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + Oid procId = CurrentProcedure(); + + if (procId) + { + portal->procId = procId; + portal->createCid = GetCurrentCommandId(true); + } + else + { + portal->cursorOptions &= ~CURSOR_OPT_RETURN; + } + } + /* * Start execution, inserting parameters if any. */ diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 04bddaef81..1e40fcedd3 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1777,7 +1777,8 @@ makeRangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructors internally-dependent on the range type so @@ -1842,7 +1843,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructor internally-dependent on the multirange type so @@ -1886,7 +1888,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); @@ -1924,7 +1927,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a0138382a1..8312fbf2c6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P - DOUBLE_P DROP + DOUBLE_P DROP DYNAMIC EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION @@ -735,7 +735,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESTART RESTRICT RESULT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -8532,6 +8532,10 @@ common_func_opt_item: { $$ = makeDefElem("parallel", (Node *) makeString($2), @1); } + | DYNAMIC RESULT SETS Iconst + { + $$ = makeDefElem("dynamic_result_sets", (Node *)makeInteger($4), @1); + } ; createfunc_opt_item: @@ -12421,6 +12425,12 @@ cursor_options: /*EMPTY*/ { $$ = 0; } opt_hold: /* EMPTY */ { $$ = 0; } | WITH HOLD { $$ = CURSOR_OPT_HOLD; } | WITHOUT HOLD { $$ = 0; } + | WITH HOLD WITH RETURN { $$ = CURSOR_OPT_HOLD | CURSOR_OPT_RETURN; } + | WITHOUT HOLD WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITH HOLD WITHOUT RETURN { $$ = CURSOR_OPT_HOLD; } + | WITHOUT HOLD WITHOUT RETURN { $$ = 0; } + | WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITHOUT RETURN { $$ = 0; } ; /***************************************************************************** @@ -16787,6 +16797,7 @@ unreserved_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ENABLE_P | ENCODING @@ -16932,6 +16943,7 @@ unreserved_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE @@ -17332,6 +17344,7 @@ bare_label_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ELSE | ENABLE_P @@ -17519,6 +17532,7 @@ bare_label_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index cab709b07b..98ac9aa012 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -32,6 +32,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/async.h" +#include "commands/defrem.h" #include "commands/prepare.h" #include "common/pg_prng.h" #include "jit/jit.h" @@ -1073,6 +1074,7 @@ exec_simple_query(const char *query_string) int16 format; const char *cmdtagname; size_t cmdtaglen; + ListCell *lc; pgstat_report_query_id(0, true); @@ -1235,7 +1237,7 @@ exec_simple_query(const char *query_string) MemoryContextSwitchTo(oldcontext); /* - * Run the portal to completion, and then drop it (and the receiver). + * Run the portal to completion, and then drop it. */ (void) PortalRun(portal, FETCH_ALL, @@ -1245,10 +1247,34 @@ exec_simple_query(const char *query_string) receiver, &qc); - receiver->rDestroy(receiver); - PortalDrop(portal, false); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dynportal = lfirst(lc); + + if (dest == DestRemote) + SetRemoteDestReceiverParams(receiver, dynportal); + + PortalRun(dynportal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dynportal, false); + } + + /* + * Drop the receiver. + */ + receiver->rDestroy(receiver); + if (lnext(parsetree_list, parsetree_item) == NULL) { /* @@ -2200,6 +2226,11 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); + if (GetReturnableCursors()) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("dynamic result sets are not yet supported in extended query protocol")); + receiver->rDestroy(receiver); /* Done executing; remove the params error callback */ diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 3d244af130..9b94a5fa92 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -83,6 +83,7 @@ Section: Class 01 - Warning # do not use this class for failure conditions 01000 W ERRCODE_WARNING warning 0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED dynamic_result_sets_returned +0100E W ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS attempt_to_return_too_many_result_sets 01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING implicit_zero_bit_padding 01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION null_value_eliminated_in_set_function 01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED privilege_not_granted diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c index 06dfa85f04..f29a6eabf8 100644 --- a/src/backend/utils/mmgr/portalmem.c +++ b/src/backend/utils/mmgr/portalmem.c @@ -1289,3 +1289,51 @@ ForgetPortalSnapshots(void) elog(ERROR, "portal snapshots (%d) did not account for all active snapshots (%d)", numPortalSnaps, numActiveSnaps); } + +static int +cmp_portals_by_creation(const ListCell *a, const ListCell *b) +{ + Portal pa = lfirst(a); + Portal pb = lfirst(b); + + return pa->createCid - pb->createCid; +} + +List * +GetReturnableCursors(void) +{ + List *ret = NIL; + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN) + ret = lappend(ret, portal); + } + + list_sort(ret, cmp_portals_by_creation); + + return ret; +} + +void +CloseOtherReturnableCursors(Oid procid) +{ + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN && portal->procId != procid) + PortalDrop(portal, false); + } +} diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 527c7651ab..659c5da25a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11647,6 +11647,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) char *prorows; char *prosupport; char *proparallel; + int prodynres; char *lanname; char **configitems = NULL; int nconfigitems = 0; @@ -11714,10 +11715,17 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (fout->remoteVersion >= 140000) appendPQExpBufferStr(query, - "pg_get_function_sqlbody(p.oid) AS prosqlbody\n"); + "pg_get_function_sqlbody(p.oid) AS prosqlbody,\n"); else appendPQExpBufferStr(query, - "NULL AS prosqlbody\n"); + "NULL AS prosqlbody,\n"); + + if (fout->remoteVersion >= 160000) + appendPQExpBufferStr(query, + "prodynres\n"); + else + appendPQExpBufferStr(query, + "0 AS prodynres\n"); appendPQExpBufferStr(query, "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n" @@ -11762,6 +11770,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows")); prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport")); proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel")); + prodynres = atoi(PQgetvalue(res, 0, PQfnumber(res, "prodynres"))); lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname")); /* @@ -11880,6 +11889,9 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (proisstrict[0] == 't') appendPQExpBufferStr(q, " STRICT"); + if (prodynres > 0) + appendPQExpBuffer(q, " DYNAMIC RESULT SETS %d", prodynres); + if (prosecdef[0] == 't') appendPQExpBufferStr(q, " SECURITY DEFINER"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index e7abe0b497..f4ef8f0ece 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -76,6 +76,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce /* see PROPARALLEL_ categories below */ char proparallel BKI_DEFAULT(s); + /* maximum number of dynamic result sets */ + int32 prodynres BKI_DEFAULT(0); + /* number of arguments */ /* Note: need not be given in pg_proc.dat; genbki.pl will compute it */ int16 pronargs; @@ -211,7 +214,8 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows); + float4 prorows, + int dynres); extern bool function_parse_error_transpose(const char *prosrc); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 4f7f87fc62..fcfe8df78e 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -57,6 +57,7 @@ extern ObjectAddress CreateTransform(CreateTransformStmt *stmt); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, oidvector *proargtypes, Oid nspOid); extern void ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic); +extern Oid CurrentProcedure(void); extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest); extern TupleDesc CallStmtResultDesc(CallStmt *stmt); extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f7d7f10f7d..acae7da708 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3010,6 +3010,7 @@ typedef struct SecLabelStmt #define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */ #define CURSOR_OPT_ASENSITIVE 0x0010 /* ASENSITIVE */ #define CURSOR_OPT_HOLD 0x0020 /* WITH HOLD */ +#define CURSOR_OPT_RETURN 0x0040 /* WITH RETURN */ /* these planner-control flags do not correspond to any SQL grammar: */ #define CURSOR_OPT_FAST_PLAN 0x0100 /* prefer fast-start plan */ #define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index bb36213e6f..60457d21f7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -144,6 +144,7 @@ PG_KEYWORD("document", DOCUMENT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL) @@ -353,6 +354,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("result", RESULT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index aa08b1e0fc..6f04362dfe 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -132,6 +132,16 @@ typedef struct PortalData SubTransactionId activeSubid; /* the last subxact with activity */ int createLevel; /* creating subxact's nesting level */ + /* + * Procedure that created this portal. Used for returnable cursors. + */ + Oid procId; + /* + * Command ID where the portal was created. Used for sorting returnable + * cursors into creation order. + */ + CommandId createCid; + /* The query or queries the portal will execute */ const char *sourceText; /* text of query (as of 8.4, never NULL) */ CommandTag commandTag; /* command tag for original query */ @@ -248,5 +258,7 @@ extern void PortalHashTableDeleteAll(void); extern bool ThereAreNoReadyPortals(void); extern void HoldPinnedPortals(void); extern void ForgetPortalSnapshots(void); +extern List *GetReturnableCursors(void); +extern void CloseOtherReturnableCursors(Oid procid); #endif /* PORTAL_H */ diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out index 1ec6182a8d..1c8872a754 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_call.out +++ b/src/pl/plpgsql/src/expected/plpgsql_call.out @@ -466,3 +466,81 @@ BEGIN END; $$; NOTICE: <NULL> +-- dynamic result sets +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2; + c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; +CALL pdrstest1(1); + ay +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest1(2); + ay +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3; + END IF; +END; +$$; +CALL pdrstest2(1); + ax +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest2(2); + ax +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DROP TABLE cp_test2, cp_test3; diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ffd6d2e3bc..ea11144f6d 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4776,6 +4776,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) elog(ERROR, "could not open cursor: %s", SPI_result_code_string(SPI_result)); + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + portal->procId = estate->func->fn_oid; + portal->createCid = GetCurrentCommandId(true); + } + /* * If cursor variable was NULL, store the generated portal name in it, * after verifying it's okay to assign to. diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index edeb72c380..bff1557005 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -212,7 +212,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <datum> getdiag_target %type <ival> getdiag_item -%type <ival> opt_scrollable +%type <ival> opt_scrollable opt_with_return %type <fetch> opt_fetch_direction %type <ival> opt_transaction_chain @@ -352,6 +352,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_WARNING %token <keyword> K_WHEN %token <keyword> K_WHILE +%token <keyword> K_WITH +%token <keyword> K_WITHOUT %% @@ -529,7 +531,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull plpgsql_ns_additem($4->itemtype, $4->itemno, $1.name); } - | decl_varname opt_scrollable K_CURSOR + | decl_varname opt_scrollable K_CURSOR opt_with_return { plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); } decl_cursor_args decl_is_for decl_cursor_query { @@ -546,12 +548,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull NULL), true); - new->cursor_explicit_expr = $7; - if ($5 == NULL) + new->cursor_explicit_expr = $8; + if ($6 == NULL) new->cursor_explicit_argrow = -1; else - new->cursor_explicit_argrow = $5->dno; - new->cursor_options = CURSOR_OPT_FAST_PLAN | $2; + new->cursor_explicit_argrow = $6->dno; + new->cursor_options = CURSOR_OPT_FAST_PLAN | $2 | $4; } ; @@ -569,6 +571,20 @@ opt_scrollable : } ; +opt_with_return : + { + $$ = 0; + } + | K_WITH K_RETURN + { + $$ = CURSOR_OPT_RETURN; + } + | K_WITHOUT K_RETURN + { + $$ = 0; + } + ; + decl_cursor_query : { $$ = read_sql_stmt(); @@ -1976,6 +1992,10 @@ stmt_execsql : K_IMPORT { $$ = make_execsql_stmt(K_MERGE, @1); } + | K_WITH + { + $$ = make_execsql_stmt(K_WITH, @1); + } | T_WORD { int tok; @@ -2098,6 +2118,30 @@ stmt_open : K_OPEN cursor_variable tok = yylex(); } + /* same for opt_with_return */ + if (tok_is_keyword(tok, &yylval, + K_WITH, "with")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= CURSOR_OPT_RETURN; + tok = yylex(); + } + } + else if (tok_is_keyword(tok, &yylval, + K_WITHOUT, "without")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= 0; + tok = yylex(); + } + } + if (tok != K_FOR) yyerror("syntax error, expected \"FOR\""); @@ -2552,6 +2596,8 @@ unreserved_keyword : | K_USE_VARIABLE | K_VARIABLE_CONFLICT | K_WARNING + | K_WITH + | K_WITHOUT ; %% diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index 466bdc7a20..8a8f8ea47a 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -109,3 +109,5 @@ PG_KEYWORD("use_column", K_USE_COLUMN) PG_KEYWORD("use_variable", K_USE_VARIABLE) PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT) PG_KEYWORD("warning", K_WARNING) +PG_KEYWORD("with", K_WITH) +PG_KEYWORD("without", K_WITHOUT) diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql index 5028398348..9ace9d4e06 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_call.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql @@ -436,3 +436,49 @@ CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL) RAISE NOTICE '%', v_Text; END; $$; + + +-- dynamic result sets + +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); + +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM cp_test2; + c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; + +CALL pdrstest1(1); +CALL pdrstest1(2); + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM cp_test2; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM cp_test3; + END IF; +END; +$$; + +CALL pdrstest2(1); +CALL pdrstest2(2); + +DROP TABLE cp_test2, cp_test3; diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index f2a677fa55..8cc009d1b2 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -375,9 +375,107 @@ ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1; ALTER ROUTINE ptest1(text) RENAME TO ptest1a; ALTER ROUTINE ptest1a RENAME TO ptest1; DROP ROUTINE cp_testfunc1(int); +-- dynamic result sets +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +$$; +CALL pdrstest1(); + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest1() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2; +$$; +CALL pdrstest2(); + a +--- + 1 +(1 row) + +CALL pdrstest2() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +CREATE PROCEDURE pdrstest3(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +SELECT a || a; +$$; +CALL pdrstest3('x'); + a +---- + xx +(1 row) + + a +--- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest3($1) \bind 'y' \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- test the nested error handling +CREATE TABLE cp_test_dummy (a int); +CREATE PROCEDURE pdrstest4a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; +$$; +CREATE PROCEDURE pdrstest4b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest4a(); +$$; +DROP TABLE cp_test_dummy; +CALL pdrstest4b(); +ERROR: relation "cp_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; + +CONTEXT: SQL function "pdrstest4a" during startup +SQL function "pdrstest4b" statement 1 +CALL pdrstest4b() \bind \g +ERROR: relation "cp_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dum... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; + +CONTEXT: SQL function "pdrstest4a" during startup +SQL function "pdrstest4b" statement 1 -- cleanup DROP PROCEDURE ptest1; DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; -DROP TABLE cp_test; +DROP TABLE cp_test, cp_test2, cp_test3; DROP USER regress_cp_user1; diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index 35b872779e..b79a312f62 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -242,12 +242,75 @@ CREATE USER regress_cp_user1; DROP ROUTINE cp_testfunc1(int); +-- dynamic result sets + +CREATE TABLE cp_test2 (a int); +INSERT INTO cp_test2 VALUES (1), (2), (3); +CREATE TABLE cp_test3 (x text, y text); +INSERT INTO cp_test3 VALUES ('abc', 'def'), ('foo', 'bar'); + +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; +$$; + +CALL pdrstest1(); +CALL pdrstest1() \bind \g + +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM cp_test2 WHERE a < 2; +$$; + +CALL pdrstest2(); +CALL pdrstest2() \bind \g + +CREATE PROCEDURE pdrstest3(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; +SELECT a || a; +$$; + +CALL pdrstest3('x'); +CALL pdrstest3($1) \bind 'y' \g + +-- test the nested error handling +CREATE TABLE cp_test_dummy (a int); + +CREATE PROCEDURE pdrstest4a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM cp_test_dummy; +$$; + +CREATE PROCEDURE pdrstest4b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest4a(); +$$; + +DROP TABLE cp_test_dummy; + +CALL pdrstest4b(); +CALL pdrstest4b() \bind \g + + -- cleanup DROP PROCEDURE ptest1; DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; -DROP TABLE cp_test; +DROP TABLE cp_test, cp_test2, cp_test3; DROP USER regress_cp_user1; base-commit: 2cb82e2acfba069d00c6bd253d58df03d315672a -- 2.39.2 [text/plain] v7-0002-WIP-Dynamic-result-sets-in-extended-query-protoco.patch (7.0K, ../../[email protected]/3-v7-0002-WIP-Dynamic-result-sets-in-extended-query-protoco.patch) download | inline diff: From 43eab75a74b841459489f5b532d80482a97ab7f2 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Mon, 20 Feb 2023 12:57:33 +0100 Subject: [PATCH v7 2/2] WIP: Dynamic result sets in extended query protocol This is currently broken due to/since acb7e4eb6b1c614c68a62fb3a6a5bba1af0a2659. TODO: consider minor protocol version bump (3.1) --- doc/src/sgml/protocol.sgml | 19 +++++++++++ src/backend/tcop/postgres.c | 32 ++++++++++++++++--- src/backend/tcop/pquery.c | 6 ++++ src/include/utils/portal.h | 2 ++ src/interfaces/libpq/fe-protocol3.c | 6 ++-- .../regress/expected/create_procedure.out | 22 +++++++++++-- 6 files changed, 76 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 93fc7167d4..ec605b12b5 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -959,6 +959,25 @@ <title>Extended Query</title> an empty query string), ErrorResponse, or PortalSuspended. </para> + <para> + Executing a portal may give rise to a <firstterm>dynamic result set + sequence</firstterm>. That means the command contained in the portal + created additional result sets beyond what it normally returns. (The + typical example is calling a stored procedure that creates dynamic result + sets.) Dynamic result sets are issued after whatever response the main + command issued. Each dynamic result set begins with a RowDescription + message followed by zero or more DataRow messages. (Since, as explained + above, an Execute message normally does not respond with a RowDescription, + the appearance of the first RowDescription marks the end of the primary + result set of the portal and the beginning of the first dynamic result + set.) The CommandComplete message that concludes the Execute message + response follows <emphasis>after</emphasis> all dynamic result sets. Note + that dynamic result sets cannot, by their nature, be decribed prior to the + execution of the portal. Multiple executions of the same prepared + statement could result in dynamic result sets with different row + descriptions being returned. + </para> + <para> At completion of each series of extended-query messages, the frontend should issue a Sync message. This parameterless message causes the diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 98ac9aa012..89da5c7512 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -2086,6 +2086,7 @@ exec_execute_message(const char *portal_name, long max_rows) const char *sourceText; const char *prepStmtName; ParamListInfo portalParams; + ListCell *lc; bool save_log_statement_stats = log_statement_stats; bool is_xact_command; bool execute_is_fetch; @@ -2226,10 +2227,33 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); - if (GetReturnableCursors()) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("dynamic result sets are not yet supported in extended query protocol")); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dyn_portal = lfirst(lc); + + if (dest == DestRemoteExecute) + SetRemoteDestReceiverParams(receiver, dyn_portal); + + PortalSetResultFormat(dyn_portal, 1, &portal->dynamic_format); + + SendRowDescriptionMessage(&row_description_buf, + dyn_portal->tupDesc, + FetchPortalTargetList(dyn_portal), + dyn_portal->formats); + + PortalRun(dyn_portal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dyn_portal, false); + } receiver->rDestroy(receiver); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5f0248acc5..6469940935 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -641,6 +641,8 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) errmsg("bind message has %d result formats but query has %d columns", nFormats, natts))); memcpy(portal->formats, formats, natts * sizeof(int16)); + + portal->dynamic_format = 0; } else if (nFormats > 0) { @@ -649,12 +651,16 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) for (i = 0; i < natts; i++) portal->formats[i] = format1; + + portal->dynamic_format = format1; } else { /* use default format for all columns */ for (i = 0; i < natts; i++) portal->formats[i] = 0; + + portal->dynamic_format = 0; } } diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index 6f04362dfe..55406b8654 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -170,6 +170,8 @@ typedef struct PortalData TupleDesc tupDesc; /* descriptor for result tuples */ /* and these are the format codes to use for the columns: */ int16 *formats; /* a format code for each column */ + /* Format code for dynamic result sets */ + int16 dynamic_format; /* * Outermost ActiveSnapshot for execution of the portal's queries. For diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..863a09cf74 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -320,10 +320,8 @@ pqParseInput3(PGconn *conn) { /* * A new 'T' message is treated as the start of - * another PGresult. (It is not clear that this is - * really possible with the current backend.) We stop - * parsing until the application accepts the current - * result. + * another PGresult. We stop parsing until the + * application accepts the current result. */ conn->asyncStatus = PGASYNC_READY; return; diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 8cc009d1b2..9cec033efb 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -402,7 +402,14 @@ CALL pdrstest1(); (2 rows) CALL pdrstest1() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) CREATE PROCEDURE pdrstest2() LANGUAGE SQL DYNAMIC RESULT SETS 1 @@ -417,7 +424,11 @@ CALL pdrstest2(); (1 row) CALL pdrstest2() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 +(1 row) + CREATE PROCEDURE pdrstest3(INOUT a text) LANGUAGE SQL DYNAMIC RESULT SETS 1 @@ -439,7 +450,12 @@ CALL pdrstest3('x'); (3 rows) CALL pdrstest3($1) \bind 'y' \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +---- + yy +(1 row) + +server sent data ("D" message) without prior row description ("T" message) -- test the nested error handling CREATE TABLE cp_test_dummy (a int); CREATE PROCEDURE pdrstest4a() -- 2.39.2 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic result sets support in extended query protocol 2023-01-30 13:06 Re: dynamic result sets support in extended query protocol Alvaro Herrera <[email protected]> 2023-01-31 11:07 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> 2023-02-20 12:58 ` Re: dynamic result sets support in extended query protocol Peter Eisentraut <[email protected]> @ 2023-02-24 11:26 ` Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Peter Eisentraut @ 2023-02-24 11:26 UTC (permalink / raw) To: pgsql-hackers; +Cc: Alvaro Herrera <[email protected]> On 20.02.23 13:58, Peter Eisentraut wrote: > The attached patches are the same as before, rebased over master and > split up as described. I haven't done any significant work on the > contents, but I will try to get the 0001 patch into a more polished > state soon. I've done a bit of work on this patch, mainly cleaned up and expanded the tests, and also added DO support, which is something that had been requested (meaning you can return result sets from DO with this facility). Here is a new version. From ada315925d02883833cc5f4bc95477b0217d9d66 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Fri, 24 Feb 2023 12:21:40 +0100 Subject: [PATCH v8 1/2] Dynamic result sets from procedures Declaring a cursor WITH RETURN in a procedure makes the cursor's data be returned as a result of the CALL (or DO) invocation. The procedure needs to be declared with the DYNAMIC RESULT SETS attribute. Discussion: https://www.postgresql.org/message-id/flat/[email protected] --- doc/src/sgml/catalogs.sgml | 10 ++ doc/src/sgml/information_schema.sgml | 3 +- doc/src/sgml/plpgsql.sgml | 27 +++- doc/src/sgml/ref/alter_procedure.sgml | 12 ++ doc/src/sgml/ref/create_procedure.sgml | 14 ++ doc/src/sgml/ref/declare.sgml | 35 ++++- src/backend/catalog/information_schema.sql | 2 +- src/backend/catalog/pg_aggregate.c | 3 +- src/backend/catalog/pg_proc.c | 4 +- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/functioncmds.c | 94 +++++++++++-- src/backend/commands/portalcmds.c | 23 ++++ src/backend/commands/typecmds.c | 12 +- src/backend/parser/gram.y | 18 ++- src/backend/tcop/postgres.c | 37 ++++- src/backend/utils/errcodes.txt | 1 + src/backend/utils/mmgr/portalmem.c | 48 +++++++ src/bin/pg_dump/pg_dump.c | 16 ++- src/include/catalog/pg_proc.h | 6 +- src/include/commands/defrem.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 2 + src/include/utils/portal.h | 12 ++ src/pl/plpgsql/src/Makefile | 2 +- .../src/expected/plpgsql_with_return.out | 105 ++++++++++++++ src/pl/plpgsql/src/meson.build | 1 + src/pl/plpgsql/src/pl_exec.c | 6 + src/pl/plpgsql/src/pl_gram.y | 58 +++++++- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 2 + .../plpgsql/src/sql/plpgsql_with_return.sql | 64 +++++++++ .../regress/expected/dynamic_result_sets.out | 129 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/dynamic_result_sets.sql | 90 ++++++++++++ 33 files changed, 803 insertions(+), 39 deletions(-) create mode 100644 src/pl/plpgsql/src/expected/plpgsql_with_return.out create mode 100644 src/pl/plpgsql/src/sql/plpgsql_with_return.sql create mode 100644 src/test/regress/expected/dynamic_result_sets.out create mode 100644 src/test/regress/sql/dynamic_result_sets.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054..5baec4dc3a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6041,6 +6041,16 @@ <title><structname>pg_proc</structname> Columns</title> </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>prodynres</structfield> <type>int4</type> + </para> + <para> + For procedures, this records the maximum number of dynamic result sets + the procedure may create. Otherwise zero. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>pronargs</structfield> <type>int2</type> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 350c75bc31..5fc9dc22ae 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -5885,7 +5885,8 @@ <title><structname>routines</structname> Columns</title> <structfield>max_dynamic_result_sets</structfield> <type>cardinal_number</type> </para> <para> - Applies to a feature not available in <productname>PostgreSQL</productname> + For a procedure, the maximum number of dynamic result sets. Otherwise + zero. </para></entry> </row> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 8897a5450a..0c0d77b0e6 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3128,7 +3128,7 @@ <title>Declaring Cursor Variables</title> Another way is to use the cursor declaration syntax, which in general is: <synopsis> -<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; +<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> <optional> WITH RETURN </optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; </synopsis> (<literal>FOR</literal> can be replaced by <literal>IS</literal> for <productname>Oracle</productname> compatibility.) @@ -3136,6 +3136,10 @@ <title>Declaring Cursor Variables</title> scrolling backward; if <literal>NO SCROLL</literal> is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. + If <literal>WITH RETURN</literal> is specified, the results of the + cursor, after it is opened, will be returned as a dynamic result set; see + <xref linkend="sql-declare"/> for details. (<literal>WITHOUT + RETURN</literal> can also be specified but has no effect.) <replaceable>arguments</replaceable>, if specified, is a comma-separated list of pairs <literal><replaceable>name</replaceable> <replaceable>datatype</replaceable></literal> that define names to be @@ -3215,7 +3219,7 @@ <title>Opening Cursors</title> <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> <synopsis> -OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>; +OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> <optional> WITH RETURN </optional> FOR <replaceable>query</replaceable>; </synopsis> <para> @@ -3233,8 +3237,9 @@ <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> substituted is the one it has at the time of the <command>OPEN</command>; subsequent changes to the variable will not affect the cursor's behavior. - The <literal>SCROLL</literal> and <literal>NO SCROLL</literal> - options have the same meanings as for a bound cursor. + The options <literal>SCROLL</literal>, <literal>NO SCROLL</literal>, + and <literal>WITH RETURN</literal> have the same meanings as for a + bound cursor. </para> <para> @@ -3612,6 +3617,20 @@ <title>Returning Cursors</title> COMMIT; </programlisting> </para> + + <note> + <para> + Returning a cursor from a function as described here is a separate + mechanism from declaring a cursor <literal>WITH RETURN</literal>, + which automatically produces a result set for the client if the + cursor is left open when returning from the procedure. Both + mechanisms can be used to achieve similar effects. The differences + are mainly how the client application prefers to manage the cursors. + Furthermore, other SQL implementations have other programming models + that might map more easily to one or the other mechanism when doing a + migration. + </para> + </note> </sect3> </sect2> diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index a4737a3439..2cdda7730e 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -34,6 +34,7 @@ <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> + DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT } SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT @@ -158,6 +159,17 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Changes the dynamic result sets setting of the procedure. See <xref + linkend="sql-createprocedure"/> for more information. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term> <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term> diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 03a14c8684..1c99b00eef 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -24,6 +24,7 @@ CREATE [ OR REPLACE ] PROCEDURE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] ) { LANGUAGE <replaceable class="parameter">lang_name</replaceable> + | DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } @@ -176,6 +177,19 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Specifies how many dynamic result sets the procedure returns (see + <literal><link linkend="sql-declare">DECLARE</link> WITH + RETURN</literal>). The default is 0. If a procedure returns more + result sets than declared, a warning is raised. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term> diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 5712825314..a19198e6cb 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -32,7 +32,8 @@ <refsynopsisdiv> <synopsis> DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] - CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> + CURSOR [ { WITH | WITHOUT } HOLD ] [ { WITH | WITHOUT } RETURN ] + FOR <replaceable class="parameter">query</replaceable> </synopsis> </refsynopsisdiv> @@ -138,6 +139,23 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>WITH RETURN</literal></term> + <term><literal>WITHOUT RETURN</literal></term> + <listitem> + <para> + This option is only valid for cursors defined inside a procedure or + <command>DO</command> block. <literal>WITH RETURN</literal> specifies + that the cursor's result rows will be provided as a result set of the + procedure or code block invocation. To accomplish that, the cursor must + be left open at the end of the procedure or code block. If multiple + <literal>WITH RETURN</literal> cursors are declared, then their results + will be returned in the order they were created. <literal>WITHOUT + RETURN</literal> is the default. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="parameter">query</replaceable></term> <listitem> @@ -339,6 +357,21 @@ <title>Examples</title> See <xref linkend="sql-fetch"/> for more examples of cursor usage. </para> + + <para> + This example shows how to return multiple result sets from a procedure: +<programlisting> +CREATE PROCEDURE test() +LANGUAGE SQL +AS $$ +DECLARE a CURSOR WITH RETURN FOR SELECT * FROM tbl1; +DECLARE b CURSOR WITH RETURN FOR SELECT * FROM tbl2; +$$; + +CALL test(); +</programlisting> + The results of the two cursors will be returned in order from this call. + </para> </refsect1> <refsect1> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..871a27b84b 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1593,7 +1593,7 @@ CREATE VIEW routines AS CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, CAST('YES' AS yes_or_no) AS schema_level_routine, - CAST(0 AS cardinal_number) AS max_dynamic_result_sets, + CAST(p.prodynres AS cardinal_number) AS max_dynamic_result_sets, CAST(null AS yes_or_no) AS is_user_defined_cast, CAST(null AS yes_or_no) AS is_implicitly_invocable, CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type, diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index ebc4454743..a633bb7501 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -640,7 +640,8 @@ AggregateCreate(const char *aggName, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* no prosupport */ 1, /* procost */ - 0); /* prorows */ + 0, /* prorows */ + 0); /* prodynres */ procOid = myself.objectId; /* diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 14d552fe2d..620fb80a9c 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -95,7 +95,8 @@ ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows) + float4 prorows, + int dynres) { Oid retval; int parameterCount; @@ -314,6 +315,7 @@ ProcedureCreate(const char *procedureName, values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet); values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility); values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel); + values[Anum_pg_proc_prodynres - 1] = Int32GetDatum(dynres); values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount); values[Anum_pg_proc_pronargdefaults - 1] = UInt16GetDatum(list_length(parameterDefaults)); values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType); diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 75a09f14e0..032bef862d 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -486,7 +486,7 @@ T433 Multiargument GROUPING function YES T434 GROUP BY DISTINCT YES T441 ABS and MOD functions YES T461 Symmetric BETWEEN predicate YES -T471 Result sets return value NO +T471 Result sets return value NO partially supported T472 DESCRIBE CURSOR NO T491 LATERAL derived table YES T495 Combined data change and retrieval NO different syntax diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 69f66dfe7d..fe0a74c4d7 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -72,6 +72,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/portal.h" #include "utils/rel.h" #include "utils/snapmgr.h" #include "utils/syscache.h" @@ -513,7 +514,8 @@ compute_common_attribute(ParseState *pstate, DefElem **cost_item, DefElem **rows_item, DefElem **support_item, - DefElem **parallel_item) + DefElem **parallel_item, + DefElem **dynres_item) { if (strcmp(defel->defname, "volatility") == 0) { @@ -589,12 +591,28 @@ compute_common_attribute(ParseState *pstate, *parallel_item = defel; } + else if (strcmp(defel->defname, "dynamic_result_sets") == 0) + { + if (!is_procedure) + goto function_error; + if (*dynres_item) + errorConflictingDefElem(defel, pstate); + + *dynres_item = defel; + } else return false; /* Recognized an option */ return true; +function_error: + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in function definition"), + parser_errposition(pstate, defel->location))); + return false; + procedure_error: ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), @@ -731,7 +749,8 @@ compute_function_attributes(ParseState *pstate, float4 *procost, float4 *prorows, Oid *prosupport, - char *parallel_p) + char *parallel_p, + int *dynres_p) { ListCell *option; DefElem *as_item = NULL; @@ -747,6 +766,7 @@ compute_function_attributes(ParseState *pstate, DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; foreach(option, options) { @@ -792,7 +812,8 @@ compute_function_attributes(ParseState *pstate, &cost_item, &rows_item, &support_item, - ¶llel_item)) + ¶llel_item, + &dynres_item)) { /* recognized common option */ continue; @@ -840,6 +861,11 @@ compute_function_attributes(ParseState *pstate, *prosupport = interpret_func_support(support_item); if (parallel_item) *parallel_p = interpret_func_parallel(parallel_item); + if (dynres_item) + { + *dynres_p = intVal(dynres_item->arg); + Assert(*dynres_p >= 0); /* enforced by parser */ + } } @@ -1051,6 +1077,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) Form_pg_language languageStruct; List *as_clause; char parallel; + int dynres; /* Convert list of names to a name and namespace */ namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname, @@ -1075,6 +1102,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) prorows = -1; /* indicates not set */ prosupport = InvalidOid; parallel = PROPARALLEL_UNSAFE; + dynres = 0; /* Extract non-default attributes from stmt->options list */ compute_function_attributes(pstate, @@ -1084,7 +1112,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &isWindowFunc, &volatility, &isStrict, &security, &isLeakProof, &proconfig, &procost, &prorows, - &prosupport, ¶llel); + &prosupport, ¶llel, &dynres); if (!language) { @@ -1285,7 +1313,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) PointerGetDatum(proconfig), prosupport, procost, - prorows); + prorows, + dynres); } /* @@ -1362,6 +1391,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; ObjectAddress address; rel = table_open(ProcedureRelationId, RowExclusiveLock); @@ -1405,7 +1435,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) &cost_item, &rows_item, &support_item, - ¶llel_item) == false) + ¶llel_item, + &dynres_item) == false) elog(ERROR, "option \"%s\" not recognized", defel->defname); } @@ -1467,6 +1498,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) } if (parallel_item) procForm->proparallel = interpret_func_parallel(parallel_item); + if (dynres_item) + procForm->prodynres = intVal(dynres_item->arg); if (set_items) { Datum datum; @@ -2044,6 +2077,17 @@ IsThereFunctionInNamespace(const char *proname, int pronargs, get_namespace_name(nspOid)))); } +static List *procedure_stack; + +Oid +CurrentProcedure(void) +{ + if (!procedure_stack) + return InvalidOid; + else + return llast_oid(procedure_stack); +} + /* * ExecuteDoStmt * Execute inline procedural-language code @@ -2140,8 +2184,19 @@ ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic) ReleaseSysCache(languageTuple); - /* execute the inline handler */ - OidFunctionCall1(laninline, PointerGetDatum(codeblock)); + procedure_stack = lappend_oid(procedure_stack, InvalidOid); + PG_TRY(); + { + /* execute the inline handler */ + OidFunctionCall1(laninline, PointerGetDatum(codeblock)); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + + CloseOtherReturnableCursors(InvalidOid); } /* @@ -2183,6 +2238,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver AclResult aclresult; FmgrInfo flinfo; CallContext *callcontext; + int prodynres; EState *estate; ExprContext *econtext; HeapTuple tp; @@ -2223,6 +2279,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef) callcontext->atomic = true; + prodynres = ((Form_pg_proc) GETSTRUCT(tp))->prodynres; + ReleaseSysCache(tp); /* safety check; see ExecInitFunc() */ @@ -2283,7 +2341,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver /* Here we actually call the procedure */ pgstat_init_function_usage(fcinfo, &fcusage); - retval = FunctionCallInvoke(fcinfo); + + procedure_stack = lappend_oid(procedure_stack, fexpr->funcid); + PG_TRY(); + { + retval = FunctionCallInvoke(fcinfo); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + pgstat_end_function_usage(&fcusage, true); /* Handle the procedure's outputs */ @@ -2344,6 +2413,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver fexpr->funcresulttype); FreeExecutorState(estate); + + CloseOtherReturnableCursors(fexpr->funcid); + + if (list_length(GetReturnableCursors()) > prodynres) + ereport(WARNING, + errcode(ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS), + errmsg("attempt to return too many result sets")); } /* diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c index 8a3cf98cce..e73f7bfb22 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -24,6 +24,7 @@ #include <limits.h> #include "access/xact.h" +#include "commands/defrem.h" #include "commands/portalcmds.h" #include "executor/executor.h" #include "executor/tstoreReceiver.h" @@ -140,6 +141,28 @@ PerformCursorOpen(ParseState *pstate, DeclareCursorStmt *cstmt, ParamListInfo pa portal->cursorOptions |= CURSOR_OPT_NO_SCROLL; } + /* + * For returnable cursors, remember the currently active procedure, as + * well as the command ID, so we can sort by creation order later. If + * there is no procedure active, the cursor is marked as WITHOUT RETURN. + * (This is not an error, per SQL standard, subclause "Effect of opening a + * cursor".) + */ + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + Oid procId = CurrentProcedure(); + + if (procId) + { + portal->procId = procId; + portal->createCid = GetCurrentCommandId(true); + } + else + { + portal->cursorOptions &= ~CURSOR_OPT_RETURN; + } + } + /* * Start execution, inserting parameters if any. */ diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 04bddaef81..1e40fcedd3 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1777,7 +1777,8 @@ makeRangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructors internally-dependent on the range type so @@ -1842,7 +1843,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructor internally-dependent on the multirange type so @@ -1886,7 +1888,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); @@ -1924,7 +1927,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a0138382a1..8312fbf2c6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P - DOUBLE_P DROP + DOUBLE_P DROP DYNAMIC EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION @@ -735,7 +735,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESTART RESTRICT RESULT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -8532,6 +8532,10 @@ common_func_opt_item: { $$ = makeDefElem("parallel", (Node *) makeString($2), @1); } + | DYNAMIC RESULT SETS Iconst + { + $$ = makeDefElem("dynamic_result_sets", (Node *)makeInteger($4), @1); + } ; createfunc_opt_item: @@ -12421,6 +12425,12 @@ cursor_options: /*EMPTY*/ { $$ = 0; } opt_hold: /* EMPTY */ { $$ = 0; } | WITH HOLD { $$ = CURSOR_OPT_HOLD; } | WITHOUT HOLD { $$ = 0; } + | WITH HOLD WITH RETURN { $$ = CURSOR_OPT_HOLD | CURSOR_OPT_RETURN; } + | WITHOUT HOLD WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITH HOLD WITHOUT RETURN { $$ = CURSOR_OPT_HOLD; } + | WITHOUT HOLD WITHOUT RETURN { $$ = 0; } + | WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITHOUT RETURN { $$ = 0; } ; /***************************************************************************** @@ -16787,6 +16797,7 @@ unreserved_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ENABLE_P | ENCODING @@ -16932,6 +16943,7 @@ unreserved_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE @@ -17332,6 +17344,7 @@ bare_label_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ELSE | ENABLE_P @@ -17519,6 +17532,7 @@ bare_label_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index cab709b07b..98ac9aa012 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -32,6 +32,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/async.h" +#include "commands/defrem.h" #include "commands/prepare.h" #include "common/pg_prng.h" #include "jit/jit.h" @@ -1073,6 +1074,7 @@ exec_simple_query(const char *query_string) int16 format; const char *cmdtagname; size_t cmdtaglen; + ListCell *lc; pgstat_report_query_id(0, true); @@ -1235,7 +1237,7 @@ exec_simple_query(const char *query_string) MemoryContextSwitchTo(oldcontext); /* - * Run the portal to completion, and then drop it (and the receiver). + * Run the portal to completion, and then drop it. */ (void) PortalRun(portal, FETCH_ALL, @@ -1245,10 +1247,34 @@ exec_simple_query(const char *query_string) receiver, &qc); - receiver->rDestroy(receiver); - PortalDrop(portal, false); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dynportal = lfirst(lc); + + if (dest == DestRemote) + SetRemoteDestReceiverParams(receiver, dynportal); + + PortalRun(dynportal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dynportal, false); + } + + /* + * Drop the receiver. + */ + receiver->rDestroy(receiver); + if (lnext(parsetree_list, parsetree_item) == NULL) { /* @@ -2200,6 +2226,11 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); + if (GetReturnableCursors()) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("dynamic result sets are not yet supported in extended query protocol")); + receiver->rDestroy(receiver); /* Done executing; remove the params error callback */ diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 3d244af130..9b94a5fa92 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -83,6 +83,7 @@ Section: Class 01 - Warning # do not use this class for failure conditions 01000 W ERRCODE_WARNING warning 0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED dynamic_result_sets_returned +0100E W ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS attempt_to_return_too_many_result_sets 01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING implicit_zero_bit_padding 01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION null_value_eliminated_in_set_function 01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED privilege_not_granted diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c index 06dfa85f04..f29a6eabf8 100644 --- a/src/backend/utils/mmgr/portalmem.c +++ b/src/backend/utils/mmgr/portalmem.c @@ -1289,3 +1289,51 @@ ForgetPortalSnapshots(void) elog(ERROR, "portal snapshots (%d) did not account for all active snapshots (%d)", numPortalSnaps, numActiveSnaps); } + +static int +cmp_portals_by_creation(const ListCell *a, const ListCell *b) +{ + Portal pa = lfirst(a); + Portal pb = lfirst(b); + + return pa->createCid - pb->createCid; +} + +List * +GetReturnableCursors(void) +{ + List *ret = NIL; + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN) + ret = lappend(ret, portal); + } + + list_sort(ret, cmp_portals_by_creation); + + return ret; +} + +void +CloseOtherReturnableCursors(Oid procid) +{ + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN && portal->procId != procid) + PortalDrop(portal, false); + } +} diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index cc424fd3b2..1bc4aacedb 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11641,6 +11641,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) char *prorows; char *prosupport; char *proparallel; + int prodynres; char *lanname; char **configitems = NULL; int nconfigitems = 0; @@ -11708,10 +11709,17 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (fout->remoteVersion >= 140000) appendPQExpBufferStr(query, - "pg_get_function_sqlbody(p.oid) AS prosqlbody\n"); + "pg_get_function_sqlbody(p.oid) AS prosqlbody,\n"); else appendPQExpBufferStr(query, - "NULL AS prosqlbody\n"); + "NULL AS prosqlbody,\n"); + + if (fout->remoteVersion >= 160000) + appendPQExpBufferStr(query, + "prodynres\n"); + else + appendPQExpBufferStr(query, + "0 AS prodynres\n"); appendPQExpBufferStr(query, "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n" @@ -11756,6 +11764,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows")); prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport")); proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel")); + prodynres = atoi(PQgetvalue(res, 0, PQfnumber(res, "prodynres"))); lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname")); /* @@ -11874,6 +11883,9 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (proisstrict[0] == 't') appendPQExpBufferStr(q, " STRICT"); + if (prodynres > 0) + appendPQExpBuffer(q, " DYNAMIC RESULT SETS %d", prodynres); + if (prosecdef[0] == 't') appendPQExpBufferStr(q, " SECURITY DEFINER"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index e7abe0b497..f4ef8f0ece 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -76,6 +76,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce /* see PROPARALLEL_ categories below */ char proparallel BKI_DEFAULT(s); + /* maximum number of dynamic result sets */ + int32 prodynres BKI_DEFAULT(0); + /* number of arguments */ /* Note: need not be given in pg_proc.dat; genbki.pl will compute it */ int16 pronargs; @@ -211,7 +214,8 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows); + float4 prorows, + int dynres); extern bool function_parse_error_transpose(const char *prosrc); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 4f7f87fc62..fcfe8df78e 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -57,6 +57,7 @@ extern ObjectAddress CreateTransform(CreateTransformStmt *stmt); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, oidvector *proargtypes, Oid nspOid); extern void ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic); +extern Oid CurrentProcedure(void); extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest); extern TupleDesc CallStmtResultDesc(CallStmt *stmt); extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f7d7f10f7d..acae7da708 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3010,6 +3010,7 @@ typedef struct SecLabelStmt #define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */ #define CURSOR_OPT_ASENSITIVE 0x0010 /* ASENSITIVE */ #define CURSOR_OPT_HOLD 0x0020 /* WITH HOLD */ +#define CURSOR_OPT_RETURN 0x0040 /* WITH RETURN */ /* these planner-control flags do not correspond to any SQL grammar: */ #define CURSOR_OPT_FAST_PLAN 0x0100 /* prefer fast-start plan */ #define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index bb36213e6f..60457d21f7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -144,6 +144,7 @@ PG_KEYWORD("document", DOCUMENT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL) @@ -353,6 +354,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("result", RESULT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index aa08b1e0fc..6f04362dfe 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -132,6 +132,16 @@ typedef struct PortalData SubTransactionId activeSubid; /* the last subxact with activity */ int createLevel; /* creating subxact's nesting level */ + /* + * Procedure that created this portal. Used for returnable cursors. + */ + Oid procId; + /* + * Command ID where the portal was created. Used for sorting returnable + * cursors into creation order. + */ + CommandId createCid; + /* The query or queries the portal will execute */ const char *sourceText; /* text of query (as of 8.4, never NULL) */ CommandTag commandTag; /* command tag for original query */ @@ -248,5 +258,7 @@ extern void PortalHashTableDeleteAll(void); extern bool ThereAreNoReadyPortals(void); extern void HoldPinnedPortals(void); extern void ForgetPortalSnapshots(void); +extern List *GetReturnableCursors(void); +extern void CloseOtherReturnableCursors(Oid procid); #endif /* PORTAL_H */ diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index f7eb42d54f..0b9686fbff 100644 --- a/src/pl/plpgsql/src/Makefile +++ b/src/pl/plpgsql/src/Makefile @@ -34,7 +34,7 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB) REGRESS = plpgsql_array plpgsql_call plpgsql_control plpgsql_copy plpgsql_domain \ plpgsql_record plpgsql_cache plpgsql_simple plpgsql_transaction \ - plpgsql_trap plpgsql_trigger plpgsql_varprops + plpgsql_trap plpgsql_trigger plpgsql_varprops plpgsql_with_return # where to find gen_keywordlist.pl and subsidiary files TOOLSDIR = $(top_srcdir)/src/tools diff --git a/src/pl/plpgsql/src/expected/plpgsql_with_return.out b/src/pl/plpgsql/src/expected/plpgsql_with_return.out new file mode 100644 index 0000000000..2f6b034e5e --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_with_return.out @@ -0,0 +1,105 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; +CALL pdrstest1(1); + ay +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest1(2); + ay +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DO $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(1); + OPEN c2; +END; +$$; + ay +---- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +-- (The result sets of the called procedure are not returned.) +DO $$ +BEGIN + CALL pdrstest1(1); +END; +$$; +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM drs_test1; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM drs_test2; + END IF; +END; +$$; +CALL pdrstest2(1); + ax +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest2(2); + ax +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DROP TABLE drs_test1, drs_test2; diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build index e185a87024..b6fc35e23f 100644 --- a/src/pl/plpgsql/src/meson.build +++ b/src/pl/plpgsql/src/meson.build @@ -86,6 +86,7 @@ tests += { 'plpgsql_trap', 'plpgsql_trigger', 'plpgsql_varprops', + 'plpgsql_with_return', ], }, } diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ffd6d2e3bc..ea11144f6d 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4776,6 +4776,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) elog(ERROR, "could not open cursor: %s", SPI_result_code_string(SPI_result)); + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + portal->procId = estate->func->fn_oid; + portal->createCid = GetCurrentCommandId(true); + } + /* * If cursor variable was NULL, store the generated portal name in it, * after verifying it's okay to assign to. diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index edeb72c380..bff1557005 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -212,7 +212,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <datum> getdiag_target %type <ival> getdiag_item -%type <ival> opt_scrollable +%type <ival> opt_scrollable opt_with_return %type <fetch> opt_fetch_direction %type <ival> opt_transaction_chain @@ -352,6 +352,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_WARNING %token <keyword> K_WHEN %token <keyword> K_WHILE +%token <keyword> K_WITH +%token <keyword> K_WITHOUT %% @@ -529,7 +531,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull plpgsql_ns_additem($4->itemtype, $4->itemno, $1.name); } - | decl_varname opt_scrollable K_CURSOR + | decl_varname opt_scrollable K_CURSOR opt_with_return { plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); } decl_cursor_args decl_is_for decl_cursor_query { @@ -546,12 +548,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull NULL), true); - new->cursor_explicit_expr = $7; - if ($5 == NULL) + new->cursor_explicit_expr = $8; + if ($6 == NULL) new->cursor_explicit_argrow = -1; else - new->cursor_explicit_argrow = $5->dno; - new->cursor_options = CURSOR_OPT_FAST_PLAN | $2; + new->cursor_explicit_argrow = $6->dno; + new->cursor_options = CURSOR_OPT_FAST_PLAN | $2 | $4; } ; @@ -569,6 +571,20 @@ opt_scrollable : } ; +opt_with_return : + { + $$ = 0; + } + | K_WITH K_RETURN + { + $$ = CURSOR_OPT_RETURN; + } + | K_WITHOUT K_RETURN + { + $$ = 0; + } + ; + decl_cursor_query : { $$ = read_sql_stmt(); @@ -1976,6 +1992,10 @@ stmt_execsql : K_IMPORT { $$ = make_execsql_stmt(K_MERGE, @1); } + | K_WITH + { + $$ = make_execsql_stmt(K_WITH, @1); + } | T_WORD { int tok; @@ -2098,6 +2118,30 @@ stmt_open : K_OPEN cursor_variable tok = yylex(); } + /* same for opt_with_return */ + if (tok_is_keyword(tok, &yylval, + K_WITH, "with")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= CURSOR_OPT_RETURN; + tok = yylex(); + } + } + else if (tok_is_keyword(tok, &yylval, + K_WITHOUT, "without")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= 0; + tok = yylex(); + } + } + if (tok != K_FOR) yyerror("syntax error, expected \"FOR\""); @@ -2552,6 +2596,8 @@ unreserved_keyword : | K_USE_VARIABLE | K_VARIABLE_CONFLICT | K_WARNING + | K_WITH + | K_WITHOUT ; %% diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index 466bdc7a20..8a8f8ea47a 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -109,3 +109,5 @@ PG_KEYWORD("use_column", K_USE_COLUMN) PG_KEYWORD("use_variable", K_USE_VARIABLE) PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT) PG_KEYWORD("warning", K_WARNING) +PG_KEYWORD("with", K_WITH) +PG_KEYWORD("without", K_WITHOUT) diff --git a/src/pl/plpgsql/src/sql/plpgsql_with_return.sql b/src/pl/plpgsql/src/sql/plpgsql_with_return.sql new file mode 100644 index 0000000000..08da362bce --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_with_return.sql @@ -0,0 +1,64 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); + + +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; + +CALL pdrstest1(1); +CALL pdrstest1(2); + + +DO $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(1); + OPEN c2; +END; +$$; + + +-- (The result sets of the called procedure are not returned.) +DO $$ +BEGIN + CALL pdrstest1(1); +END; +$$; + + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM drs_test1; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM drs_test2; + END IF; +END; +$$; + +CALL pdrstest2(1); +CALL pdrstest2(2); + + +DROP TABLE drs_test1, drs_test2; diff --git a/src/test/regress/expected/dynamic_result_sets.out b/src/test/regress/expected/dynamic_result_sets.out new file mode 100644 index 0000000000..7b2529c99e --- /dev/null +++ b/src/test/regress/expected/dynamic_result_sets.out @@ -0,0 +1,129 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); +-- return a couple of result sets from a procedure +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; +CALL pdrstest1(); + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest1() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- return too many result sets from a procedure +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; +CALL pdrstest2(); +WARNING: attempt to return too many result sets + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest2() \bind \g +WARNING: attempt to return too many result sets +ERROR: dynamic result sets are not yet supported in extended query protocol +-- nested calls +CREATE PROCEDURE pdrstest3() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM drs_test1 WHERE a < 2; +$$; +-- (The result sets of the called procedure are not returned.) +CALL pdrstest3(); + a +--- + 1 +(1 row) + +CALL pdrstest3() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- both out parameter and result sets +CREATE PROCEDURE pdrstest4(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +SELECT a || a; +$$; +CALL pdrstest4('x'); + a +---- + xx +(1 row) + + a +--- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest4($1) \bind 'y' \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- test the nested error handling +CREATE TABLE drs_test_dummy (a int); +CREATE PROCEDURE pdrstest5a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; +$$; +CREATE PROCEDURE pdrstest5b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest5a(); +$$; +DROP TABLE drs_test_dummy; +CALL pdrstest5b(); +ERROR: relation "drs_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_du... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; + +CONTEXT: SQL function "pdrstest5a" during startup +SQL function "pdrstest5b" statement 1 +CALL pdrstest5b() \bind \g +ERROR: relation "drs_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_du... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; + +CONTEXT: SQL function "pdrstest5a" during startup +SQL function "pdrstest5b" statement 1 +-- cleanup +DROP TABLE drs_test1, drs_test2; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 15e015b3d6..57f3c9b6cd 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -43,7 +43,7 @@ test: copy copyselect copydml insert insert_conflict # Note: many of the tests in later groups depend on create_index # ---------- test: create_function_c create_misc create_operator create_procedure create_table create_type -test: create_index create_index_spgist create_view index_including index_including_gist +test: create_index create_index_spgist create_view index_including index_including_gist dynamic_result_sets # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/dynamic_result_sets.sql b/src/test/regress/sql/dynamic_result_sets.sql new file mode 100644 index 0000000000..ed4a91740e --- /dev/null +++ b/src/test/regress/sql/dynamic_result_sets.sql @@ -0,0 +1,90 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); + + +-- return a couple of result sets from a procedure + +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; + +CALL pdrstest1(); +CALL pdrstest1() \bind \g + + +-- return too many result sets from a procedure + +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; + +CALL pdrstest2(); +CALL pdrstest2() \bind \g + + +-- nested calls + +CREATE PROCEDURE pdrstest3() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM drs_test1 WHERE a < 2; +$$; + +-- (The result sets of the called procedure are not returned.) +CALL pdrstest3(); +CALL pdrstest3() \bind \g + + +-- both out parameter and result sets + +CREATE PROCEDURE pdrstest4(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +SELECT a || a; +$$; + +CALL pdrstest4('x'); +CALL pdrstest4($1) \bind 'y' \g + + +-- test the nested error handling + +CREATE TABLE drs_test_dummy (a int); + +CREATE PROCEDURE pdrstest5a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; +$$; + +CREATE PROCEDURE pdrstest5b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest5a(); +$$; + +DROP TABLE drs_test_dummy; + +CALL pdrstest5b(); +CALL pdrstest5b() \bind \g + + +-- cleanup + +DROP TABLE drs_test1, drs_test2; base-commit: 4fc53819a45fe6e7233a69bb279557b2070dcc40 -- 2.39.2 From 552a7fa9e79578b37f375579f7cb46cd897d100f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Fri, 24 Feb 2023 12:21:40 +0100 Subject: [PATCH v8 2/2] WIP: Dynamic result sets in extended query protocol This is currently broken due to/since acb7e4eb6b. TODO: consider minor protocol version bump (3.1) --- doc/src/sgml/protocol.sgml | 19 +++++++++++ src/backend/tcop/postgres.c | 32 ++++++++++++++++--- src/backend/tcop/pquery.c | 6 ++++ src/include/utils/portal.h | 2 ++ src/interfaces/libpq/fe-protocol3.c | 6 ++-- .../regress/expected/dynamic_result_sets.out | 31 +++++++++++++++--- 6 files changed, 84 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 93fc7167d4..ec605b12b5 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -959,6 +959,25 @@ <title>Extended Query</title> an empty query string), ErrorResponse, or PortalSuspended. </para> + <para> + Executing a portal may give rise to a <firstterm>dynamic result set + sequence</firstterm>. That means the command contained in the portal + created additional result sets beyond what it normally returns. (The + typical example is calling a stored procedure that creates dynamic result + sets.) Dynamic result sets are issued after whatever response the main + command issued. Each dynamic result set begins with a RowDescription + message followed by zero or more DataRow messages. (Since, as explained + above, an Execute message normally does not respond with a RowDescription, + the appearance of the first RowDescription marks the end of the primary + result set of the portal and the beginning of the first dynamic result + set.) The CommandComplete message that concludes the Execute message + response follows <emphasis>after</emphasis> all dynamic result sets. Note + that dynamic result sets cannot, by their nature, be decribed prior to the + execution of the portal. Multiple executions of the same prepared + statement could result in dynamic result sets with different row + descriptions being returned. + </para> + <para> At completion of each series of extended-query messages, the frontend should issue a Sync message. This parameterless message causes the diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 98ac9aa012..89da5c7512 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -2086,6 +2086,7 @@ exec_execute_message(const char *portal_name, long max_rows) const char *sourceText; const char *prepStmtName; ParamListInfo portalParams; + ListCell *lc; bool save_log_statement_stats = log_statement_stats; bool is_xact_command; bool execute_is_fetch; @@ -2226,10 +2227,33 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); - if (GetReturnableCursors()) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("dynamic result sets are not yet supported in extended query protocol")); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dyn_portal = lfirst(lc); + + if (dest == DestRemoteExecute) + SetRemoteDestReceiverParams(receiver, dyn_portal); + + PortalSetResultFormat(dyn_portal, 1, &portal->dynamic_format); + + SendRowDescriptionMessage(&row_description_buf, + dyn_portal->tupDesc, + FetchPortalTargetList(dyn_portal), + dyn_portal->formats); + + PortalRun(dyn_portal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dyn_portal, false); + } receiver->rDestroy(receiver); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5f0248acc5..6469940935 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -641,6 +641,8 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) errmsg("bind message has %d result formats but query has %d columns", nFormats, natts))); memcpy(portal->formats, formats, natts * sizeof(int16)); + + portal->dynamic_format = 0; } else if (nFormats > 0) { @@ -649,12 +651,16 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) for (i = 0; i < natts; i++) portal->formats[i] = format1; + + portal->dynamic_format = format1; } else { /* use default format for all columns */ for (i = 0; i < natts; i++) portal->formats[i] = 0; + + portal->dynamic_format = 0; } } diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index 6f04362dfe..55406b8654 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -170,6 +170,8 @@ typedef struct PortalData TupleDesc tupDesc; /* descriptor for result tuples */ /* and these are the format codes to use for the columns: */ int16 *formats; /* a format code for each column */ + /* Format code for dynamic result sets */ + int16 dynamic_format; /* * Outermost ActiveSnapshot for execution of the portal's queries. For diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..863a09cf74 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -320,10 +320,8 @@ pqParseInput3(PGconn *conn) { /* * A new 'T' message is treated as the start of - * another PGresult. (It is not clear that this is - * really possible with the current backend.) We stop - * parsing until the application accepts the current - * result. + * another PGresult. We stop parsing until the + * application accepts the current result. */ conn->asyncStatus = PGASYNC_READY; return; diff --git a/src/test/regress/expected/dynamic_result_sets.out b/src/test/regress/expected/dynamic_result_sets.out index 7b2529c99e..3584f1ec8c 100644 --- a/src/test/regress/expected/dynamic_result_sets.out +++ b/src/test/regress/expected/dynamic_result_sets.out @@ -25,7 +25,14 @@ CALL pdrstest1(); (2 rows) CALL pdrstest1() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) -- return too many result sets from a procedure CREATE PROCEDURE pdrstest2() LANGUAGE SQL @@ -51,7 +58,14 @@ WARNING: attempt to return too many result sets CALL pdrstest2() \bind \g WARNING: attempt to return too many result sets -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) -- nested calls CREATE PROCEDURE pdrstest3() LANGUAGE SQL @@ -68,7 +82,11 @@ CALL pdrstest3(); (1 row) CALL pdrstest3() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 +(1 row) + -- both out parameter and result sets CREATE PROCEDURE pdrstest4(INOUT a text) LANGUAGE SQL @@ -91,7 +109,12 @@ CALL pdrstest4('x'); (3 rows) CALL pdrstest4($1) \bind 'y' \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +---- + yy +(1 row) + +server sent data ("D" message) without prior row description ("T" message) -- test the nested error handling CREATE TABLE drs_test_dummy (a int); CREATE PROCEDURE pdrstest5a() -- 2.39.2 Attachments: [text/plain] v8-0001-Dynamic-result-sets-from-procedures.patch (52.7K, ../../[email protected]/2-v8-0001-Dynamic-result-sets-from-procedures.patch) download | inline diff: From ada315925d02883833cc5f4bc95477b0217d9d66 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Fri, 24 Feb 2023 12:21:40 +0100 Subject: [PATCH v8 1/2] Dynamic result sets from procedures Declaring a cursor WITH RETURN in a procedure makes the cursor's data be returned as a result of the CALL (or DO) invocation. The procedure needs to be declared with the DYNAMIC RESULT SETS attribute. Discussion: https://www.postgresql.org/message-id/flat/[email protected] --- doc/src/sgml/catalogs.sgml | 10 ++ doc/src/sgml/information_schema.sgml | 3 +- doc/src/sgml/plpgsql.sgml | 27 +++- doc/src/sgml/ref/alter_procedure.sgml | 12 ++ doc/src/sgml/ref/create_procedure.sgml | 14 ++ doc/src/sgml/ref/declare.sgml | 35 ++++- src/backend/catalog/information_schema.sql | 2 +- src/backend/catalog/pg_aggregate.c | 3 +- src/backend/catalog/pg_proc.c | 4 +- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/functioncmds.c | 94 +++++++++++-- src/backend/commands/portalcmds.c | 23 ++++ src/backend/commands/typecmds.c | 12 +- src/backend/parser/gram.y | 18 ++- src/backend/tcop/postgres.c | 37 ++++- src/backend/utils/errcodes.txt | 1 + src/backend/utils/mmgr/portalmem.c | 48 +++++++ src/bin/pg_dump/pg_dump.c | 16 ++- src/include/catalog/pg_proc.h | 6 +- src/include/commands/defrem.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 2 + src/include/utils/portal.h | 12 ++ src/pl/plpgsql/src/Makefile | 2 +- .../src/expected/plpgsql_with_return.out | 105 ++++++++++++++ src/pl/plpgsql/src/meson.build | 1 + src/pl/plpgsql/src/pl_exec.c | 6 + src/pl/plpgsql/src/pl_gram.y | 58 +++++++- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 2 + .../plpgsql/src/sql/plpgsql_with_return.sql | 64 +++++++++ .../regress/expected/dynamic_result_sets.out | 129 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/dynamic_result_sets.sql | 90 ++++++++++++ 33 files changed, 803 insertions(+), 39 deletions(-) create mode 100644 src/pl/plpgsql/src/expected/plpgsql_with_return.out create mode 100644 src/pl/plpgsql/src/sql/plpgsql_with_return.sql create mode 100644 src/test/regress/expected/dynamic_result_sets.out create mode 100644 src/test/regress/sql/dynamic_result_sets.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054..5baec4dc3a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -6041,6 +6041,16 @@ <title><structname>pg_proc</structname> Columns</title> </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>prodynres</structfield> <type>int4</type> + </para> + <para> + For procedures, this records the maximum number of dynamic result sets + the procedure may create. Otherwise zero. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>pronargs</structfield> <type>int2</type> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 350c75bc31..5fc9dc22ae 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -5885,7 +5885,8 @@ <title><structname>routines</structname> Columns</title> <structfield>max_dynamic_result_sets</structfield> <type>cardinal_number</type> </para> <para> - Applies to a feature not available in <productname>PostgreSQL</productname> + For a procedure, the maximum number of dynamic result sets. Otherwise + zero. </para></entry> </row> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 8897a5450a..0c0d77b0e6 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3128,7 +3128,7 @@ <title>Declaring Cursor Variables</title> Another way is to use the cursor declaration syntax, which in general is: <synopsis> -<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; +<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> <optional> WITH RETURN </optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>; </synopsis> (<literal>FOR</literal> can be replaced by <literal>IS</literal> for <productname>Oracle</productname> compatibility.) @@ -3136,6 +3136,10 @@ <title>Declaring Cursor Variables</title> scrolling backward; if <literal>NO SCROLL</literal> is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. + If <literal>WITH RETURN</literal> is specified, the results of the + cursor, after it is opened, will be returned as a dynamic result set; see + <xref linkend="sql-declare"/> for details. (<literal>WITHOUT + RETURN</literal> can also be specified but has no effect.) <replaceable>arguments</replaceable>, if specified, is a comma-separated list of pairs <literal><replaceable>name</replaceable> <replaceable>datatype</replaceable></literal> that define names to be @@ -3215,7 +3219,7 @@ <title>Opening Cursors</title> <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> <synopsis> -OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>; +OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> <optional> WITH RETURN </optional> FOR <replaceable>query</replaceable>; </synopsis> <para> @@ -3233,8 +3237,9 @@ <title><command>OPEN FOR</command> <replaceable>query</replaceable></title> substituted is the one it has at the time of the <command>OPEN</command>; subsequent changes to the variable will not affect the cursor's behavior. - The <literal>SCROLL</literal> and <literal>NO SCROLL</literal> - options have the same meanings as for a bound cursor. + The options <literal>SCROLL</literal>, <literal>NO SCROLL</literal>, + and <literal>WITH RETURN</literal> have the same meanings as for a + bound cursor. </para> <para> @@ -3612,6 +3617,20 @@ <title>Returning Cursors</title> COMMIT; </programlisting> </para> + + <note> + <para> + Returning a cursor from a function as described here is a separate + mechanism from declaring a cursor <literal>WITH RETURN</literal>, + which automatically produces a result set for the client if the + cursor is left open when returning from the procedure. Both + mechanisms can be used to achieve similar effects. The differences + are mainly how the client application prefers to manage the cursors. + Furthermore, other SQL implementations have other programming models + that might map more easily to one or the other mechanism when doing a + migration. + </para> + </note> </sect3> </sect2> diff --git a/doc/src/sgml/ref/alter_procedure.sgml b/doc/src/sgml/ref/alter_procedure.sgml index a4737a3439..2cdda7730e 100644 --- a/doc/src/sgml/ref/alter_procedure.sgml +++ b/doc/src/sgml/ref/alter_procedure.sgml @@ -34,6 +34,7 @@ <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> + DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT } SET <replaceable class="parameter">configuration_parameter</replaceable> FROM CURRENT @@ -158,6 +159,17 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Changes the dynamic result sets setting of the procedure. See <xref + linkend="sql-createprocedure"/> for more information. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal><optional> EXTERNAL </optional> SECURITY INVOKER</literal></term> <term><literal><optional> EXTERNAL </optional> SECURITY DEFINER</literal></term> diff --git a/doc/src/sgml/ref/create_procedure.sgml b/doc/src/sgml/ref/create_procedure.sgml index 03a14c8684..1c99b00eef 100644 --- a/doc/src/sgml/ref/create_procedure.sgml +++ b/doc/src/sgml/ref/create_procedure.sgml @@ -24,6 +24,7 @@ CREATE [ OR REPLACE ] PROCEDURE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] ) { LANGUAGE <replaceable class="parameter">lang_name</replaceable> + | DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable> | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT } @@ -176,6 +177,19 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>DYNAMIC RESULT SETS <replaceable class="parameter">dynamic_result_sets</replaceable></literal></term> + + <listitem> + <para> + Specifies how many dynamic result sets the procedure returns (see + <literal><link linkend="sql-declare">DECLARE</link> WITH + RETURN</literal>). The default is 0. If a procedure returns more + result sets than declared, a warning is raised. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term> diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 5712825314..a19198e6cb 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -32,7 +32,8 @@ <refsynopsisdiv> <synopsis> DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] - CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> + CURSOR [ { WITH | WITHOUT } HOLD ] [ { WITH | WITHOUT } RETURN ] + FOR <replaceable class="parameter">query</replaceable> </synopsis> </refsynopsisdiv> @@ -138,6 +139,23 @@ <title>Parameters</title> </listitem> </varlistentry> + <varlistentry> + <term><literal>WITH RETURN</literal></term> + <term><literal>WITHOUT RETURN</literal></term> + <listitem> + <para> + This option is only valid for cursors defined inside a procedure or + <command>DO</command> block. <literal>WITH RETURN</literal> specifies + that the cursor's result rows will be provided as a result set of the + procedure or code block invocation. To accomplish that, the cursor must + be left open at the end of the procedure or code block. If multiple + <literal>WITH RETURN</literal> cursors are declared, then their results + will be returned in the order they were created. <literal>WITHOUT + RETURN</literal> is the default. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="parameter">query</replaceable></term> <listitem> @@ -339,6 +357,21 @@ <title>Examples</title> See <xref linkend="sql-fetch"/> for more examples of cursor usage. </para> + + <para> + This example shows how to return multiple result sets from a procedure: +<programlisting> +CREATE PROCEDURE test() +LANGUAGE SQL +AS $$ +DECLARE a CURSOR WITH RETURN FOR SELECT * FROM tbl1; +DECLARE b CURSOR WITH RETURN FOR SELECT * FROM tbl2; +$$; + +CALL test(); +</programlisting> + The results of the two cursors will be returned in order from this call. + </para> </refsect1> <refsect1> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 0555e9bc03..871a27b84b 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1593,7 +1593,7 @@ CREATE VIEW routines AS CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, CAST('YES' AS yes_or_no) AS schema_level_routine, - CAST(0 AS cardinal_number) AS max_dynamic_result_sets, + CAST(p.prodynres AS cardinal_number) AS max_dynamic_result_sets, CAST(null AS yes_or_no) AS is_user_defined_cast, CAST(null AS yes_or_no) AS is_implicitly_invocable, CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type, diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index ebc4454743..a633bb7501 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -640,7 +640,8 @@ AggregateCreate(const char *aggName, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* no prosupport */ 1, /* procost */ - 0); /* prorows */ + 0, /* prorows */ + 0); /* prodynres */ procOid = myself.objectId; /* diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 14d552fe2d..620fb80a9c 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -95,7 +95,8 @@ ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows) + float4 prorows, + int dynres) { Oid retval; int parameterCount; @@ -314,6 +315,7 @@ ProcedureCreate(const char *procedureName, values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet); values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility); values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel); + values[Anum_pg_proc_prodynres - 1] = Int32GetDatum(dynres); values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount); values[Anum_pg_proc_pronargdefaults - 1] = UInt16GetDatum(list_length(parameterDefaults)); values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType); diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 75a09f14e0..032bef862d 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -486,7 +486,7 @@ T433 Multiargument GROUPING function YES T434 GROUP BY DISTINCT YES T441 ABS and MOD functions YES T461 Symmetric BETWEEN predicate YES -T471 Result sets return value NO +T471 Result sets return value NO partially supported T472 DESCRIBE CURSOR NO T491 LATERAL derived table YES T495 Combined data change and retrieval NO different syntax diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 69f66dfe7d..fe0a74c4d7 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -72,6 +72,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/portal.h" #include "utils/rel.h" #include "utils/snapmgr.h" #include "utils/syscache.h" @@ -513,7 +514,8 @@ compute_common_attribute(ParseState *pstate, DefElem **cost_item, DefElem **rows_item, DefElem **support_item, - DefElem **parallel_item) + DefElem **parallel_item, + DefElem **dynres_item) { if (strcmp(defel->defname, "volatility") == 0) { @@ -589,12 +591,28 @@ compute_common_attribute(ParseState *pstate, *parallel_item = defel; } + else if (strcmp(defel->defname, "dynamic_result_sets") == 0) + { + if (!is_procedure) + goto function_error; + if (*dynres_item) + errorConflictingDefElem(defel, pstate); + + *dynres_item = defel; + } else return false; /* Recognized an option */ return true; +function_error: + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in function definition"), + parser_errposition(pstate, defel->location))); + return false; + procedure_error: ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), @@ -731,7 +749,8 @@ compute_function_attributes(ParseState *pstate, float4 *procost, float4 *prorows, Oid *prosupport, - char *parallel_p) + char *parallel_p, + int *dynres_p) { ListCell *option; DefElem *as_item = NULL; @@ -747,6 +766,7 @@ compute_function_attributes(ParseState *pstate, DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; foreach(option, options) { @@ -792,7 +812,8 @@ compute_function_attributes(ParseState *pstate, &cost_item, &rows_item, &support_item, - ¶llel_item)) + ¶llel_item, + &dynres_item)) { /* recognized common option */ continue; @@ -840,6 +861,11 @@ compute_function_attributes(ParseState *pstate, *prosupport = interpret_func_support(support_item); if (parallel_item) *parallel_p = interpret_func_parallel(parallel_item); + if (dynres_item) + { + *dynres_p = intVal(dynres_item->arg); + Assert(*dynres_p >= 0); /* enforced by parser */ + } } @@ -1051,6 +1077,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) Form_pg_language languageStruct; List *as_clause; char parallel; + int dynres; /* Convert list of names to a name and namespace */ namespaceId = QualifiedNameGetCreationNamespace(stmt->funcname, @@ -1075,6 +1102,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) prorows = -1; /* indicates not set */ prosupport = InvalidOid; parallel = PROPARALLEL_UNSAFE; + dynres = 0; /* Extract non-default attributes from stmt->options list */ compute_function_attributes(pstate, @@ -1084,7 +1112,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &isWindowFunc, &volatility, &isStrict, &security, &isLeakProof, &proconfig, &procost, &prorows, - &prosupport, ¶llel); + &prosupport, ¶llel, &dynres); if (!language) { @@ -1285,7 +1313,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) PointerGetDatum(proconfig), prosupport, procost, - prorows); + prorows, + dynres); } /* @@ -1362,6 +1391,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) DefElem *rows_item = NULL; DefElem *support_item = NULL; DefElem *parallel_item = NULL; + DefElem *dynres_item = NULL; ObjectAddress address; rel = table_open(ProcedureRelationId, RowExclusiveLock); @@ -1405,7 +1435,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) &cost_item, &rows_item, &support_item, - ¶llel_item) == false) + ¶llel_item, + &dynres_item) == false) elog(ERROR, "option \"%s\" not recognized", defel->defname); } @@ -1467,6 +1498,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) } if (parallel_item) procForm->proparallel = interpret_func_parallel(parallel_item); + if (dynres_item) + procForm->prodynres = intVal(dynres_item->arg); if (set_items) { Datum datum; @@ -2044,6 +2077,17 @@ IsThereFunctionInNamespace(const char *proname, int pronargs, get_namespace_name(nspOid)))); } +static List *procedure_stack; + +Oid +CurrentProcedure(void) +{ + if (!procedure_stack) + return InvalidOid; + else + return llast_oid(procedure_stack); +} + /* * ExecuteDoStmt * Execute inline procedural-language code @@ -2140,8 +2184,19 @@ ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic) ReleaseSysCache(languageTuple); - /* execute the inline handler */ - OidFunctionCall1(laninline, PointerGetDatum(codeblock)); + procedure_stack = lappend_oid(procedure_stack, InvalidOid); + PG_TRY(); + { + /* execute the inline handler */ + OidFunctionCall1(laninline, PointerGetDatum(codeblock)); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + + CloseOtherReturnableCursors(InvalidOid); } /* @@ -2183,6 +2238,7 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver AclResult aclresult; FmgrInfo flinfo; CallContext *callcontext; + int prodynres; EState *estate; ExprContext *econtext; HeapTuple tp; @@ -2223,6 +2279,8 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver if (((Form_pg_proc) GETSTRUCT(tp))->prosecdef) callcontext->atomic = true; + prodynres = ((Form_pg_proc) GETSTRUCT(tp))->prodynres; + ReleaseSysCache(tp); /* safety check; see ExecInitFunc() */ @@ -2283,7 +2341,18 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver /* Here we actually call the procedure */ pgstat_init_function_usage(fcinfo, &fcusage); - retval = FunctionCallInvoke(fcinfo); + + procedure_stack = lappend_oid(procedure_stack, fexpr->funcid); + PG_TRY(); + { + retval = FunctionCallInvoke(fcinfo); + } + PG_FINALLY(); + { + procedure_stack = list_delete_last(procedure_stack); + } + PG_END_TRY(); + pgstat_end_function_usage(&fcusage, true); /* Handle the procedure's outputs */ @@ -2344,6 +2413,13 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver fexpr->funcresulttype); FreeExecutorState(estate); + + CloseOtherReturnableCursors(fexpr->funcid); + + if (list_length(GetReturnableCursors()) > prodynres) + ereport(WARNING, + errcode(ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS), + errmsg("attempt to return too many result sets")); } /* diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c index 8a3cf98cce..e73f7bfb22 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -24,6 +24,7 @@ #include <limits.h> #include "access/xact.h" +#include "commands/defrem.h" #include "commands/portalcmds.h" #include "executor/executor.h" #include "executor/tstoreReceiver.h" @@ -140,6 +141,28 @@ PerformCursorOpen(ParseState *pstate, DeclareCursorStmt *cstmt, ParamListInfo pa portal->cursorOptions |= CURSOR_OPT_NO_SCROLL; } + /* + * For returnable cursors, remember the currently active procedure, as + * well as the command ID, so we can sort by creation order later. If + * there is no procedure active, the cursor is marked as WITHOUT RETURN. + * (This is not an error, per SQL standard, subclause "Effect of opening a + * cursor".) + */ + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + Oid procId = CurrentProcedure(); + + if (procId) + { + portal->procId = procId; + portal->createCid = GetCurrentCommandId(true); + } + else + { + portal->cursorOptions &= ~CURSOR_OPT_RETURN; + } + } + /* * Start execution, inserting parameters if any. */ diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 04bddaef81..1e40fcedd3 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1777,7 +1777,8 @@ makeRangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructors internally-dependent on the range type so @@ -1842,7 +1843,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* * Make the constructor internally-dependent on the multirange type so @@ -1886,7 +1888,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); @@ -1924,7 +1927,8 @@ makeMultirangeConstructors(const char *name, Oid namespace, PointerGetDatum(NULL), /* proconfig */ InvalidOid, /* prosupport */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + 0); /* prodynres */ /* ditto */ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); pfree(argtypes); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a0138382a1..8312fbf2c6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DEPTH DESC DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P - DOUBLE_P DROP + DOUBLE_P DROP DYNAMIC EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION @@ -735,7 +735,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESTART RESTRICT RESULT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -8532,6 +8532,10 @@ common_func_opt_item: { $$ = makeDefElem("parallel", (Node *) makeString($2), @1); } + | DYNAMIC RESULT SETS Iconst + { + $$ = makeDefElem("dynamic_result_sets", (Node *)makeInteger($4), @1); + } ; createfunc_opt_item: @@ -12421,6 +12425,12 @@ cursor_options: /*EMPTY*/ { $$ = 0; } opt_hold: /* EMPTY */ { $$ = 0; } | WITH HOLD { $$ = CURSOR_OPT_HOLD; } | WITHOUT HOLD { $$ = 0; } + | WITH HOLD WITH RETURN { $$ = CURSOR_OPT_HOLD | CURSOR_OPT_RETURN; } + | WITHOUT HOLD WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITH HOLD WITHOUT RETURN { $$ = CURSOR_OPT_HOLD; } + | WITHOUT HOLD WITHOUT RETURN { $$ = 0; } + | WITH RETURN { $$ = CURSOR_OPT_RETURN; } + | WITHOUT RETURN { $$ = 0; } ; /***************************************************************************** @@ -16787,6 +16797,7 @@ unreserved_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ENABLE_P | ENCODING @@ -16932,6 +16943,7 @@ unreserved_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE @@ -17332,6 +17344,7 @@ bare_label_keyword: | DOMAIN_P | DOUBLE_P | DROP + | DYNAMIC | EACH | ELSE | ENABLE_P @@ -17519,6 +17532,7 @@ bare_label_keyword: | RESET | RESTART | RESTRICT + | RESULT | RETURN | RETURNS | REVOKE diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index cab709b07b..98ac9aa012 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -32,6 +32,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/async.h" +#include "commands/defrem.h" #include "commands/prepare.h" #include "common/pg_prng.h" #include "jit/jit.h" @@ -1073,6 +1074,7 @@ exec_simple_query(const char *query_string) int16 format; const char *cmdtagname; size_t cmdtaglen; + ListCell *lc; pgstat_report_query_id(0, true); @@ -1235,7 +1237,7 @@ exec_simple_query(const char *query_string) MemoryContextSwitchTo(oldcontext); /* - * Run the portal to completion, and then drop it (and the receiver). + * Run the portal to completion, and then drop it. */ (void) PortalRun(portal, FETCH_ALL, @@ -1245,10 +1247,34 @@ exec_simple_query(const char *query_string) receiver, &qc); - receiver->rDestroy(receiver); - PortalDrop(portal, false); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dynportal = lfirst(lc); + + if (dest == DestRemote) + SetRemoteDestReceiverParams(receiver, dynportal); + + PortalRun(dynportal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dynportal, false); + } + + /* + * Drop the receiver. + */ + receiver->rDestroy(receiver); + if (lnext(parsetree_list, parsetree_item) == NULL) { /* @@ -2200,6 +2226,11 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); + if (GetReturnableCursors()) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("dynamic result sets are not yet supported in extended query protocol")); + receiver->rDestroy(receiver); /* Done executing; remove the params error callback */ diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 3d244af130..9b94a5fa92 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -83,6 +83,7 @@ Section: Class 01 - Warning # do not use this class for failure conditions 01000 W ERRCODE_WARNING warning 0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED dynamic_result_sets_returned +0100E W ERRCODE_WARNING_ATTEMPT_TO_RETURN_TOO_MANY_RESULT_SETS attempt_to_return_too_many_result_sets 01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING implicit_zero_bit_padding 01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION null_value_eliminated_in_set_function 01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED privilege_not_granted diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c index 06dfa85f04..f29a6eabf8 100644 --- a/src/backend/utils/mmgr/portalmem.c +++ b/src/backend/utils/mmgr/portalmem.c @@ -1289,3 +1289,51 @@ ForgetPortalSnapshots(void) elog(ERROR, "portal snapshots (%d) did not account for all active snapshots (%d)", numPortalSnaps, numActiveSnaps); } + +static int +cmp_portals_by_creation(const ListCell *a, const ListCell *b) +{ + Portal pa = lfirst(a); + Portal pb = lfirst(b); + + return pa->createCid - pb->createCid; +} + +List * +GetReturnableCursors(void) +{ + List *ret = NIL; + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN) + ret = lappend(ret, portal); + } + + list_sort(ret, cmp_portals_by_creation); + + return ret; +} + +void +CloseOtherReturnableCursors(Oid procid) +{ + HASH_SEQ_STATUS status; + PortalHashEnt *hentry; + + hash_seq_init(&status, PortalHashTable); + + while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL) + { + Portal portal = hentry->portal; + + if (portal->cursorOptions & CURSOR_OPT_RETURN && portal->procId != procid) + PortalDrop(portal, false); + } +} diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index cc424fd3b2..1bc4aacedb 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11641,6 +11641,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) char *prorows; char *prosupport; char *proparallel; + int prodynres; char *lanname; char **configitems = NULL; int nconfigitems = 0; @@ -11708,10 +11709,17 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (fout->remoteVersion >= 140000) appendPQExpBufferStr(query, - "pg_get_function_sqlbody(p.oid) AS prosqlbody\n"); + "pg_get_function_sqlbody(p.oid) AS prosqlbody,\n"); else appendPQExpBufferStr(query, - "NULL AS prosqlbody\n"); + "NULL AS prosqlbody,\n"); + + if (fout->remoteVersion >= 160000) + appendPQExpBufferStr(query, + "prodynres\n"); + else + appendPQExpBufferStr(query, + "0 AS prodynres\n"); appendPQExpBufferStr(query, "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n" @@ -11756,6 +11764,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) prorows = PQgetvalue(res, 0, PQfnumber(res, "prorows")); prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport")); proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel")); + prodynres = atoi(PQgetvalue(res, 0, PQfnumber(res, "prodynres"))); lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname")); /* @@ -11874,6 +11883,9 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (proisstrict[0] == 't') appendPQExpBufferStr(q, " STRICT"); + if (prodynres > 0) + appendPQExpBuffer(q, " DYNAMIC RESULT SETS %d", prodynres); + if (prosecdef[0] == 't') appendPQExpBufferStr(q, " SECURITY DEFINER"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index e7abe0b497..f4ef8f0ece 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -76,6 +76,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce /* see PROPARALLEL_ categories below */ char proparallel BKI_DEFAULT(s); + /* maximum number of dynamic result sets */ + int32 prodynres BKI_DEFAULT(0); + /* number of arguments */ /* Note: need not be given in pg_proc.dat; genbki.pl will compute it */ int16 pronargs; @@ -211,7 +214,8 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, Datum proconfig, Oid prosupport, float4 procost, - float4 prorows); + float4 prorows, + int dynres); extern bool function_parse_error_transpose(const char *prosrc); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 4f7f87fc62..fcfe8df78e 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -57,6 +57,7 @@ extern ObjectAddress CreateTransform(CreateTransformStmt *stmt); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, oidvector *proargtypes, Oid nspOid); extern void ExecuteDoStmt(ParseState *pstate, DoStmt *stmt, bool atomic); +extern Oid CurrentProcedure(void); extern void ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver *dest); extern TupleDesc CallStmtResultDesc(CallStmt *stmt); extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f7d7f10f7d..acae7da708 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3010,6 +3010,7 @@ typedef struct SecLabelStmt #define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */ #define CURSOR_OPT_ASENSITIVE 0x0010 /* ASENSITIVE */ #define CURSOR_OPT_HOLD 0x0020 /* WITH HOLD */ +#define CURSOR_OPT_RETURN 0x0040 /* WITH RETURN */ /* these planner-control flags do not correspond to any SQL grammar: */ #define CURSOR_OPT_FAST_PLAN 0x0100 /* prefer fast-start plan */ #define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index bb36213e6f..60457d21f7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -144,6 +144,7 @@ PG_KEYWORD("document", DOCUMENT_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("domain", DOMAIN_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("double", DOUBLE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("drop", DROP, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("dynamic", DYNAMIC, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("each", EACH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("else", ELSE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL) @@ -353,6 +354,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("result", RESULT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index aa08b1e0fc..6f04362dfe 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -132,6 +132,16 @@ typedef struct PortalData SubTransactionId activeSubid; /* the last subxact with activity */ int createLevel; /* creating subxact's nesting level */ + /* + * Procedure that created this portal. Used for returnable cursors. + */ + Oid procId; + /* + * Command ID where the portal was created. Used for sorting returnable + * cursors into creation order. + */ + CommandId createCid; + /* The query or queries the portal will execute */ const char *sourceText; /* text of query (as of 8.4, never NULL) */ CommandTag commandTag; /* command tag for original query */ @@ -248,5 +258,7 @@ extern void PortalHashTableDeleteAll(void); extern bool ThereAreNoReadyPortals(void); extern void HoldPinnedPortals(void); extern void ForgetPortalSnapshots(void); +extern List *GetReturnableCursors(void); +extern void CloseOtherReturnableCursors(Oid procid); #endif /* PORTAL_H */ diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index f7eb42d54f..0b9686fbff 100644 --- a/src/pl/plpgsql/src/Makefile +++ b/src/pl/plpgsql/src/Makefile @@ -34,7 +34,7 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB) REGRESS = plpgsql_array plpgsql_call plpgsql_control plpgsql_copy plpgsql_domain \ plpgsql_record plpgsql_cache plpgsql_simple plpgsql_transaction \ - plpgsql_trap plpgsql_trigger plpgsql_varprops + plpgsql_trap plpgsql_trigger plpgsql_varprops plpgsql_with_return # where to find gen_keywordlist.pl and subsidiary files TOOLSDIR = $(top_srcdir)/src/tools diff --git a/src/pl/plpgsql/src/expected/plpgsql_with_return.out b/src/pl/plpgsql/src/expected/plpgsql_with_return.out new file mode 100644 index 0000000000..2f6b034e5e --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_with_return.out @@ -0,0 +1,105 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; +CALL pdrstest1(1); + ay +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest1(2); + ay +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DO $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(1); + OPEN c2; +END; +$$; + ay +---- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +-- (The result sets of the called procedure are not returned.) +DO $$ +BEGIN + CALL pdrstest1(1); +END; +$$; +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM drs_test1; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM drs_test2; + END IF; +END; +$$; +CALL pdrstest2(1); + ax +---- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest2(2); + ax +---- + 2 + 4 + 6 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +DROP TABLE drs_test1, drs_test2; diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build index e185a87024..b6fc35e23f 100644 --- a/src/pl/plpgsql/src/meson.build +++ b/src/pl/plpgsql/src/meson.build @@ -86,6 +86,7 @@ tests += { 'plpgsql_trap', 'plpgsql_trigger', 'plpgsql_varprops', + 'plpgsql_with_return', ], }, } diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ffd6d2e3bc..ea11144f6d 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4776,6 +4776,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) elog(ERROR, "could not open cursor: %s", SPI_result_code_string(SPI_result)); + if (portal->cursorOptions & CURSOR_OPT_RETURN) + { + portal->procId = estate->func->fn_oid; + portal->createCid = GetCurrentCommandId(true); + } + /* * If cursor variable was NULL, store the generated portal name in it, * after verifying it's okay to assign to. diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index edeb72c380..bff1557005 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -212,7 +212,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <datum> getdiag_target %type <ival> getdiag_item -%type <ival> opt_scrollable +%type <ival> opt_scrollable opt_with_return %type <fetch> opt_fetch_direction %type <ival> opt_transaction_chain @@ -352,6 +352,8 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_WARNING %token <keyword> K_WHEN %token <keyword> K_WHILE +%token <keyword> K_WITH +%token <keyword> K_WITHOUT %% @@ -529,7 +531,7 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull plpgsql_ns_additem($4->itemtype, $4->itemno, $1.name); } - | decl_varname opt_scrollable K_CURSOR + | decl_varname opt_scrollable K_CURSOR opt_with_return { plpgsql_ns_push($1.name, PLPGSQL_LABEL_OTHER); } decl_cursor_args decl_is_for decl_cursor_query { @@ -546,12 +548,12 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull NULL), true); - new->cursor_explicit_expr = $7; - if ($5 == NULL) + new->cursor_explicit_expr = $8; + if ($6 == NULL) new->cursor_explicit_argrow = -1; else - new->cursor_explicit_argrow = $5->dno; - new->cursor_options = CURSOR_OPT_FAST_PLAN | $2; + new->cursor_explicit_argrow = $6->dno; + new->cursor_options = CURSOR_OPT_FAST_PLAN | $2 | $4; } ; @@ -569,6 +571,20 @@ opt_scrollable : } ; +opt_with_return : + { + $$ = 0; + } + | K_WITH K_RETURN + { + $$ = CURSOR_OPT_RETURN; + } + | K_WITHOUT K_RETURN + { + $$ = 0; + } + ; + decl_cursor_query : { $$ = read_sql_stmt(); @@ -1976,6 +1992,10 @@ stmt_execsql : K_IMPORT { $$ = make_execsql_stmt(K_MERGE, @1); } + | K_WITH + { + $$ = make_execsql_stmt(K_WITH, @1); + } | T_WORD { int tok; @@ -2098,6 +2118,30 @@ stmt_open : K_OPEN cursor_variable tok = yylex(); } + /* same for opt_with_return */ + if (tok_is_keyword(tok, &yylval, + K_WITH, "with")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= CURSOR_OPT_RETURN; + tok = yylex(); + } + } + else if (tok_is_keyword(tok, &yylval, + K_WITHOUT, "without")) + { + tok = yylex(); + if (tok_is_keyword(tok, &yylval, + K_RETURN, "return")) + { + new->cursor_options |= 0; + tok = yylex(); + } + } + if (tok != K_FOR) yyerror("syntax error, expected \"FOR\""); @@ -2552,6 +2596,8 @@ unreserved_keyword : | K_USE_VARIABLE | K_VARIABLE_CONFLICT | K_WARNING + | K_WITH + | K_WITHOUT ; %% diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index 466bdc7a20..8a8f8ea47a 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -109,3 +109,5 @@ PG_KEYWORD("use_column", K_USE_COLUMN) PG_KEYWORD("use_variable", K_USE_VARIABLE) PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT) PG_KEYWORD("warning", K_WARNING) +PG_KEYWORD("with", K_WITH) +PG_KEYWORD("without", K_WITHOUT) diff --git a/src/pl/plpgsql/src/sql/plpgsql_with_return.sql b/src/pl/plpgsql/src/sql/plpgsql_with_return.sql new file mode 100644 index 0000000000..08da362bce --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_with_return.sql @@ -0,0 +1,64 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); + + +CREATE PROCEDURE pdrstest1(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(x); + IF x > 1 THEN + OPEN c2; + END IF; +END; +$$; + +CALL pdrstest1(1); +CALL pdrstest1(2); + + +DO $$ +DECLARE + c1 CURSOR WITH RETURN (y int) FOR SELECT a * y AS ay FROM drs_test1; + c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +BEGIN + OPEN c1(1); + OPEN c2; +END; +$$; + + +-- (The result sets of the called procedure are not returned.) +DO $$ +BEGIN + CALL pdrstest1(1); +END; +$$; + + +CREATE PROCEDURE pdrstest2(x int) +LANGUAGE plpgsql +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE + c1 refcursor; + c2 refcursor; +BEGIN + OPEN c1 WITH RETURN FOR SELECT a * x AS ax FROM drs_test1; + IF x > 1 THEN + OPEN c2 SCROLL WITH RETURN FOR SELECT * FROM drs_test2; + END IF; +END; +$$; + +CALL pdrstest2(1); +CALL pdrstest2(2); + + +DROP TABLE drs_test1, drs_test2; diff --git a/src/test/regress/expected/dynamic_result_sets.out b/src/test/regress/expected/dynamic_result_sets.out new file mode 100644 index 0000000000..7b2529c99e --- /dev/null +++ b/src/test/regress/expected/dynamic_result_sets.out @@ -0,0 +1,129 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); +-- return a couple of result sets from a procedure +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; +CALL pdrstest1(); + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest1() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- return too many result sets from a procedure +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; +CALL pdrstest2(); +WARNING: attempt to return too many result sets + a +--- + 1 + 2 + 3 +(3 rows) + + x | y +-----+----- + abc | def + foo | bar +(2 rows) + +CALL pdrstest2() \bind \g +WARNING: attempt to return too many result sets +ERROR: dynamic result sets are not yet supported in extended query protocol +-- nested calls +CREATE PROCEDURE pdrstest3() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM drs_test1 WHERE a < 2; +$$; +-- (The result sets of the called procedure are not returned.) +CALL pdrstest3(); + a +--- + 1 +(1 row) + +CALL pdrstest3() \bind \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- both out parameter and result sets +CREATE PROCEDURE pdrstest4(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +SELECT a || a; +$$; +CALL pdrstest4('x'); + a +---- + xx +(1 row) + + a +--- + 1 + 2 + 3 +(3 rows) + +CALL pdrstest4($1) \bind 'y' \g +ERROR: dynamic result sets are not yet supported in extended query protocol +-- test the nested error handling +CREATE TABLE drs_test_dummy (a int); +CREATE PROCEDURE pdrstest5a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; +$$; +CREATE PROCEDURE pdrstest5b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest5a(); +$$; +DROP TABLE drs_test_dummy; +CALL pdrstest5b(); +ERROR: relation "drs_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_du... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; + +CONTEXT: SQL function "pdrstest5a" during startup +SQL function "pdrstest5b" statement 1 +CALL pdrstest5b() \bind \g +ERROR: relation "drs_test_dummy" does not exist +LINE 2: DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_du... + ^ +QUERY: +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; + +CONTEXT: SQL function "pdrstest5a" during startup +SQL function "pdrstest5b" statement 1 +-- cleanup +DROP TABLE drs_test1, drs_test2; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 15e015b3d6..57f3c9b6cd 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -43,7 +43,7 @@ test: copy copyselect copydml insert insert_conflict # Note: many of the tests in later groups depend on create_index # ---------- test: create_function_c create_misc create_operator create_procedure create_table create_type -test: create_index create_index_spgist create_view index_including index_including_gist +test: create_index create_index_spgist create_view index_including index_including_gist dynamic_result_sets # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/dynamic_result_sets.sql b/src/test/regress/sql/dynamic_result_sets.sql new file mode 100644 index 0000000000..ed4a91740e --- /dev/null +++ b/src/test/regress/sql/dynamic_result_sets.sql @@ -0,0 +1,90 @@ +CREATE TABLE drs_test1 (a int); +INSERT INTO drs_test1 VALUES (1), (2), (3); +CREATE TABLE drs_test2 (x text, y text); +INSERT INTO drs_test2 VALUES ('abc', 'def'), ('foo', 'bar'); + + +-- return a couple of result sets from a procedure + +CREATE PROCEDURE pdrstest1() +LANGUAGE SQL +DYNAMIC RESULT SETS 2 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; + +CALL pdrstest1(); +CALL pdrstest1() \bind \g + + +-- return too many result sets from a procedure + +CREATE PROCEDURE pdrstest2() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM drs_test2; +$$; + +CALL pdrstest2(); +CALL pdrstest2() \bind \g + + +-- nested calls + +CREATE PROCEDURE pdrstest3() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest1(); +DECLARE c3 CURSOR WITH RETURN FOR SELECT * FROM drs_test1 WHERE a < 2; +$$; + +-- (The result sets of the called procedure are not returned.) +CALL pdrstest3(); +CALL pdrstest3() \bind \g + + +-- both out parameter and result sets + +CREATE PROCEDURE pdrstest4(INOUT a text) +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c4 CURSOR WITH RETURN FOR SELECT * FROM drs_test1; +SELECT a || a; +$$; + +CALL pdrstest4('x'); +CALL pdrstest4($1) \bind 'y' \g + + +-- test the nested error handling + +CREATE TABLE drs_test_dummy (a int); + +CREATE PROCEDURE pdrstest5a() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +DECLARE c5a CURSOR WITH RETURN FOR SELECT * FROM drs_test_dummy; +$$; + +CREATE PROCEDURE pdrstest5b() +LANGUAGE SQL +DYNAMIC RESULT SETS 1 +AS $$ +CALL pdrstest5a(); +$$; + +DROP TABLE drs_test_dummy; + +CALL pdrstest5b(); +CALL pdrstest5b() \bind \g + + +-- cleanup + +DROP TABLE drs_test1, drs_test2; base-commit: 4fc53819a45fe6e7233a69bb279557b2070dcc40 -- 2.39.2 [text/plain] v8-0002-WIP-Dynamic-result-sets-in-extended-query-protoco.patch (7.4K, ../../[email protected]/3-v8-0002-WIP-Dynamic-result-sets-in-extended-query-protoco.patch) download | inline diff: From 552a7fa9e79578b37f375579f7cb46cd897d100f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Fri, 24 Feb 2023 12:21:40 +0100 Subject: [PATCH v8 2/2] WIP: Dynamic result sets in extended query protocol This is currently broken due to/since acb7e4eb6b. TODO: consider minor protocol version bump (3.1) --- doc/src/sgml/protocol.sgml | 19 +++++++++++ src/backend/tcop/postgres.c | 32 ++++++++++++++++--- src/backend/tcop/pquery.c | 6 ++++ src/include/utils/portal.h | 2 ++ src/interfaces/libpq/fe-protocol3.c | 6 ++-- .../regress/expected/dynamic_result_sets.out | 31 +++++++++++++++--- 6 files changed, 84 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 93fc7167d4..ec605b12b5 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -959,6 +959,25 @@ <title>Extended Query</title> an empty query string), ErrorResponse, or PortalSuspended. </para> + <para> + Executing a portal may give rise to a <firstterm>dynamic result set + sequence</firstterm>. That means the command contained in the portal + created additional result sets beyond what it normally returns. (The + typical example is calling a stored procedure that creates dynamic result + sets.) Dynamic result sets are issued after whatever response the main + command issued. Each dynamic result set begins with a RowDescription + message followed by zero or more DataRow messages. (Since, as explained + above, an Execute message normally does not respond with a RowDescription, + the appearance of the first RowDescription marks the end of the primary + result set of the portal and the beginning of the first dynamic result + set.) The CommandComplete message that concludes the Execute message + response follows <emphasis>after</emphasis> all dynamic result sets. Note + that dynamic result sets cannot, by their nature, be decribed prior to the + execution of the portal. Multiple executions of the same prepared + statement could result in dynamic result sets with different row + descriptions being returned. + </para> + <para> At completion of each series of extended-query messages, the frontend should issue a Sync message. This parameterless message causes the diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 98ac9aa012..89da5c7512 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -2086,6 +2086,7 @@ exec_execute_message(const char *portal_name, long max_rows) const char *sourceText; const char *prepStmtName; ParamListInfo portalParams; + ListCell *lc; bool save_log_statement_stats = log_statement_stats; bool is_xact_command; bool execute_is_fetch; @@ -2226,10 +2227,33 @@ exec_execute_message(const char *portal_name, long max_rows) receiver, &qc); - if (GetReturnableCursors()) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("dynamic result sets are not yet supported in extended query protocol")); + /* + * Run portals for dynamic result sets. + */ + foreach (lc, GetReturnableCursors()) + { + Portal dyn_portal = lfirst(lc); + + if (dest == DestRemoteExecute) + SetRemoteDestReceiverParams(receiver, dyn_portal); + + PortalSetResultFormat(dyn_portal, 1, &portal->dynamic_format); + + SendRowDescriptionMessage(&row_description_buf, + dyn_portal->tupDesc, + FetchPortalTargetList(dyn_portal), + dyn_portal->formats); + + PortalRun(dyn_portal, + FETCH_ALL, + true, + true, + receiver, + receiver, + NULL); + + PortalDrop(dyn_portal, false); + } receiver->rDestroy(receiver); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5f0248acc5..6469940935 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -641,6 +641,8 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) errmsg("bind message has %d result formats but query has %d columns", nFormats, natts))); memcpy(portal->formats, formats, natts * sizeof(int16)); + + portal->dynamic_format = 0; } else if (nFormats > 0) { @@ -649,12 +651,16 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats) for (i = 0; i < natts; i++) portal->formats[i] = format1; + + portal->dynamic_format = format1; } else { /* use default format for all columns */ for (i = 0; i < natts; i++) portal->formats[i] = 0; + + portal->dynamic_format = 0; } } diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index 6f04362dfe..55406b8654 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -170,6 +170,8 @@ typedef struct PortalData TupleDesc tupDesc; /* descriptor for result tuples */ /* and these are the format codes to use for the columns: */ int16 *formats; /* a format code for each column */ + /* Format code for dynamic result sets */ + int16 dynamic_format; /* * Outermost ActiveSnapshot for execution of the portal's queries. For diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 8ab6a88416..863a09cf74 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -320,10 +320,8 @@ pqParseInput3(PGconn *conn) { /* * A new 'T' message is treated as the start of - * another PGresult. (It is not clear that this is - * really possible with the current backend.) We stop - * parsing until the application accepts the current - * result. + * another PGresult. We stop parsing until the + * application accepts the current result. */ conn->asyncStatus = PGASYNC_READY; return; diff --git a/src/test/regress/expected/dynamic_result_sets.out b/src/test/regress/expected/dynamic_result_sets.out index 7b2529c99e..3584f1ec8c 100644 --- a/src/test/regress/expected/dynamic_result_sets.out +++ b/src/test/regress/expected/dynamic_result_sets.out @@ -25,7 +25,14 @@ CALL pdrstest1(); (2 rows) CALL pdrstest1() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) -- return too many result sets from a procedure CREATE PROCEDURE pdrstest2() LANGUAGE SQL @@ -51,7 +58,14 @@ WARNING: attempt to return too many result sets CALL pdrstest2() \bind \g WARNING: attempt to return too many result sets -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 + 2 + 3 +(3 rows) + +server sent data ("D" message) without prior row description ("T" message) -- nested calls CREATE PROCEDURE pdrstest3() LANGUAGE SQL @@ -68,7 +82,11 @@ CALL pdrstest3(); (1 row) CALL pdrstest3() \bind \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +--- + 1 +(1 row) + -- both out parameter and result sets CREATE PROCEDURE pdrstest4(INOUT a text) LANGUAGE SQL @@ -91,7 +109,12 @@ CALL pdrstest4('x'); (3 rows) CALL pdrstest4($1) \bind 'y' \g -ERROR: dynamic result sets are not yet supported in extended query protocol + a +---- + yy +(1 row) + +server sent data ("D" message) without prior row description ("T" message) -- test the nested error handling CREATE TABLE drs_test_dummy (a int); CREATE PROCEDURE pdrstest5a() -- 2.39.2 ^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v47 7/9] Fix a few problems in index build progress reporting. @ 2026-03-27 15:50 Álvaro Herrera <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Álvaro Herrera @ 2026-03-27 15:50 UTC (permalink / raw) First, index_build() should not update the progress when being driven by REPACK, because the progress reporting infractructure cannot handle status of two commands at the same time. So far, REPACK with the CONCURRENTLY option neglected this problem altogether, but even the existing REPACK wasn't consistent enough: even if the 'progress' variable in repack_index() was false, it didn't pass the value to index_build(). Second, REPACK (CONCURRENTLY) should not set PROGRESS_REPACK_PHASE to PROGRESS_REPACK_PHASE_FINAL_CLEANUP in rebuild_relation() because it calls finish_heap_swap() anyway (via rebuild_relation_finish_concurrent()), which does the same thing. --- src/backend/bootstrap/bootstrap.c | 2 +- src/backend/catalog/heap.c | 3 ++- src/backend/catalog/index.c | 22 ++++++++++++++++++---- src/backend/catalog/toasting.c | 3 ++- src/backend/commands/indexcmds.c | 1 + src/include/catalog/index.h | 4 +++- 6 files changed, 27 insertions(+), 8 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 38ef683d4c7..60fb7051830 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -1183,7 +1183,7 @@ build_indices(void) heap = table_open(ILHead->il_heap, NoLock); ind = index_open(ILHead->il_ind, NoLock); - index_build(heap, ind, ILHead->il_info, false, false); + index_build(heap, ind, ILHead->il_info, false, false, false); index_close(ind, NoLock); table_close(heap, NoLock); diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 5748aa9a1a9..ae6b7cda3dd 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -3570,7 +3570,8 @@ RelationTruncateIndexes(Relation heapRelation) /* Initialize the index and rebuild */ /* Note: we do not need to re-establish pkey setting */ - index_build(heapRelation, currentIndex, indexInfo, true, false); + index_build(heapRelation, currentIndex, indexInfo, true, false, + true); /* We're done with this index */ index_close(currentIndex, NoLock); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index b86ad73c626..bac8d527ae5 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -715,6 +715,9 @@ UpdateIndexRelation(Oid indexoid, * already exists. * INDEX_CREATE_PARTITIONED: * create a partitioned index (table must be partitioned) + * INDEX_CREATE_REPORT_PROGRESS: + * update the backend's progress information during index build. + * constr_flags: flags passed to index_constraint_create * (only if INDEX_CREATE_ADD_CONSTRAINT is set) * allow_system_table_mods: allow table to be a system catalog @@ -760,6 +763,7 @@ index_create(Relation heapRelation, bool invalid = (flags & INDEX_CREATE_INVALID) != 0; bool concurrent = (flags & INDEX_CREATE_CONCURRENT) != 0; bool partitioned = (flags & INDEX_CREATE_PARTITIONED) != 0; + bool progress = (flags & INDEX_CREATE_REPORT_PROGRESS) != 0; char relkind; TransactionId relfrozenxid; MultiXactId relminmxid; @@ -1276,7 +1280,8 @@ index_create(Relation heapRelation, } else { - index_build(heapRelation, indexRelation, indexInfo, false, true); + index_build(heapRelation, indexRelation, indexInfo, false, true, + progress); } /* @@ -1438,6 +1443,12 @@ index_create_copy(Relation heapRelation, bool concurrently, stattargets[i].isnull = isnull; } + /* + * Note: The current callers do not need INDEX_CREATE_REPORT_PROGRESS. If + * 'concurrently' is true, there is no build at all. Otherwise the index + * build is a sub-command of REPACK. The current infrastructure does not + * allow two commands to report their progress at the same time. + */ if (concurrently) flags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT; @@ -1528,7 +1539,7 @@ index_concurrently_build(Oid heapRelationId, indexInfo->ii_BrokenHotChain = false; /* Now build the index */ - index_build(heapRel, indexRelation, indexInfo, false, true); + index_build(heapRel, indexRelation, indexInfo, false, true, true); /* Roll back any GUC changes executed by index functions */ AtEOXact_GUC(false, save_nestlevel); @@ -3001,6 +3012,7 @@ index_update_stats(Relation rel, * * isreindex indicates we are recreating a previously-existing index. * parallel indicates if parallelism may be useful. + * progress indicates if the backend should update its progress info. * * Note: before Postgres 8.2, the passed-in heap and index Relations * were automatically closed by this routine. This is no longer the case. @@ -3011,7 +3023,8 @@ index_build(Relation heapRelation, Relation indexRelation, IndexInfo *indexInfo, bool isreindex, - bool parallel) + bool parallel, + bool progress) { IndexBuildResult *stats; Oid save_userid; @@ -3062,6 +3075,7 @@ index_build(Relation heapRelation, RestrictSearchPath(); /* Set up initial progress report status */ + if (progress) { const int progress_index[] = { PROGRESS_CREATEIDX_PHASE, @@ -3819,7 +3833,7 @@ reindex_index(const ReindexStmt *stmt, Oid indexId, /* Initialize the index and rebuild */ /* Note: we do not need to re-establish pkey setting */ - index_build(heapRelation, iRel, indexInfo, true, true); + index_build(heapRelation, iRel, indexInfo, true, true, progress); /* Re-allow use of target index */ ResetReindexProcessing(); diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 078a1cf5127..73c41360ae9 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -331,7 +331,8 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, BTREE_AM_OID, rel->rd_rel->reltablespace, collationIds, opclassIds, NULL, coloptions, NULL, (Datum) 0, - INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL); + INDEX_CREATE_IS_PRIMARY | INDEX_CREATE_REPORT_PROGRESS, 0, + true, true, NULL); table_close(toast_rel, NoLock); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 4a2d21915b1..25e8c1945c5 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1233,6 +1233,7 @@ DefineIndex(ParseState *pstate, flags |= INDEX_CREATE_PARTITIONED; if (stmt->primary) flags |= INDEX_CREATE_IS_PRIMARY; + flags |= INDEX_CREATE_REPORT_PROGRESS; /* * If the table is partitioned, and recursion was declined but partitions diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index ed9e4c37d27..7ebe4f0bd87 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -71,6 +71,7 @@ extern void index_check_primary_key(Relation heapRel, #define INDEX_CREATE_IF_NOT_EXISTS (1 << 4) #define INDEX_CREATE_PARTITIONED (1 << 5) #define INDEX_CREATE_INVALID (1 << 6) +#define INDEX_CREATE_REPORT_PROGRESS (1 << 7) extern Oid index_create(Relation heapRelation, const char *indexRelationName, @@ -148,7 +149,8 @@ extern void index_build(Relation heapRelation, Relation indexRelation, IndexInfo *indexInfo, bool isreindex, - bool parallel); + bool parallel, + bool progress); extern void validate_index(Oid heapId, Oid indexId, Snapshot snapshot); -- 2.47.3 --2fxmamo6mu2qbxgv Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v47-0008-Error-out-any-process-that-would-block-at-REPACK.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-03-27 15:50 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-01-30 13:06 Re: dynamic result sets support in extended query protocol Alvaro Herrera <[email protected]> 2023-01-31 11:07 ` Peter Eisentraut <[email protected]> 2023-02-20 12:58 ` Peter Eisentraut <[email protected]> 2023-02-24 11:26 ` Peter Eisentraut <[email protected]> 2026-03-27 15:50 [PATCH v47 7/9] Fix a few problems in index build progress reporting. Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox