Re: Error: server closed the connection unexpectedly

[Sachin Kotwal <kotsachin@xxxxxxxxx>]

Hi All,

I am also facing a similar issue. In my case I am connecting to AWS Aurora PostgreSQL.

When my psql session is idle for a while ( for 5 - 10 minutes ) , It gets terminated and reconnect but query/transaction started by earlier session remain in `idle in transaction` state and the only option left to clean it is terminate that session with `pg_terminate_backend()`. Issue happens when we are connecting from either psql or DBeaver client but not with the application.

Here is example :
```
test=> begin;
BEGIN
test=*> LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
LOCK TABLE
test=*>
test=*> rollback;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:
Succeeded.
psql (13.4, server 11.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

test=> select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
    ?column?     |  pid  |        state        | usename  |    application_name    | client_addr  | wait_event_type |                                                                     query                                                                      
-----------------+-------+---------------------+----------+------------------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------
 00:08:22.762573 | 20233 | active              | postgres | PostgreSQL JDBC Driver | 10.0.174.30  | Lock            | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE
 00:09:28.101577 | 14830 | idle in transaction | postgres | psql                   | 10.0.173.200 | Client          | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
 00:00:00        | 10198 | active              | postgres | psql                   | 10.0.103.144 |                 | select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
(3 rows)

test=> rollback;
WARNING:  there is no transaction in progress
ROLLBACK
```

Nothing in the postgresql logs apart from below statements multiple times:

```

cat postgresql.log.2021-10-22-1200 | grep '10.0.173.200'            
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG:  connection received: host=10.0.173.200 port=4082
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG:  incomplete startup packet

```

Now because the psql session was terminated incorrectly, another session from the application trying to get explicit lock on the same table is waiting for a long time, until we are not terminating the hanging session.

Below are timeout/keepalive settings on my PostgreSQL instance:

```
select name, setting, unit  from pg_settings where name ilike '%timeout%';
                name                 | setting  | unit
-------------------------------------+----------+------
 archive_timeout                     | 300      | s
 authentication_timeout              | 60       | s
 checkpoint_timeout                  | 60       | s
 deadlock_timeout                    | 1000     | ms
 idle_in_transaction_session_timeout | 86400000 | ms
 lock_timeout                        | 0        | ms
 statement_timeout                   | 0        | ms
 wal_receiver_timeout                | 30000    | ms
 wal_sender_timeout                  | 60000    | ms
(9 rows)

select name, setting, unit  from pg_settings where name ilike '%keep%';
          name           | setting | unit
-------------------------+---------+------
 tcp_keepalives_count    | 9       |
 tcp_keepalives_idle     | 7200    | s
 tcp_keepalives_interval | 75      | s
 wal_keep_segments       | 0       |
(4 rows)
```

macOS Catelina 10.15.7 timeout settings :
```
sysctl -A | grep keep              

net.inet.tcp.keepidle: 7200000
net.inet.tcp.keepintvl: 75000
net.inet.tcp.keepinit: 75000
net.inet.tcp.keepcnt: 8
net.inet.tcp.always_keepalive: 0
net.inet.mptcp.keepalive: 840
net.link.ether.inet.keep_announcements: 1
net.key.natt_keepalive_interval: 20
net.inet6.ip6.keepfaith: 0
net.necp.pass_keepalives: 1
```

I can set ` lock_timeout` and ` idle_in_transaction_session_timeout` to lower value so that the hanging session can get terminated automatically by postgres but I would like to know root cause of the issue and fix it there instead of adjusting postgresql timeout setting values.

Please let me know which setting on my client or server is not set properly and because of that the issue is happening.

Regards,

Sachin

On Mon, Oct 11, 2021 at 1:38 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:

On Mon, 2021-10-11 at 00:12 +0530, Anil wrote:
> Please find the server log, if you can find something (due to some reasons not being able to get OS system log, will be shared soon). 

Nothing to see.

Perhaps an ill-configured network component closes idle connection?
In that case, try keepalive.

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

--

Thanks and Regards,

Sachin Kotwal