Re: SQL/MED estimated time of arrival?

On Mon, 08 Nov 2010 10:31:22 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> writes:
> > How about removing them, ConnectServer and FreeFSConnection, from
> > FdwRoutine and leaving the responsibility of resource management to
> > each FDW? Each FDW would have to use mechanism such as Virtual File
> > and ResourceOwner to manage resources properly, though.
>
> For the most part, we expect that ResourceOwners only do something
> useful during error cleanup. That is, you *should* have a
> close-connection type of function that is expected to be called during
> normal query shutdown. The ResourceOwner hooks will operate to
> compensate for the lack of this call in an error recovery case.
> The reason for doing things that way is so that we can check for
> unintentional resource leakage in the non-error code paths.

I fixed postgresql_fdw to use RegisterResourceReleaseCallback() to
close all connections in error cases including user interrupt. But
I'm not sure if I used the mechanism correctly because all I could
find about the API was only few documents, README of resowner and
function comments. I tested the codes in cases below and confirmed
that all connections have been closed.

- remote query error with wrong relation name
- user interrupt, Ctrl+C on psql during long query

Of course, in non-error case, all connections are closed via normal
query shutdown path.

During fixing connection cleanup, I've removed connection pooling
from postgresql_fdw to make resource management simple. Now
postgresql_fdw uses one connection for all of ForeignScan nodes in a
local query, but doesn't keep the connection alive beyond queries.
Originally, sharing connection is intended to execute multiple remote
query in a transaction for consistency.

I think external tools such as pgpool or pgbouncer would be better to
pool connections. Is it reasonable?

Also, ExecInitForeignScan() was fixed to call ConnectServer() and
BeginScan() only when the EXEC_FLAG_EXPLAIN_ONLY is not set.

Regards,
--
Shigeru Hanada