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'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).
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