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

"klimych" == klimych <klimych@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)

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

2016-08-03 11:53 GMT+02:00 <klimych@tut.by>:

The following bug has been logged on the website:

Bug reference: 14275
Logged by: Oleg Klimovich
Email address: klimych@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)

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

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

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

2016-08-03 11:53 GMT+02:00 <klimych@tut.by>:

The following bug has been logged on the website:

Bug reference:      14275
Logged by:          Oleg Klimovich
Email address:      klimych@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)

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

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

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

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" <andrew@tao11.riddles.org.uk>:

 "klimych" == klimych <klimych@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)

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

2016-08-04 15:03 GMT+02:00 klimych@tut.by <klimych@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

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

2016-08-04 15:03 GMT+02:00 klimych@tut.by <klimych@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.

​