Thread: postgres_fdw and connection management

postgres_fdw and connection management

From
Sandro Santilli
Date:
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  



Re: postgres_fdw and connection management

From
Fabrízio de Royes Mello
Date:
<div dir="ltr"><br />On Fri, May 23, 2014 at 8:09 AM, Sandro Santilli <<a
href="mailto:strk@keybit.net">strk@keybit.net</a>>wrote:<br />><br />> I'm evaluating use of the postgres FDW
tokeep in sync a central database<br /> > to changes made in thousand other databases, via triggers.<br />><br
/>>But as long as postgres_fdw keeps connections open for the whole lifetime<br />> of a session this conflicts
withlarge use of poolers which make sessions<br /> > virtually never expire.<br />><br />> Is there any way to
askthe postgres_fdw to close connections at the<br />> end of a transaction ? Or would it be possible at all to do
froma<br />> FDW handler ? Do you see any drawback in doing that ?<br /> ><br />> I'm willing to work on a
patch,maybe accepting an additional OPTION,<br />> if you agree on the idea.<br />><br /><br />Maybe this is a
casefor the idea pointed here [1]. One way to register a cleanup code to extensions, then we can run a command called
'DISCARDEXTENSIONS' and/or 'DISCARD ALL' to do this job.<br /><br />Unfortunately nobody comment my suggestion yet.<br
/><br/>Regards,<br /><br />[1] <a
href="http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com">http://www.postgresql.org/message-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com</a><br
/><br/>--<br />Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog sobre TI: <a
href="http://fabriziomello.blogspot.com">http://fabriziomello.blogspot.com</a><br/> >> Perfil Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a></div>

Re: postgres_fdw and connection management

From
Sandro Santilli
Date:
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;



Re: postgres_fdw and connection management

From
Shigeru Hanada
Date:
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



Re: postgres_fdw and connection management

From
Fabrízio de Royes Mello
Date:
<div dir="ltr"><br />On Mon, May 26, 2014 at 11:47 PM, Shigeru Hanada <<a
href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>>wrote:<br />><br />> 2014-05-24 0:09
GMT+09:00Sandro Santilli <<a href="mailto:strk@keybit.net">strk@keybit.net</a>>:<br /> > > Indeed I tried
"DISCARDALL" in hope it would have helped, so I find<br />> > good your idea of allowing extensions to register
anhook there.<br />> ><br />> > Still, I'd like the FDW handler itself to possibly be configured<br /> >
>to disable the pool completely as a server-specific configuration.<br />><br />> Connection management seems
FDW-specificfeature to me.  How about to<br />> add FDW option, say pool_connection=true|false, to postgres_fdw
which<br/> > allows per-server configuration?<br />><br /><br />Makes sense... but if we use
"pool_connection=true"and want to close the opened connection. How can we do that?<br /><br />--<br />Fabrízio de Royes
Mello<br/>Consultoria/Coaching PostgreSQL<br /> >> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog sobre TI: <a
href="http://fabriziomello.blogspot.com">http://fabriziomello.blogspot.com</a><br/>>> Perfil Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/> >> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a></div>

Re: postgres_fdw and connection management

From
Sandro Santilli
Date:
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;



Re: postgres_fdw and connection management

From
Robert Haas
Date:
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