I was hoping to use idle_in_transaction_session_timeout to prevent schema change migrations from running too long and thereby locking up the application for an extended period even if any one statement in the migration is very short.
I am not finding predictable behavior using idle_in_transaction_session_timeout. If I create a simple SQL file with two ALTER TABLE statements, and intentionally block the first statement long enough to exceed idle_in_transaction_session_timeout, usually once I unblock the first statement, the migration succeeds. I want it to actually be killed once it has exceeded idle_in_transaction_session_timeout and finished executing one SQL statement and is about to move to another. One of my tries, it actually did somehow exceed it and terminate, with the exact same test: $ psql test -f foo SET BEGIN ALTER TABLE ALTER TABLE psql:foo:11: FATAL: terminating connection due to idle-in-transaction timeout psql:foo:12: SSL connection has been closed unexpectedly psql:foo:12: connection to server was lost However, I only got that to happen once.... usually it just executes fine which I don't want. Session 1: SET idle_in_transaction_session_timeout = 1; BEGIN; ALTER TABLE foo ADD COLUMN bar text; -- block this for > idle_in_transaction_session_timeout -- I was hoping it would timeout here ALTER TABLE bar ADD COLUMN foo text; COMMIT; Session 2: BEGIN; SELECT * FROM foo; ..... wait then abort Granted this example is contrived, but the goal is again to avoid allowing a migration with many individual statements from taking longer than say 5 seconds to execute, locking up the application. Is there any way to timeout a long transaction or any clarity around how idle_in_transaction_session_timeout works when executing a file with multiple SQL statements? Thanks, Jeremy
