Thanks guys. I realise it was an odd request. The scenario is I'm building a mechanism for an application to operate in limited capacity using a secondary database while the primary database is being upgraded. I'm using postgres_fdw to sync changes between the primary and secondary databases. The reason for the question was during my testing I was switching between 'modes' (we refer to them as online and offline), and during the secondary database setup process, it renames a database if it exists. That was failing due to an existing connection that ended up being from the primary database during its 'sync-from-offline' process from the previous test. The primary database connection still existed because it was made from a connection pool. So, the bottom line is that this was a somewhat contrived situation, and I was able to release the connection from the pool after performing the fdw query.
We're using AWS RDS, so we've had to implement our own 'zero-downtime' functionality. RDS also means we're a bit behind version-wise. The latest version we have available today is 12.5, so I imagine it will be quite a while before PG14 is a possibility. Thanks very much for your help. Kind regards, Steve On Fri, Jan 22, 2021 at 7:32 PM Hou, Zhijie <houzj.f...@cn.fujitsu.com> wrote: > > > If I have made a query on a foreign table (using postgres_fdw), it > > > establishes a connection automatically. Is there any way to > > > disconnect that fdw connection without disconnecting the session that > > > instigated it? > > > > No. > > > > From PostgreSQL v14 on, there is the "idle_session_timeout" that you > could > > set on the server to close such sessions. postgresql_fdw will silently > > re-establish such broken connections. You could set this parameter in > the > > foreign server definition. > > > > But this is a strange request: why would you want to close such > connections > > before the database session ends? > > > > Hi > > There are two new functions being reviewed called: > > postgres_fdw_disconnect() > postgres_fdw_disconnect_all() > > These function may solve your problem, > If you are interested in that, you can take a look at [1]. > > The functions have not been committed yet, it may can be used in PG14. > > [1] > https://www.postgresql.org/message-id/CALj2ACVcpU%3DwB7G%3DzT8msVHvPs0-y0BbviupiT%2Bf3--bGYaOMA%40mail.gmail.com > > > Best regards, > houzj > > > > > > > >
