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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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]/messages/by-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com. 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.
Regards,
[1]: /messages/by-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com
/messages/by-id/CAFcNs+orcZZ3-wPfa0RFuOCtq81sg=PrZuHv4-m1+j5etH6HOQ@mail.gmail.com
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
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]
/messages/by-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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
Makes sense... but if we use "pool_connection=true" and want to close the
opened connection. How can we do that?
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers