Thread: Cursors in SPI functions/procedures

Cursors in SPI functions/procedures

From
Camm Maguire
Date:
Greetings!  Just read that this functionality is planned for the
future.  Can 7.1 have cursors in pgsql functions, for example?

Take care,
--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

Re: Cursors in SPI functions/procedures

From
Ian Lance Taylor
Date:
Camm Maguire <camm@enhanced.com> writes:

> Greetings!  Just read that this functionality is planned for the
> future.  Can 7.1 have cursors in pgsql functions, for example?

No, it can't.

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.

Ian

Re: Cursors in SPI functions/procedures

From
Jan Wieck
Date:
Ian Lance Taylor wrote:
> Camm Maguire <camm@enhanced.com> writes:
>
> > Greetings!  Just read that this functionality is planned for the
> > future.  Can 7.1 have cursors in pgsql functions, for example?
>
> No, it can't.
>
> 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.


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


Re: Cursors in SPI functions/procedures

From
Ian Lance Taylor
Date:
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

Re: Cursors in SPI functions/procedures

From
Jan Wieck
Date:
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