Thread: Cursor support in pl/pg
Now that 7.1 is safely in the can, is it time to consider this patch? It provides cursor support in PL. http://www.airs.com/ian/postgresql-cursor.patch Nathan Myers ncm@zembu.com
ncm@zembu.com (Nathan Myers) writes:
> Now that 7.1 is safely in the can, is it time to consider
> this patch?
Not till we've forked the tree for 7.2, which is probably a week or so
away...
regards, tom lane
Tom Lane wrote: > ncm@zembu.com (Nathan Myers) writes: > > Now that 7.1 is safely in the can, is it time to consider > > this patch? > > Not till we've forked the tree for 7.2, which is probably a week or so > away... IIRC the patch only provides the syntax for CURSOR to PL/pgSQL. Not real cursor support on the SPI level. So it's still the same as before, the backend will try to suck up the entire resultset into the SPI tuple table (that'smemory) and die if it's huge enough. What we really need is an improvement to the SPI manager to support cursor (or cursor like behaviour through repeated executor calls). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck <JanWieck@Yahoo.com> writes: > IIRC the patch only provides the syntax for CURSOR to > PL/pgSQL. Not real cursor support on the SPI level. So it's > still the same as before, the backend will try to suck up the > entire resultset into the SPI tuple table (that's memory) and > die if it's huge enough. > > What we really need is an improvement to the SPI manager to > support cursor (or cursor like behaviour through repeated > executor calls). Agreed, but as I may have said before, 1) the problem you describe already exists in PL/pgSQL when using the FOR x IN SELECT statement, 2) the PL/pgSQL cursor patch is useful without the improvement to the SPI layer, 3) I would argue that the PL/pgSQL cursor patch is still needed after the SPI layer is improved. So I do not think that is a valid argument against installing the PL/pgSQL cursor patch. Ian ---------------------------(end of broadcast)--------------------------- TIP 83: The only thing cheaper than hardware is talk.
Ian Lance Taylor wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > > > IIRC the patch only provides the syntax for CURSOR to > > PL/pgSQL. Not real cursor support on the SPI level. So it's > > still the same as before, the backend will try to suck up the > > entire resultset into the SPI tuple table (that's memory) and > > die if it's huge enough. > > > > What we really need is an improvement to the SPI manager to > > support cursor (or cursor like behaviour through repeated > > executor calls). > > Agreed, but as I may have said before, 1) the problem you describe > already exists in PL/pgSQL when using the FOR x IN SELECT statement, > 2) the PL/pgSQL cursor patch is useful without the improvement to the > SPI layer, 3) I would argue that the PL/pgSQL cursor patch is still > needed after the SPI layer is improved. > > So I do not think that is a valid argument against installing the > PL/pgSQL cursor patch. I don't object if we can be sure that it's implementing the syntax a final version with *real* cursor support will have. Can we? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck <JanWieck@Yahoo.com> writes: > I don't object if we can be sure that it's implementing the > syntax a final version with *real* cursor support will have. > Can we? I don't know, and I don't know what the decision criteria are. I intentionally implemented the Oracle cursor syntax. PL/pgSQL is very similar to PL/SQL, and I didn't see any reason to introduce a spurious difference. Note in particular that simply passing OPEN/FETCH/CLOSE through to the Postgres SQL parser does not implement the Oracle cursor syntax, so I wouldn't have done that even if it would have worked. (I have a vested interest here. For various reasons, my company, Zembu, has an interest in minimizing the strain of porting applications from Oracle to Postgres. I assume that the Postgres team also has that interest, within reason. But I don't know for sure.) Ian
Ian Lance Taylor wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > > > I don't object if we can be sure that it's implementing the > > syntax a final version with *real* cursor support will have. > > Can we? > > I don't know, and I don't know what the decision criteria are. > > I intentionally implemented the Oracle cursor syntax. PL/pgSQL is > very similar to PL/SQL, and I didn't see any reason to introduce a > spurious difference. Note in particular that simply passing > OPEN/FETCH/CLOSE through to the Postgres SQL parser does not implement > the Oracle cursor syntax, so I wouldn't have done that even if it > would have worked. Maybe it's "very similar" because I had an Oracle PL/SQL language reference at hand while writing the grammar file, maybe it's just by accident :-) > > (I have a vested interest here. For various reasons, my company, > Zembu, has an interest in minimizing the strain of porting > applications from Oracle to Postgres. I assume that the Postgres team > also has that interest, within reason. But I don't know for sure.) Who hasn't? O.K., you convinced me. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com