Обсуждение: postgres_fdw and connection management

I'm evaluating use of the postgres FDW to keep in sync a central database
to changes made in thousand other databases, via triggers.

But as long as postgres_fdw keeps connections open for the whole lifetime
of a session this conflicts with large use of poolers which make sessions
virtually never expire.

Is there any way to ask the postgres_fdw to close connections at the
end of a transaction ? Or would it be possible at all to do from a 
FDW handler ? Do you see any drawback in doing that ?

I'm willing to work on a patch, maybe accepting an additional OPTION,
if you agree on the idea.

--strk;
()  ASCII ribbon campaign  --  Keep it simple !/\  http://strk.keybit.net/rants/ascii_mails.txt  

On Fri, May 23, 2014 at 11:13:50AM -0300, Fabrízio de Royes Mello wrote:
> On Fri, May 23, 2014 at 8:09 AM, Sandro Santilli <strk@keybit.net> wrote:
> >
> > I'm evaluating use of the postgres FDW to keep in sync a central database
> > to changes made in thousand other databases, via triggers.
> >
> > But as long as postgres_fdw keeps connections open for the whole lifetime
> > of a session this conflicts with large use of poolers which make sessions
> > virtually never expire.
> >
> > Is there any way to ask the postgres_fdw to close connections at the
> > end of a transaction ? Or would it be possible at all to do from a
> > FDW handler ? Do you see any drawback in doing that ?
> >
> > I'm willing to work on a patch, maybe accepting an additional OPTION,
> > if you agree on the idea.
> >
> 
> Maybe this is a case for the idea pointed here [1]. One way to register a
> cleanup code to extensions, then we can run a command called 'DISCARD
> EXTENSIONS' and/or 'DISCARD ALL' to do this job.
> 
> Unfortunately nobody comment my suggestion yet.
>
> [1]
> http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com

Indeed I tried "DISCARD ALL" in hope it would have helped, so I find
good your idea of allowing extensions to register an hook there.

Still, I'd like the FDW handler itself to possibly be configured
to disable the pool completely as a server-specific configuration.

--strk;

2014-05-24 0:09 GMT+09:00 Sandro Santilli <strk@keybit.net>:
> Indeed I tried "DISCARD ALL" in hope it would have helped, so I find
> good your idea of allowing extensions to register an hook there.
>
> Still, I'd like the FDW handler itself to possibly be configured
> to disable the pool completely as a server-specific configuration.

Connection management seems FDW-specific feature to me.  How about to
add FDW option, say pool_connection=true|false, to postgres_fdw which
allows per-server configuration?

--
Shigeru HANADA

On Tue, May 27, 2014 at 12:32:50AM -0300, Fabrízio de Royes Mello wrote:
> On Mon, May 26, 2014 at 11:47 PM, Shigeru Hanada <shigeru.hanada@gmail.com>
> wrote:
> >
> > 2014-05-24 0:09 GMT+09:00 Sandro Santilli <strk@keybit.net>:
> > > Indeed I tried "DISCARD ALL" in hope it would have helped, so I find
> > > good your idea of allowing extensions to register an hook there.
> > >
> > > Still, I'd like the FDW handler itself to possibly be configured
> > > to disable the pool completely as a server-specific configuration.
> >
> > Connection management seems FDW-specific feature to me.  How about to
> > add FDW option, say pool_connection=true|false, to postgres_fdw which
> > allows per-server configuration?

Yes, that's what I had in mind.
I'll try something along those lines.

> Makes sense... but if we use "pool_connection=true" and want to close the
> opened connection. How can we do that?

Right, I still consider hooks on DISCARD a useful addition.

--strk;

On Mon, May 26, 2014 at 10:47 PM, Shigeru Hanada
<shigeru.hanada@gmail.com> wrote:
> 2014-05-24 0:09 GMT+09:00 Sandro Santilli <strk@keybit.net>:
>> Indeed I tried "DISCARD ALL" in hope it would have helped, so I find
>> good your idea of allowing extensions to register an hook there.
>>
>> Still, I'd like the FDW handler itself to possibly be configured
>> to disable the pool completely as a server-specific configuration.
>
> Connection management seems FDW-specific feature to me.  How about to
> add FDW option, say pool_connection=true|false, to postgres_fdw which
> allows per-server configuration?

Right... or you could have an option to close the connection at
end-of-statement, end-of-transaction, or end-of-session.  But quite
apart from that, it seems like there ought to be a way to tell an FDW
to flush its state.

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