creating WITH HOLD cursors using SPI

Abhijit Menon-Sen <ams@oryx.com> writes:

I've been working on making it possible for PL/Perl users to fetch large
result sets one row at a time (the current spi_exec_query interface just
returns a big hash).

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and have
an spi_fetchrow that calls SPI_cursor_fetch.

This seems awfully limiting; isn't return_next a better API?

regards, tom lane

Tom Lane said:

Abhijit Menon-Sen <ams@oryx.com> writes:

I've been working on making it possible for PL/Perl users to fetch
large result sets one row at a time (the current spi_exec_query
interface just returns a big hash).

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and
have an spi_fetchrow that calls SPI_cursor_fetch.

This seems awfully limiting; isn't return_next a better API?

plperl already does return_next for handing data back to postgres (see
recently applied patch). I don't understand how we can use it when fetching
data from postgres into plperl, which is what Abhijit is working on.

cheers

andrew

"Andrew Dunstan" <andrew@dunslane.net> writes:

Tom Lane said:

This seems awfully limiting; isn't return_next a better API?

plperl already does return_next for handing data back to postgres (see
recently applied patch). I don't understand how we can use it when fetching
data from postgres into plperl, which is what Abhijit is working on.

Oh, I see --- I thought this was duplicating return_next. Sorry for
the noise.

regards, tom lane

At 2005-06-12 14:54:47 +0530, ams@oryx.com wrote:

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and have
an spi_fetchrow that calls SPI_cursor_fetch. It works well enough, but I
don't know how to reproduce spi_exec_query's error handling (it runs the
SPI_execute in a subtransaction).

One possibility would be to make plperl_call_handler create the internal
subtransaction, so that all of the perl code runs inside it. But I'm not
sure if that would actually work, especially if one of the SPI functions
failed. But I can't think of what else to do, either.

Thoughts?

-- ams

On Mon, Jun 13, 2005 at 07:39:40PM +0530, Abhijit Menon-Sen wrote:

At 2005-06-12 14:54:47 +0530, ams@oryx.com wrote:

The idea is to have spi_query call SPI_prepare/SPI_open_cursor,
and have an spi_fetchrow that calls SPI_cursor_fetch. It works
well enough, but I don't know how to reproduce spi_exec_query's
error handling (it runs the SPI_execute in a subtransaction).

One possibility would be to make plperl_call_handler create the
internal subtransaction, so that all of the perl code runs inside
it. But I'm not sure if that would actually work, especially if one
of the SPI functions failed. But I can't think of what else to do,
either.

Thoughts?

I have some :)

If we figure this out for PL/Perl, the general method will be
applicable to PL/YourFavoriteLanguage, so even if you're not a fan of
Perl, it's worth looking into.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Abhijit Menon-Sen wrote:

I've been working on making it possible for PL/Perl users to fetch large
result sets one row at a time (the current spi_exec_query interface just
returns a big hash).

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and have
an spi_fetchrow that calls SPI_cursor_fetch. It works well enough, but I
don't know how to reproduce spi_exec_query's error handling (it runs the
SPI_execute in a subtransaction).

To do something similar, I would have to create a WITH HOLD cursor in my
spi_query function. But SPI_cursor_open provides no way to do this, and
it calls PortalStart before I can set CURSOR_OPT_HOLD myself.

and later:

One possibility would be to make plperl_call_handler create the internal
subtransaction, so that all of the perl code runs inside it. But I'm not
sure if that would actually work, especially if one of the SPI functions
failed. But I can't think of what else to do, either.

This is an important piece of work in making plperl really usable.

Is it possible to do by using non-SPI calls?

Is is possible to do it without using a cursor, e.g. run the query all
at once and store the data in a TupleStore (rather like you did for
plperl return_next) and then hand the rows to plperl one at a time on
demand (in effect a sort of homegrown cursor)? Could something like that
be done in a PG_TRY block?

I'm just thinking off the top of my head here because I don't know the
answer and I'm hoping some kindly wizard will speak up and set us both
straight :-)

cheers

andrew

Abhijit Menon-Sen wrote:

Hi.

I've been working on making it possible for PL/Perl users to fetch large
result sets one row at a time (the current spi_exec_query interface just
returns a big hash).

The idea is to have spi_query call SPI_prepare/SPI_open_cursor, and have
an spi_fetchrow that calls SPI_cursor_fetch. It works well enough, but I
don't know how to reproduce spi_exec_query's error handling (it runs the
SPI_execute in a subtransaction).

To do something similar, I would have to create a WITH HOLD cursor in my
spi_query function. But SPI_cursor_open provides no way to do this, and
it calls PortalStart before I can set CURSOR_OPT_HOLD myself.

Suggestions?

Abhijit,

Thinking and reading about this some more, I think we should not try to
mimic the error handling of the existing mechanism. Let's just provide a
separate API using SPI_prepare/SPI_open_cursor/SPI_cursor_fetch, and
leave the current mechanism in place - it's useful enough on small
resultsets.

Does that make sense? If so, can you do that, or give me what you have
and let me bang on it?

cheers

andrew