Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit d747dc8

Browse filesBrowse files
tglsfdctglsfdc
committed
In plpgsql, don't preassign portal names to bound cursor variables.
A refcursor variable that is bound to a specific query (by declaring it with "CURSOR FOR") now chooses a portal name in the same way as an unbound, plain refcursor variable. Its string value starts out as NULL, and unless that's overridden by manual assignment, it will be replaced by a unique-within-session portal name during OPEN. The previous behavior was to initialize such variables to contain their own name, resulting in that also being the portal name unless the user overwrote it before OPEN. The trouble with this is that it causes failures due to conflicting portal names if the same cursor variable name is used in different functions. It is pretty non-orthogonal to have bound and unbound refcursor variables behave differently on this point, too, so let's change it. This change can cause compatibility problems for applications that open a bound cursor in a plpgsql function and then use it in the calling code without explicitly passing back the refcursor value (portal name). If the calling code simply assumes that the portal name matches the called function's variable name, it will now fail. That can be fixed by explicitly assigning a string value to the refcursor variable before OPEN, e.g. DECLARE myc CURSOR FOR SELECT ...; BEGIN myc := 'myc'; -- add this OPEN myc; We have no documentation examples showing the troublesome usage pattern, so we can hope it's rare in practice. Patch by me; thanks to Pavel Stehule and Jan Wieck for review. Discussion: https://postgr.es/m/1465101.1667345983@sss.pgh.pa.us
1 parent 14d63dd commit d747dc8
Copy full SHA for d747dc8

File tree

5 files changed

+87
-37
lines changed
Filter options

5 files changed

+87
-37
lines changed

‎doc/src/sgml/plpgsql.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/plpgsql.sgml
+32-10Lines changed: 32 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3177,7 +3177,9 @@ DECLARE
31773177
<para>
31783178
Before a cursor can be used to retrieve rows, it must be
31793179
<firstterm>opened</firstterm>. (This is the equivalent action to the SQL
3180-
command <command>DECLARE CURSOR</command>.) <application>PL/pgSQL</application> has
3180+
command <link linkend="sql-declare"><command>DECLARE
3181+
CURSOR</command></link>.)
3182+
<application>PL/pgSQL</application> has
31813183
three forms of the <command>OPEN</command> statement, two of which use unbound
31823184
cursor variables while the third uses a bound cursor variable.
31833185
</para>
@@ -3187,9 +3189,28 @@ DECLARE
31873189
Bound cursor variables can also be used without explicitly opening the cursor,
31883190
via the <command>FOR</command> statement described in
31893191
<xref linkend="plpgsql-cursor-for-loop"/>.
3192+
A <command>FOR</command> loop will open the cursor and then
3193+
close it again when the loop completes.
31903194
</para>
31913195
</note>
31923196

3197+
<indexterm>
3198+
<primary>portal</primary>
3199+
<secondary>in PL/pgSQL</secondary>
3200+
</indexterm>
3201+
3202+
<para>
3203+
Opening a cursor involves creating a server-internal data structure
3204+
called a <firstterm>portal</firstterm>, which holds the execution
3205+
state for the cursor's query. A portal has a name, which must be
3206+
unique within the session for the duration of the portal's existence.
3207+
By default, <application>PL/pgSQL</application> will assign a unique
3208+
name to each portal it creates. However, if you assign a non-null
3209+
string value to a cursor variable, that string will be used as its
3210+
portal name. This feature can be used as described in
3211+
<xref linkend="plpgsql-cursor-returning"/>.
3212+
</para>
3213+
31933214
<sect3>
31943215
<title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
31953216

@@ -3338,7 +3359,7 @@ BEGIN
33383359
opened the cursor to begin with. You can return a <type>refcursor</type>
33393360
value out of a function and let the caller operate on the cursor.
33403361
(Internally, a <type>refcursor</type> value is simply the string name
3341-
of a so-called portal containing the active query for the cursor. This name
3362+
of the portal containing the active query for the cursor. This name
33423363
can be passed around, assigned to other <type>refcursor</type> variables,
33433364
and so on, without disturbing the portal.)
33443365
</para>
@@ -3480,7 +3501,7 @@ CLOSE curs1;
34803501
</para>
34813502
</sect3>
34823503

3483-
<sect3>
3504+
<sect3 id="plpgsql-cursor-returning">
34843505
<title>Returning Cursors</title>
34853506

34863507
<para>
@@ -3500,20 +3521,21 @@ CLOSE curs1;
35003521
simply assign a string to the <type>refcursor</type> variable before
35013522
opening it. The string value of the <type>refcursor</type> variable
35023523
will be used by <command>OPEN</command> as the name of the underlying portal.
3503-
However, if the <type>refcursor</type> variable is null,
3524+
However, if the <type>refcursor</type> variable's value is null
3525+
(as it will be by default), then
35043526
<command>OPEN</command> automatically generates a name that does not
35053527
conflict with any existing portal, and assigns it to the
35063528
<type>refcursor</type> variable.
35073529
</para>
35083530

35093531
<note>
35103532
<para>
3511-
A bound cursor variable is initialized to the string value
3512-
representing its name, so that the portal name is the same as
3513-
the cursor variable name, unless the programmer overrides it
3514-
by assignment before opening the cursor. But an unbound cursor
3515-
variable defaults to the null value initially, so it will receive
3516-
an automatically-generated unique name, unless overridden.
3533+
Prior to <productname>PostgreSQL</productname> 16, bound cursor
3534+
variables were initialized to contain their own names, rather
3535+
than being left as null, so that the underlying portal name would
3536+
be the same as the cursor variable's name by default. This was
3537+
changed because it created too much risk of conflicts between
3538+
similarly-named cursors in different functions.
35173539
</para>
35183540
</note>
35193541

‎doc/src/sgml/ref/declare.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/declare.sgml
+16Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,11 @@ PostgreSQL documentation
1313
<secondary>DECLARE</secondary>
1414
</indexterm>
1515

16+
<indexterm>
17+
<primary>portal</primary>
18+
<secondary>DECLARE</secondary>
19+
</indexterm>
20+
1621
<refmeta>
1722
<refentrytitle>DECLARE</refentrytitle>
1823
<manvolnum>7</manvolnum>
@@ -61,6 +66,8 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIV
6166
<listitem>
6267
<para>
6368
The name of the cursor to be created.
69+
This must be different from any other active cursor name in the
70+
session.
6471
</para>
6572
</listitem>
6673
</varlistentry>
@@ -305,6 +312,15 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIV
305312
<command>DECLARE</command> and <command>OPEN</command> statements.
306313
</para>
307314

315+
<para>
316+
The server data structure underlying an open cursor is called a
317+
<firstterm>portal</firstterm>. Portal names are exposed in the
318+
client protocol: a client can fetch rows directly from an open
319+
portal, if it knows the portal name. When creating a cursor with
320+
<command>DECLARE</command>, the portal name is the same as the
321+
cursor name.
322+
</para>
323+
308324
<para>
309325
You can see all available cursors by querying the <link
310326
linkend="view-pg-cursors"><structname>pg_cursors</structname></link>

‎src/pl/plpgsql/src/pl_gram.y

Copy file name to clipboardExpand all lines: src/pl/plpgsql/src/pl_gram.y
-27Lines changed: 0 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -534,10 +534,6 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
534534
decl_cursor_args decl_is_for decl_cursor_query
535535
{
536536
PLpgSQL_var *new;
537-
PLpgSQL_expr *curname_def;
538-
char buf[NAMEDATALEN * 2 + 64];
539-
char *cp1;
540-
char *cp2;
541537

542538
/* pop local namespace for cursor args */
543539
plpgsql_ns_pop();
@@ -550,29 +546,6 @@ decl_statement : decl_varname decl_const decl_datatype decl_collate decl_notnull
550546
NULL),
551547
true);
552548

553-
curname_def = palloc0(sizeof(PLpgSQL_expr));
554-
555-
/* Note: refname has been truncated to NAMEDATALEN */
556-
cp1 = new->refname;
557-
cp2 = buf;
558-
/*
559-
* Don't trust standard_conforming_strings here;
560-
* it might change before we use the string.
561-
*/
562-
if (strchr(cp1, '\\') != NULL)
563-
*cp2++ = ESCAPE_STRING_SYNTAX;
564-
*cp2++ = '\'';
565-
while (*cp1)
566-
{
567-
if (SQL_STR_DOUBLE(*cp1, true))
568-
*cp2++ = *cp1;
569-
*cp2++ = *cp1++;
570-
}
571-
strcpy(cp2, "'::pg_catalog.refcursor");
572-
curname_def->query = pstrdup(buf);
573-
curname_def->parseMode = RAW_PARSE_PLPGSQL_EXPR;
574-
new->default_val = curname_def;
575-
576549
new->cursor_explicit_expr = $7;
577550
if ($5 == NULL)
578551
new->cursor_explicit_argrow = -1;

‎src/test/regress/expected/plpgsql.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/plpgsql.out
+19Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3482,6 +3482,9 @@ declare
34823482
c2 cursor
34833483
for select * from generate_series(41,43) i;
34843484
begin
3485+
-- assign portal names to cursors to get stable output
3486+
c := 'c';
3487+
c2 := 'c2';
34853488
for r in c(5,7) loop
34863489
raise notice '% from %', r.i, c;
34873490
end loop;
@@ -3624,6 +3627,22 @@ select * from forc_test;
36243627
(10 rows)
36253628

36263629
drop function forc01();
3630+
-- it's okay to re-use a cursor variable name, even when bound
3631+
do $$
3632+
declare cnt int := 0;
3633+
c1 cursor for select * from forc_test;
3634+
begin
3635+
for r1 in c1 loop
3636+
declare c1 cursor for select * from forc_test;
3637+
begin
3638+
for r2 in c1 loop
3639+
cnt := cnt + 1;
3640+
end loop;
3641+
end;
3642+
end loop;
3643+
raise notice 'cnt = %', cnt;
3644+
end $$;
3645+
NOTICE: cnt = 100
36273646
-- fail because cursor has no query bound to it
36283647
create or replace function forc_bad() returns void as $$
36293648
declare

‎src/test/regress/sql/plpgsql.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/plpgsql.sql
+20Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2929,6 +2929,9 @@ declare
29292929
c2 cursor
29302930
for select * from generate_series(41,43) i;
29312931
begin
2932+
-- assign portal names to cursors to get stable output
2933+
c := 'c';
2934+
c2 := 'c2';
29322935
for r in c(5,7) loop
29332936
raise notice '% from %', r.i, c;
29342937
end loop;
@@ -3002,6 +3005,23 @@ select * from forc_test;
30023005

30033006
drop function forc01();
30043007

3008+
-- it's okay to re-use a cursor variable name, even when bound
3009+
3010+
do $$
3011+
declare cnt int := 0;
3012+
c1 cursor for select * from forc_test;
3013+
begin
3014+
for r1 in c1 loop
3015+
declare c1 cursor for select * from forc_test;
3016+
begin
3017+
for r2 in c1 loop
3018+
cnt := cnt + 1;
3019+
end loop;
3020+
end;
3021+
end loop;
3022+
raise notice 'cnt = %', cnt;
3023+
end $$;
3024+
30053025
-- fail because cursor has no query bound to it
30063026

30073027
create or replace function forc_bad() returns void as $$

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.