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