FDW connections

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

FDW connections

Steve Baldwin
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
Reply | Threaded
Open this post in threaded view
|

Re: FDW connections

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

RE: FDW connections

Hou, Zhijie
> > 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



 



Reply | Threaded
Open this post in threaded view
|

Re: FDW connections

Steve Baldwin
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 <[hidden email]> 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