[BUGS] BUG #14275: cursor's variable in pgsql doesn't respect scope

[kli...@tut.by]

The following bug has been logged on the website:

Bug reference: 14275
Logged by: Oleg Klimovich
Email address: kli...@tut.by
PostgreSQL version: 9.5.3
Operating system: Windows 7, Windows 8, Ubuntu 14.04
Description:

DO $$
DECLARE
cur cursor for select 1; -- (1)
BEGIN
open cur; -- (2)

DECLARE
cur cursor for select 2; -- (3)
BEGIN
open cur; -- (4)
close cur; -- (5)
END;

close cur; -- (6)
end $$;

Executing of the code gives error "cursor "cur" already in use". Evedently,
PG in statement (4) refers to the variable, defined in statement (1). (and I
expect it should be variable, defined in statement (3)).
Futhermore, same error exists even across different functions:

create function func1() returns void as $$
declare
cur cursor for select 1;
BEGIN
open cur;
close cur;
end
$$
LANGUAGE 'plpgsql';

create function func2() returns void as $$
declare
cur cursor for select 1;
BEGIN
open cur;

PERFORM func1();

close cur;
end
$$
LANGUAGE 'plpgsql';

select func2();

So, cursor's variable is kind of global. I just hope it's a bug and not
"feature" (at least I haven't found mention of such behaviour in
documentation)

--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Andrew Gierth]

>>>>> "klimych" == klimych <kli...@tut.by> writes:

klimych> Executing of the code gives error "cursor "cur" already in
klimych> use".

The cursor name (portal name) is global to the session, and for a bound
cursor it defaults to the name of the cursor variable:

40.7.3.5. Returning Cursors

[...]

Note: A bound cursor variable is initialized to the string value
representing its name, so that the portal name is the same as the
cursor variable name, unless the programmer overrides it by assignment
before opening the cursor. But an unbound cursor variable defaults to
the null value initially, so it will receive an
automatically-generated unique name, unless overridden.

It's a bit ugly, but you can do

declare
cur for select 1;
begin
cur := null; -- force a unique generated portal name
open cur;
//...

--
Andrew (irc:RhodiumToad)

[Pavel Stehule]

It is feature - PLpgSQL engine uses named cursor accessed via SPI API. The name of SPI cursor is generated by cursor variable name. SPI API has zero relation to plpgsql block structure.
 

See source code pl_exec.c exec_stmt_open

Regards

Pavel
 

[kli...@tut.by]

Wow. Thank you! Sorry, i should read the documentation more carefully

03.08.2016, 16:04, "Pavel Stehule" <pavel....@gmail.com>:

[kli...@tut.by]

Thank you!
Sorry, I should read the documentation more carefully (athough I didnt't expect to find explanation of such behaviour in "Returning Cursors" section, as well as I didn't belive such behaviour could be made on purpose).
And thanks again for workaround! It seems to be the only way to use cursors in pl/pgsql (the weirdest thing I've ever seen, I should say)

03.08.2016, 16:03, "Andrew Gierth" <and...@tao11.riddles.org.uk>:

>>>>>>  "klimych" == klimych <kli...@tut.by> writes:
>
>  klimych> Executing of the code gives error "cursor "cur" already in
>  klimych> use".
>
> The cursor name (portal name) is global to the session, and for a bound
> cursor it defaults to the name of the cursor variable:
>
> 40.7.3.5. Returning Cursors
>
>   [...]
>
>   Note: A bound cursor variable is initialized to the string value
>   representing its name, so that the portal name is the same as the
>   cursor variable name, unless the programmer overrides it by assignment
>   before opening the cursor. But an unbound cursor variable defaults to
>   the null value initially, so it will receive an
>   automatically-generated unique name, unless overridden.
>
> It's a bit ugly, but you can do
>
> declare
>   cur for select 1;
> begin
>   cur := null; -- force a unique generated portal name
>   open cur;
>   //...
>
> --
> Andrew (irc:RhodiumToad)

[Pavel Stehule]

2016-08-04 15:03 GMT+02:00 kli...@tut.by <kli...@tut.by>:

Thank you!
Sorry, I should read the documentation more carefully (athough I didnt't expect to find explanation of such behaviour in "Returning Cursors" section, as well as I didn't belive such behaviour could be made on purpose).
And thanks again for workaround! It seems to be the only way to use cursors in pl/pgsql (the weirdest thing I've ever seen, I should say)

I agree so this is little bit strange - it looks like workaround of some historical limit of SPI. It is too late to change. But it has some advantage. Postgres can't to pass parameters by ref. With named cursors you can do it.

Regards

Pavel
 

[David G. Johnston]

On Thu, Aug 4, 2016 at 10:10 AM, Pavel Stehule <pavel....@gmail.com> wrote:

2016-08-04 15:03 GMT+02:00 kli...@tut.by <kli...@tut.by>:

Thank you!
Sorry, I should read the documentation more carefully (athough I didnt't expect to find explanation of such behaviour in "Returning Cursors" section, as well as I didn't belive such behaviour could be made on purpose).
And thanks again for workaround! It seems to be the only way to use cursors in pl/pgsql (the weirdest thing I've ever seen, I should say)

I agree so this is little bit strange - it looks like workaround of some historical limit of SPI. It is too late to change. But it has some advantage. Postgres can't to pass parameters by ref. With named cursors you can do it.

​The docs could maybe be improved, though it is documented and being mis-informed simply results in an error and a question on the lists, so expending get mental effort here isn't that appealing.

Improving the code would involve something like:

OPEN unbound_cursorvar [  [ NO ] SCROLL ] [ NAME system_name ] FOR query

Also, would a hint on the error message be too much to ask?

HINT:  by default the global name of the cursor is equal to the variable name to which it is assigned

David J.

​