Re: [HACKERS] [GENERAL] Fun with Cursors- how to rewind a cursor
I wrote: > I'd venture that we should try to get rid of the restriction, but I'm > unsure whether removing the error check is sufficient or whether there > are real problems it's preventing. I did a little experimentation and it seems that DECLARE CURSOR, FETCH, and CLOSE work perfectly fine when executed through SPI (after diking out the error check). So I see no reason to have that prohibition in place --- we may as well just take it out and save a few lines of code. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Fun with Cursors- how to rewind a cursor
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Hm, you're right. This arises from the fact that _SPI_execute_plan >> rejects cursor-related utility statements. While I'd never stopped >> to question that before, it does seem like this restriction is a >> bit pointless. Does anyone remember why it's like that? > Is there anything to do on this item? I dug in the archives and realized that SPI was originally written by Vadim, not Jan as I'd been thinking, so there's nobody left on the project who has any special insight into this. I found this message describing it: http://archives.postgresql.org/pgsql-hackers/1997-08/msg00338.php wherein Vadim says >>> 4. I have some code for server programming interface (SPI) >>> which allows to run queries from user defined C-functions. >>> With current postgres limitations (no nested transactions, cursors >>> inside BEGIN/END only) SPI disallows using of BEGIN/END & cursors. >>> It's bad for procedures but it's enough for triggers! so it seems he saw this just as an implementation restriction rather than a fundamental property of SPI. And I don't see why cursors being within-transaction only means SPI shouldn't touch them --- maybe he just wasn't thinking carefully about that. I'd venture that we should try to get rid of the restriction, but I'm unsure whether removing the error check is sufficient or whether there are real problems it's preventing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Fun with Cursors- how to rewind a cursor
Tom Lane wrote: > "Postgres User" <[EMAIL PROTECTED]> writes: > > On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote: > >> "Postgres User" <[EMAIL PROTECTED]> writes: > >>> ref_entry = 'c_entry'; > >>> MOVE Backward All In c_entry; > >> > >> You have to use EXECUTE for the latter. > > > I had tried several variations of MOVE Backward inside an Execute > > statement earlier. And now, I'm seeing this error appear again: > > ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL > > Hm, you're right. This arises from the fact that _SPI_execute_plan > rejects cursor-related utility statements. While I'd never stopped > to question that before, it does seem like this restriction is a > bit pointless. Does anyone remember why it's like that? Is there anything to do on this item? -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Fun with Cursors- how to rewind a cursor
"Postgres User" <[EMAIL PROTECTED]> writes: > On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Postgres User" <[EMAIL PROTECTED]> writes: >>> ref_entry = 'c_entry'; >>> MOVE Backward All In c_entry; >> >> You have to use EXECUTE for the latter. > I had tried several variations of MOVE Backward inside an Execute > statement earlier. And now, I'm seeing this error appear again: > ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL Hm, you're right. This arises from the fact that _SPI_execute_plan rejects cursor-related utility statements. While I'd never stopped to question that before, it does seem like this restriction is a bit pointless. Does anyone remember why it's like that? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
