Обсуждение: idle_in_transaction_session_timeout for a set of SQL statements
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
On Wed, Feb 27, 2019 at 7:56 AM Jeremy Finzel <finzelj@gmail.com> wrote:
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 don't think you are actually going idle. Check pg_stat_activity for what this transaction is doing. It should show waiting on locks, but not idle. I think the command you seek is
SET lock_timeout TO '1s';
SET lock_timeout TO '1s';
No, my assumption is that lock_timeout does not cover my use case here. My point is actually that any one statement is not hitting lock_timeout, but as a whole the transaction takes too long. For example if I set lock_timeout to 1 second, but my migration actually has 10 SQL statements each of which take just under a second, I have a total now of a near 10-second lockout.
Regardless of whether or not I am technically going idle, I want to be able to abort based on transaction time length. I do believe I can handle this externally via the timeout command, but curious still if there is any way to reliably do it from within postgres.
Thanks,
Jeremy