2

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=#
1
  • I don't think there is a way to do this if the session is interactive. For non interactive session, I think ON_ERROR_STOP would do it.
    – jjanes
    Jun 9 at 15:36

2 Answers 2

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.

2
  • Exactly what I was looking for! Thank you! The psqlrc behavior is a little unintuitive. I wonder if that should be changed (or at least denoted clearly in the docs), especially when the docs seem to imply that connection settings should/can be set using the .psqlrc, "These files can be used to set up the client and/or the server to taste, typically with \set and SET commands."
    – Bryce
    Jun 9 at 19:05
  • Yes, the part of the documentation that says that it is useful for setting up the server is a bit misleading. The behavior is logical if you understand that .psqlrc is run when psql starts. Reconnecting with \c or after a disconnection is not a restart of psql. Jun 9 at 20:18
0

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.

1
  • Thanks for the suggestion! In this case, I don't want to modify any server settings. I want my client defaults to be decoupled from my PG instance defaults. I'm slightly concerned that this might be a bit of a gap in the psql tooling
    – Bryce
    Jun 9 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.