Thread: 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
<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>
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
<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>
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