Thread: Cursors in SPI functions/procedures
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
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
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
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
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