psql: Preventing Idle Transactions by Default

Question

To prevent idle transactions in psql by default, I thought I should just set an aggressive idle_in_transaction_session_timeout in my .psqlrc.

This works initially, however, when the connection is killed and psql automatically attempts to re-connect, it doesn't re-run the .psqlrc, meaning the shell has no longer has this added layer of safety until I re-run the SET SESSION idle_in_transaction_timeout. For my purposes, I don't want to have to re-run manually re-run the commands in my psqlrc every time a conn is re-built by psql, especially because I might forget.

Is there a way to re-run the .psqrc whenever psql attempts to re-establish a connection? Alternatively, is there a way to just completely kill the psql session if the connection is killed, as a forcing function to re-run the .psqlrc?

psql version: 14.8

Example:

psql -h localhost -W -U postgres -d postgres                                                        
SET
psql (14.8 (Homebrew), server 14.6 (Debian 14.6-1.pgdg110+1))
Type "help" for help.

postgres=# SHOW idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 2s
(1 row)

postgres=# BEGIN;
BEGIN
postgres=*# SELECT * FROM bla;
FATAL:  terminating connection due to idle-in-transaction timeout
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 (14.8 (Homebrew), server 14.6 (Debian 14.6-1.pgdg110+1))
postgres=# SHOW idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 0
(1 row)

postgres=#

Answer 1

You can specify the parameter in your connection string, then it will be set during a reconnect:

psql 'options=-cidle_in_transaction_session_timeout=2000 host=localhost user=postgres dbname=postgres'
psql (15.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

test=# SHOW idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
═════════════════════════════════════
 2s
(1 row)

test=# BEGIN;
BEGIN
test=*# SELECT 42;
FATAL:  terminating connection due to idle-in-transaction timeout
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.
test=# SHOW idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout 
═════════════════════════════════════
 2s
(1 row)

Alternatively, you could use the PGOPTIONS environment variable.

Answer 2

You can change the postgresql.conf file on the server to add

idle_in_transaction_session_timeout = 2s

This is documented here.

You can also do it from within SQL:

ALTER SYSTEM SET idle_in_transaction_session_timeout = 2s;
SELECT pg_reload_conf();

This modifies the postgresql.auto.conf file which overrides the aforementioned.

---

Having said that, you should probably ensure that any client applications are correctly disposing their transaction and connection objects, so that you don't need to rely on this.