public inbox for [email protected]  

help / color / mirror / Atom feed

Resetting the lock_timeout value for a transaction
2+ messages / 2 participants
[nested] [flat]

* Resetting the lock_timeout value for a transaction
@ 2025-04-27 23:28  Marcelo Fernandes <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Marcelo Fernandes @ 2025-04-27 23:28 UTC (permalink / raw)
  To: [email protected]

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Resetting the lock_timeout value for a transaction
@ 2025-04-27 23:44  Tom Lane <[email protected]>
  parent: Marcelo Fernandes <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2025-04-27 23:44 UTC (permalink / raw)
  To: Marcelo Fernandes <[email protected]>; +Cc: [email protected]

Marcelo Fernandes <[email protected]> writes:
> 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.

We don't seem to have bothered with that exact syntax, but you
could spell it like

SET LOCAL lock_timeout TO DEFAULT;

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-04-27 23:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-27 23:28 Resetting the lock_timeout value for a transaction Marcelo Fernandes <[email protected]>
2025-04-27 23:44 ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox