psql timeout: who's waiting for whom

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: psql timeout: who's waiting for whom
Date: 2022-04-19 19:29:04
Message-ID: 81a2f331-de6a-2c8e-a625-d692ccff7363@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'll be asking 'those in charge' to reset some timeout/keep-alive
setting /somewhere/. I need help in hopefully naming the correct
setting.  The server is running version 14

Am I dealing with a client issue, a server issue or a mis-match in
host/postgres configuration values?  If I cannot have psql wait forever,
I would at least like it to die/reconnect quickly.  What are my options?

If I leave my psql session for long enough, then the first interaction
(with some exceptions) will be unresponsive for /some time/.  The
typical, eventual response is a successful re-connection though last one
was fatal:

barnard=# \i
~/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql

psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:22:
SSL SYSCALL error: Connection timed out
psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:23:
no connection to the server
psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:23:
fatal: connection to server was lost

More typically I get this

barnard=# select 1;
  C-c C-cCancel request sent
SSL SYSCALL error: Connection timed out
barnard=# \c barnard
psql (12.2, server 14.2)
WARNING: psql major version 12, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher:
ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "barnard" as user "postgres".

The client machine is using default TCP values,

tcp_keepalive_intvl: 75
tcp_keepalive_probes: 9
tcp_keepalive_time: 7200

the dbhost postgresql.conf has tcp_* set to zero (use default)

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_count = 0               # TCP_KEEPCNT;
                                        # 0 selects the system default
#tcp_user_timeout = 0                   # TCP_USER_TIMEOUT, in
milliseconds;
                                        # 0 selects the system default

#client_connection_check_interval = 0   # time between checks for client
                                        # disconnection while
running queries;
                                        # 0 for never
[postgres(at)csgsdb ipv4]$ for t in tcp_keep*;do printf "%s: " $t; cat
$t; done
tcp_keepalive_intvl: 75
tcp_keepalive_probes: 9
tcp_keepalive_time: 7200

Thanks

Browse pgsql-general by date

  From Date Subject
Next Message andrew cooke 2022-04-19 20:28:51 Repeated, never-ending deadlock
Previous Message Adrian Klaver 2022-04-19 18:37:35 Re: Joining with calendar table