Re: SQL/MED estimated time of arrival?

On Mon, 08 Nov 2010 10:31:22 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Shigeru HANADA <hanada@metrosystems.co.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