From: | Marcelo Fernandes <marcefern7(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Resetting the lock_timeout value for a transaction |
Date: | 2025-04-27 23:28:41 |
Message-ID: | CAM2F1VM86387XCtNgbTbVQO6PfYBjCHg74XLsfavWJ-o-OZE+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi folks,
I have been using:
-- Setting the SESSION lock timeout to 10 seconds
SET lock_timeout = '10s';
-- Setting the TRANSACTION lock timeout to 20 seconds
BEGIN;
SET LOCAL lock_timeout = '20s';
COMMIT;
However, I have been caught by the behaviour of "RESET lock_timeout;" when
inside and outside a transaction.
-- Resets the lock_timeout value for the SESSION.
RESET lock_timeout;
-- WARNING: This will reset BOTH the SESSION and TRANSACTION lock_timeouts.
BEGIN;
SET LOCAL lock_timeout = '20s';
RESET lock_timeout;
COMMIT;
I would have expected that the "RESET lock_timeout;" inside a transaction would
only reset the value of lock_timeout for that specific transaction. Or else,
there would be an equivalent "RESET LOCAL lock_timeout;" to be used for that.
But I can't find anything that does just that.
Am I missing something? Example script for convenience:
-- This is the default lock_timeout (0s)
SHOW lock_timeout;
-- Set the SESSION lock timeout (42s)
SET lock_timeout = '42s';
SHOW lock_timeout;
BEGIN;
-- WARNING: This will set a new value for the SESSION lock_timeout from within
-- the transaction because it is missing the LOCAL key word!
SET lock_timeout = '10s';
SHOW lock_timeout;
-- Set it again but this time only for the transaction. This value will not
-- affect the session lock_timeout.
SET LOCAL lock_timeout = '9s';
SHOW lock_timeout;
-- Reset BOTH the SESSION and Transaction lock_timeout (both go back to 0, the
-- default).
RESET lock_timeout;
SHOW lock_timeout;
COMMIT;
-- Should now be 0s because it was reset inside the transaction.
SHOW lock_timeout;
Thanks, Marcelo
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2025-04-27 23:44:19 | Re: Resetting the lock_timeout value for a transaction |
Previous Message | Christophe Pettus | 2025-04-27 19:21:30 | Re: Changing default fillfactor for the whole database |