Обсуждение: FDW connections
Hi all,
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?
Thanks,
Steve
On Fri, 2021-01-22 at 16:03 +1100, Steve Baldwin 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? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
> > 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
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.fnst@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