idle_in_transaction_session_timeout for a set of SQL statements

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: idle_in_transaction_session_timeout for a set of SQL statements
Date: 2019-02-27 14:55:55
Message-ID: CAMa1XUj_u7HCmBajdSaA4caPDesTv8G=Z5wGBWBb2=ML4u1XMw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2019-02-27 15:01:23 Re: why not using a mountpoint as PGDATA?
Previous Message Achilleas Mantzios 2019-02-27 14:48:37 Re: Barman disaster recovery solution