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
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 |