PostgreSQL ping/pong to client

On 4/17/19 5:39 AM, Ajay Pratap wrote:

Hello there,
I am using PostgreSQL 10.7 as the database, and have Java web app. My
app takes a lock on the database using the following command whenever my
server is starting.
/select /pg_try_advisory_lock/(100)/
To make sure only one instance of the App is using the database at a time.
There have been several instances when my server crashes(or stops/ or
kill), but this lock still remained on the PostgreSQL. Ideally, this
should not happen because TCP connection breaks if one of the peers
dies, but it happens otherwise.

If the server is exiting uncleanly I am not sure how it will gracefully
close connections.

To solve this problem PostgreSQL needs to break the connection if the
client dies.

But the problem as you describe it is that the server dies not the client.

One of the methods is to configure TCP timeout but since this is very
impactful and hence unrealistic.
I was wounding how can out of the box PostgreSQL help me solve this.
May be like a ping pong service to check if client is still alive.

Thanks and regards,
Ajay Pratap,
Software Engineer,
Drishti-Soft

--
Adrian Klaver
adrian.klaver@aklaver.com

Correction: I meant when my java application dies postgres should break all
the connections that were associated with that peer.

On Wed, Apr 17, 2019, 19:59 Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 4/17/19 5:39 AM, Ajay Pratap wrote:

Hello there,
I am using PostgreSQL 10.7 as the database, and have Java web app. My
app takes a lock on the database using the following command whenever my
server is starting.
/select /pg_try_advisory_lock/(100)/
To make sure only one instance of the App is using the database at a

time.

There have been several instances when my server crashes(or stops/ or
kill), but this lock still remained on the PostgreSQL. Ideally, this
should not happen because TCP connection breaks if one of the peers
dies, but it happens otherwise.

If the server is exiting uncleanly I am not sure how it will gracefully
close connections.

To solve this problem PostgreSQL needs to break the connection if the
client dies.

But the problem as you describe it is that the server dies not the client.

One of the methods is to configure TCP timeout but since this is very
impactful and hence unrealistic.
I was wounding how can out of the box PostgreSQL help me solve this.
May be like a ping pong service to check if client is still alive.

Thanks and regards,
Ajay Pratap,
Software Engineer,
Drishti-Soft

--
Adrian Klaver
adrian.klaver@aklaver.com

--

*Disclaimer:* The information in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorized to
receive it. If you are not the intended recipient you are hereby notified
that any disclosure, copying, distribution or taking action in reliance of
the contents of this information is strictly prohibited and may be
unlawful. Drishti is neither liable for the improper, incomplete
transmission of the information contained in this communication nor any
delay in its receipt. The communication is not intended to operate as an
electronic signature under any applicable law. Drishti assumes no
responsibility for any loss or damage resulting from the use of e-mails.

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 4/17/19 5:39 AM, Ajay Pratap wrote:

I am using PostgreSQL 10.7 as the database, and have Java web app. My
app takes a lock on the database using the following command whenever my
server is starting.
/select /pg_try_advisory_lock/(100)/
To make sure only one instance of the App is using the database at a time.
There have been several instances when my server crashes(or stops/ or
kill), but this lock still remained on the PostgreSQL. Ideally, this
should not happen because TCP connection breaks if one of the peers
dies, but it happens otherwise.
To solve this problem PostgreSQL needs to break the connection if the
client dies.

But the problem as you describe it is that the server dies not the client.

An advisory lock wouldn't survive a server reboot, so there's something
not very accurate about this description.

I suspect what the OP wants is quicker detection of client connection
loss, and yes, messing with TCP timeouts and/or keepalive is the only way.

regards, tom lane

On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap <ajaypratap@drishti-soft.com> wrote:

Correction: I meant when my java application dies postgres should break all the connections that were associated with that peer.

And how is the server supposed to detect that without keepalives? TCP
is dessigned to survice for extended period of times without traffic,
I used that a lot in the dial up times.

And what makes you think keepalives are impactful and unrealistic? I
use them a lot, they do not impact my workloads measurably.

Francisco Olarte.

Francisco Olarte <folarte@peoplecall.com> writes:

On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap <ajaypratap@drishti-soft.com> wrote:

Correction: I meant when my java application dies postgres should break all the connections that were associated with that peer.

And how is the server supposed to detect that without keepalives? TCP
is dessigned to survice for extended period of times without traffic,
I used that a lot in the dial up times.
And what makes you think keepalives are impactful and unrealistic? I
use them a lot, they do not impact my workloads measurably.

If we tried to do something about that in the server code proper,
we'd basically be reinventing TCP keepalives --- probably badly.
And we couldn't do it at all without a protocol version break,
because the client-side code would also need to know about it.

Just use the keepalive facility.

regards, tom lane

Maybe postgresql (application) layer ping pong.

Enabling system wise timeouts can impact my whole setup including my web
app, thats why it is hard for me to configure them.

On Wed, Apr 17, 2019, 22:12 Francisco Olarte <folarte@peoplecall.com> wrote:

On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap <ajaypratap@drishti-soft.com>
wrote:

Correction: I meant when my java application dies postgres should break

all the connections that were associated with that peer.

And how is the server supposed to detect that without keepalives? TCP
is dessigned to survice for extended period of times without traffic,
I used that a lot in the dial up times.

And what makes you think keepalives are impactful and unrealistic? I
use them a lot, they do not impact my workloads measurably.

Francisco Olarte.

--

*Disclaimer:* The information in this communication is confidential and
may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorized to
receive it. If you are not the intended recipient you are hereby notified
that any disclosure, copying, distribution or taking action in reliance of
the contents of this information is strictly prohibited and may be
unlawful. Drishti is neither liable for the improper, incomplete
transmission of the information contained in this communication nor any
delay in its receipt. The communication is not intended to operate as an
electronic signature under any applicable law. Drishti assumes no
responsibility for any loss or damage resulting from the use of e-mails.

Ajay Pratap <ajaypratap@drishti-soft.com> writes:

Enabling system wise timeouts can impact my whole setup including my web
app, thats why it is hard for me to configure them.

Uh ... who said anything about system-wide timeouts?

The normal way to set this up is to configure the tcp_keepalives_xxx
PG parameters, which will be applied to connections to the Postgres
server, nothing else.

regards, tom lane

On 2019-04-17 18:41:57 +0200, Francisco Olarte wrote:

On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap <ajaypratap@drishti-soft.com> wrote:

Correction: I meant when my java application dies postgres should break all the connections that were associated with that peer.

And how is the server supposed to detect that without keepalives?

Ajay was taliking about an application crashing. If that happens, the OS
(on the application machine) should close all connections the
application had open. So in that case PostgreSQL just gets EOF on any
attempt to read from or write to the socket.

I'm sure this works for any unixoid OS and fairly sure it works for
Windows, too.

If for some reason this doesn't work for Ajay, I suspect that the
problem isn't what he thinks it is and should investigate the cause
further.

TCP is dessigned to survice for extended period of times without
traffic, I used that a lot in the dial up times.

And what makes you think keepalives are impactful and unrealistic? I
use them a lot, they do not impact my workloads measurably.

Right. But keepalives solve a different problem (detecting loss of
network connectivity or the peer machine being turned off).

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;