Обсуждение: tcp keepalives not sent during long query

On Wed, 2022-12-14 at 08:55 +0100, Willy-Bas Loos wrote:
> Some users of our database have a NAT firewall and keep a postgres client (e.g. pgAdmin )
> open for hours. To prevent the connection from being killed by the firewall due to inactivity,
> we configured tcp_keepalives_idle = 120 so that the server sends keepalives and keeps the
> connection active. (this is on debian)
> 
> We've recently upgraded from postgres 9.3 to 13 and from debian 6 to 11.
> I'm getting the complaint that since the upgrade, the connection breaks. But only when they run a long query.
> 
> Has anything changed in postgres that might cause this? e.g. that keepalives are only sent when the session is idle?

It is the operating system kernel that sends keepalives, so that should be independent of
what the PostgreSQL backend is doing.

Yours,
Laurenz Albe

Willy-Bas Loos <willybas@gmail.com> writes:
> Thanks for your answer. I was afraid someone would say that...
> I was hoping that the keepalives would be more of a matter of cooperation
> between postgres and the OS.

No, we just apply the setting to the open socket and trust the OS
to do it.

Are you quite certain that you applied the configuration change to
your new installation?

It'd be worth doing

show tcp_keepalives_idle;

in one of the sessions where you are having trouble.  And maybe
check the other keepalives settings too?

            regards, tom lane

On Thu, 2022-12-15 at 08:31 +0100, Willy-Bas Loos wrote:
> On Wed, Dec 14, 2022 at 6:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > It'd be worth doing
> > 
> > show tcp_keepalives_idle;
> 
> Wow, you're right! It's in the postgresql.conf but it isn't set when I reload the server
> A restart also doesn't do it and even doing SET tcp_keepalives_idle=120; doesn't work.
> It gives me a confirmation, but then when I SHOW the value, it gives me 0.
> 
> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle 
> ---------------------
>  0
> (1 row)

One good way to debug this is

  SELECT setting, source, sourcefile, sourceline
  FROM pg_settings
  WHERE name = 'tcp_keepalives_idle';

That will tell you from where you get the parameter value.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Thu, 15 Dec 2022 at 07:31, Willy-Bas Loos <willybas@gmail.com> wrote:
>
> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle
> ---------------------
>  0

Are you connected in this psql session via tcp or unix domain socket?

"In sessions connected via a Unix-domain socket, this parameter is
ignored and always reads as zero."

Geoff

Willy-Bas Loos <willybas@gmail.com> writes:
> It gives me a confirmation, but then when I SHOW the value, it gives me 0.

> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle
> ---------------------
>  0
> (1 row)

That's the behavior I'd expect on a local (Unix-socket) connection
... you sure you're doing this from one of the problematic clients?

            regards, tom lane