[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: FDW connections
From: Steve Baldwin <steve.baldwin () gmail ! com>
Date: 2021-01-22 20:44:57
Message-ID: CAKE1AiY+O3U2WeesBp7DcNJ5HbhgWs+kzumPdfkN6eVnecFKJg () mail ! gmail ! com
[Download RAW message or body]
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
>
>
>
>
>
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr">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.<div><br></div><div> \
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.<br></div><div><br></div><div>Thanks very much for your \
help.</div><div><br></div><div>Kind \
regards,</div><div><br></div><div>Steve</div></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Fri, Jan 22, 2021 at 7:32 PM Hou, Zhijie <<a \
href="mailto:houzj.fnst@cn.fujitsu.com">houzj.fnst@cn.fujitsu.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> > If I have \
made a query on a foreign table (using postgres_fdw), it<br> > > establishes \
a connection automatically. Is there any way to<br> > > disconnect that fdw \
connection without disconnecting the session that<br> > > instigated it?<br>
> <br>
> No.<br>
> <br>
> From PostgreSQL v14 on, there is the "idle_session_timeout" that you \
could<br> > set on the server to close such sessions. postgresql_fdw will \
silently<br> > re-establish such broken connections. You could set this \
parameter in the<br> > foreign server definition.<br>
> <br>
> But this is a strange request: why would you want to close such connections<br>
> before the database session ends?<br>
> <br>
<br>
Hi<br>
<br>
There are two new functions being reviewed called:<br>
<br>
postgres_fdw_disconnect()<br>
postgres_fdw_disconnect_all()<br>
<br>
These function may solve your problem,<br>
If you are interested in that, you can take a look at [1].<br>
<br>
The functions have not been committed yet, it may can be used in PG14.<br>
<br>
[1] <a href="https://www.postgresql.org/message-id/CALj2ACVcpU%3DwB7G%3DzT8msVHvPs0-y0BbviupiT%2Bf3--bGYaOMA%40mail.gmail.com" \
rel="noreferrer" target="_blank">https://www.postgresql.org/message-id/CALj2ACVcpU%3DwB7G%3DzT8msVHvPs0-y0BbviupiT%2Bf3--bGYaOMA%40mail.gmail.com</a><br>
<br>
<br>
Best regards,<br>
houzj<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic