Обсуждение: BUG #14275: cursor's variable in pgsql doesn't respect scope
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI3NQpMb2dnZWQgYnk6ICAg ICAgICAgIE9sZWcgS2xpbW92aWNoCkVtYWlsIGFkZHJlc3M6ICAgICAga2xp bXljaEB0dXQuYnkKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuMwpPcGVyYXRp bmcgc3lzdGVtOiAgIFdpbmRvd3MgNywgV2luZG93cyA4LCBVYnVudHUgMTQu MDQKRGVzY3JpcHRpb246ICAgICAgICAKCkRPICQkDQpERUNMQVJFDQogY3Vy IGN1cnNvciBmb3Igc2VsZWN0IDE7IC0tICgxKQ0KQkVHSU4NCiAgb3BlbiBj dXI7IC0tICgyKQ0KDQogIERFQ0xBUkUNCiAgICBjdXIgY3Vyc29yIGZvciBz ZWxlY3QgMjsgLS0gKDMpDQogIEJFR0lODQogICAgb3BlbiBjdXI7IC0tICg0 KQ0KICAgIGNsb3NlIGN1cjsgLS0gKDUpDQogIEVORDsNCg0KICBjbG9zZSBj dXI7IC0tICg2KQ0KZW5kICQkOw0KDQpFeGVjdXRpbmcgb2YgdGhlIGNvZGUg Z2l2ZXMgZXJyb3IgImN1cnNvciAiY3VyIiBhbHJlYWR5IGluIHVzZSIuIEV2 ZWRlbnRseSwKUEcgaW4gc3RhdGVtZW50ICg0KSByZWZlcnMgdG8gdGhlIHZh cmlhYmxlLCBkZWZpbmVkIGluIHN0YXRlbWVudCAoMSkuIChhbmQgSQpleHBl Y3QgaXQgc2hvdWxkIGJlIHZhcmlhYmxlLCBkZWZpbmVkIGluIHN0YXRlbWVu dCAoMykpLg0KRnV0aGVybW9yZSwgc2FtZSBlcnJvciBleGlzdHMgZXZlbiBh Y3Jvc3MgZGlmZmVyZW50IGZ1bmN0aW9uczoNCg0KY3JlYXRlIGZ1bmN0aW9u IGZ1bmMxKCkgcmV0dXJucyB2b2lkIGFzICQkDQpkZWNsYXJlDQogIGN1ciBj dXJzb3IgZm9yIHNlbGVjdCAxOw0KQkVHSU4NCiAgb3BlbiBjdXI7DQogIGNs b3NlIGN1cjsNCmVuZA0KJCQNCiBMQU5HVUFHRSAncGxwZ3NxbCc7DQoNCmNy ZWF0ZSBmdW5jdGlvbiBmdW5jMigpIHJldHVybnMgdm9pZCBhcyAkJA0KZGVj bGFyZQ0KICBjdXIgY3Vyc29yIGZvciBzZWxlY3QgMTsNCkJFR0lODQogIG9w ZW4gY3VyOw0KDQogIFBFUkZPUk0gZnVuYzEoKTsNCg0KICBjbG9zZSBjdXI7 DQplbmQNCiQkDQogTEFOR1VBR0UgJ3BscGdzcWwnOw0KDQpzZWxlY3QgZnVu YzIoKTsgDQoNClNvLCBjdXJzb3IncyB2YXJpYWJsZSBpcyBraW5kIG9mIGds b2JhbC4gSSBqdXN0IGhvcGUgaXQncyBhIGJ1ZyBhbmQgbm90CiJmZWF0dXJl IiAoYXQgbGVhc3QgSSBoYXZlbid0IGZvdW5kIG1lbnRpb24gb2Ygc3VjaCBi ZWhhdmlvdXIgaW4KZG9jdW1lbnRhdGlvbikKCg==
>>>>> "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)
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 >
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 variablename. 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
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)
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 > > 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 >
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 y= ou > can do it. > > =E2=80=8BThe docs could maybe be improved, though it is documented and bein= g 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. =E2=80=8B