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

Wed, 25 Apr 2001 13:36:45 -0700
ncm@zembu.com (Nathan Myers)
Cursor support in pl/pg
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

Wed, 25 Apr 2001 16:46:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cursor support in pl/pg 
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


Wed, 25 Apr 2001 16:10:40 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Cursor support in pl/pg
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.

25 Apr 2001 23:43:41 -0700
Ian Lance Taylor <ian@airs.com>
Re: Cursor support in pl/pg
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


Thu, 26 Apr 2001 08:48:13 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Cursor support in pl/pg
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

26 Apr 2001 10:29:14 -0700
Ian Lance Taylor <ian@airs.com>
Re: Cursor support in pl/pg
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


Thu, 26 Apr 2001 13:53:11 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Cursor support in pl/pg