Re: pgsql_fdw, FDW for PostgreSQL server

(2012/02/01 3:56), Robert Haas wrote:
> 2012/1/29 Kohei KaiGai<kaigai(at)kaigai(dot)gr(dot)jp>:
>> Remote SQL: DECLARE pgsql_fdw_cursor_0 SCROLL CURSOR FOR SELECT
>> a, b FROM public.t1 WHERE (a> 2)
>> (3 rows)
>
> Shouldn't we be using protocol-level cursors rather than SQL-level cursors?

Yes, we should, if we have protocol-level cursor :)
I checked libpq interface but I couldn't find any function for
protocol-level cursor.

>> [Design comment]
>> When "BEGIN" should be issued on the remote-side?
>> The connect_pg_server() is an only chance to issue "BEGIN" command
>> at the remote-side on connection being opened. However, the connection
>> shall be kept unless an error is not raised. Thus, the remote-side will
>> continue to work within a single transaction block, even if local-side iterates
>> a pair of "BEGIN" and "COMMIT".
>> I'd like to suggest to close the transaction block at the timing of either
>> end of the scan, transaction or sub-transaction.
>
> I suspect this is ultimately going to need to be configurable. Some
> people might want to close the transaction on the remote side ASAP,
> while other people might want to hold it open until commit. For a
> first version I think it's most likely best to do whatever seems
> simplest to code, planning to add more options later.

I fixed pgsql_fdw to abort remote transaction at the end of each local
query. I chose this timing because local query might include multiple
scans on same foreign server. I think this would be "ASAP" timing in
your comment.

It would be useful to make length of remote transaction same as local's,
I'll try RegisterXactCallback for this purpose, though we need to
preload FDW module to catch BEGIN preceding query using foreign tables.

--
Shigeru Hanada