From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: In plpgsql, don't preassign portal names to bound cursor variabl
Date: Sun, 01 Jan 2023 18:22:48 +0000
Message-ID: <E1pC2yu-001Ydg-Ee@gemulon.postgresql.org> (raw)
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/d747dc85aec536c471fd7c739695e155627b08fd
Modified Files
--------------
doc/src/sgml/plpgsql.sgml | 42 ++++++++++++++++++++++++++---------
doc/src/sgml/ref/declare.sgml | 16 +++++++++++++
src/pl/plpgsql/src/pl_gram.y | 27 ----------------------
src/test/regress/expected/plpgsql.out | 19 ++++++++++++++++
src/test/regress/sql/plpgsql.sql | 20 +++++++++++++++++
5 files changed, 87 insertions(+), 37 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: pgsql: In plpgsql, don't preassign portal names to bound cursor variabl
In-Reply-To: <E1pC2yu-001Ydg-Ee@gemulon.postgresql.org>
References: <E1pC2yu-001Ydg-Ee@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox