Обсуждение: creating WITH HOLD cursors using SPI
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? -- ams
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