Re: Error: server closed the connection unexpectedly

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