Re: pgsql_fdw, FDW for PostgreSQL server

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?

> [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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company