Re: pgsql_fdw, FDW for PostgreSQL server - Mailing list pgsql-hackers

From Shigeru Hanada
Subject Re: pgsql_fdw, FDW for PostgreSQL server
Date
Msg-id 4F29F5A3.3090801@gmail.com
Whole thread Raw
In response to Re: pgsql_fdw, FDW for PostgreSQL server  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
(2012/02/01 3:56), Robert Haas wrote:
> 2012/1/29 Kohei KaiGai<kaigai@kaigai.gr.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


pgsql-hackers by date:

Previous
From: Fujii Masao
Date:
Subject: Re: pg_stats_recovery view
Next
From: Tom Lane
Date:
Subject: Re: feature request - datum_compute_size and datum write_should be public