Re: Cursors in SPI functions/procedures

Ian Lance Taylor wrote:
> Jan Wieck <janwieck(at)Yahoo(dot)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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com