FDW connections

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]/messages/by-id/CALj2ACVcpU=wB7G=zT8msVHvPs0-y0BbviupiT+f3--bGYaOMA@mail.gmail.com.

The functions have not been committed yet, it may can be used in PG14.

[1]: /messages/by-id/CALj2ACVcpU=wB7G=zT8msVHvPs0-y0BbviupiT+f3--bGYaOMA@mail.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: