Re: postgres_fdw and connection management

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;