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

Jan Wieck <janwieck@Yahoo.com> writes:

> Ian Lance Taylor wrote:
> >
> > I have a patch which adds support for cursors in PL/pgSQL.  If anybody
> > is interested, I can provide that patch against either 7.0.3 or the
> > current CVS sources.  I have sent it to the maintainers, and they will
> > consider it after the 7.1 release.
>
>     The  one  patch  I've  seen  on  this topic simply added some
>     CURSOR syntax to the PL/pgSQL  parser,  but  did  it  with  a
>     regular  SPI  query  in the background.  I object that'd be a
>     good idea, because it let's ppl think they're using a cursor,
>     test  their  functions  against  test data and assume they'll
>     still work if selecting a couple of million rows at  once  in
>     production.  That wouldn't be true, because the backend might
>     run out of memory, what they wanted to  avoid  by  using  the
>     cursor in the first place.
>
>     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.

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.

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.

Ian