Re: Cursors in SPI functions/procedures - Mailing list pgsql-general

Ian Lance Taylor wrote:
> Jan Wieck <janwieck@Yahoo.com> writes:
>
> >     So if we provide CURSOR syntax in PL's, we should provide the
> >     real functionality as well.
>
> That is what my patch does: adds syntax to the PL/pgSQL parser to
> support cursors.  The problem you mention is present in the patch.
>
> I did not use the real underlying cursor functionality because SPI
> does not support cursors.  I asked on pgsql-hackers how to fix that,
> and received no reply.
>
> Looking into the problem, I saw that syntax changes would be required
> even if and when cursors were supported in SPI, in order to support
> retrieving values from cursors into PL/pgSQL variables.  Therefore, I
> implemented that syntax.
>
> This permits people to use cursors in PL/pgSQL, which adds
> functionality like the ability to traverse two tables at the same time
> at different rates.  So PL/pgSQL with the patch is more functional
> than PL/pgSQL without it.  Your argument is essentially that people
> might be fooled by cursors.  That is true.  But that is an argument
> for better documentation, not an argument for avoiding an increase in
> functionality.

    Sorry  for  the  oversight  of  this  enhanced functionality.
    You're right - point taken.

> I also think that your argument is slightly disingenuous, in that
> PL/pgSQL already has the FOR statement.  When I saw an example of the
> FOR statement, I assumed that it read one row at a time.  I was
> surprised to see in the implementation that it actually reads all the
> rows, presumably for exactly the same reason that my cursor patch
> reads all the rows.  I believe that the problem that you point out in
> my cursor patch already exists in the PL/pgSQL FOR statement.

    The basic problem for both of us is the lack  of  flexibility
    in  SPI.   I  think SPI should work alot more like cursors in
    the first place.  So you open a  SPI-plan,  fetch  chunks  of
    rows  from  it, and close it.  I can live with the limitation
    not beeing able to move the cursor backward,  but  it  really
    scares  me that each query actually can blow away the backend
    by running out of memory.

> When cursors are supported in SPI, the patch I wrote will still be
> useful.  In fact, only a few places will have to be changed in my
> patch to use real cursors.
>
> This patch solved my immediate problem, so I didn't pursue
> implementing cursors in SPI.  However, if somebody can explain why
> they are not currently supported, I am willing to look into it.  There
> is a defined SPI_ERROR_CURSOR error return, so clearly somebody has
> thought about the issue.  I would prefer to know what that thinking is
> than to have to rediscover it myself.

    Let's   think   about   SPI_openplan(),    SPI_fetch()    and
    SPI_closeplan()  for  7.2.   The patch you have won't make it
    into 7.1 anyway, because that's closed for features  already.


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