Feedback-ID: i7a6842ed:Fastmail
MIME-Version: 1.0
Date: Thu, 25 Sep 2025 16:29:50 +0200
From: "Quentin de Metz" <quentin@de.me.tz>
To: x4mmm@yandex-team.ru
Cc: pgsql-novice@postgresql.org, amborodin@acm.org, aekorotkov@gmail.com,
 japinli@hotmail.com, zhjwpku@gmail.com
Message-Id: <97dbb3e1-6f2b-4e15-9c63-68609fd428c8@app.fastmail.com>
In-Reply-To: <34EA56AD-712D-4DEF-91B9-74FA1673A95D@yandex-team.ru>
References: <554670a7-7560-4d18-8ecb-8518fddf2b9d@app.fastmail.com>
 <34EA56AD-712D-4DEF-91B9-74FA1673A95D@yandex-team.ru>
Subject: Re: SET transaction_timeout inside a transaction
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/97dbb3e1-6f2b-4e15-9c63-68609fd428c8%40app.fastmail.com>
Precedence: bulk

Hi Andrey,

The answers from the other participants in this thread make sense.

My application has tooling to modify connection variables (e.g. statement_timeout) around specific queries, most of which can be set inside or outside the transaction with the same practical consequences.

This is simply the first time we need to explicitly set a variable before opening the transaction. We'll make the necessary modifications at the application layer.

Regards,
Quentin de Metz


On Sat, Sep 20, 2025, at 07:41, x4mmm@yandex-team.ru wrote:
> On 20 Sep 2025, at 1:12, Quentin de Metz wrote:
>
>> It appears that changing the transaction_timeout when inside a transaction does not work as expected.
>>
>> Running the following script on master:
>>
>> SET transaction_timeout = '1s';
>> BEGIN;
>> SET transaction_timeout = '3s';
>> SELECT pg_sleep(2);
>>
>> Fails with the following:
>>
>> FATAL:  terminating connection due to transaction timeout
>> server closed the connection unexpectedly
>>         This probably means the server terminated abnormally
>>         before or while processing the request.
>>
>> A workaround is to "SET transaction_timeout = 0" before each override. But this resets the timer, which may not be aligned with this parameter's intention.
>>
>
> Hi Quentin!
>
> Thanks for raising this, it's very good to hear more about usage 
> patterns, it really helps to improve.
>
> Together with reviewers we did consider "extending" transaction 
> timeout. But the problem is it promotes very unreliable coding pattern: 
> adjusting time budget without checking how many time is already spent.
>
> Yes, if expectations of your transaction changed you can reset 
> transaction_timeout and set new time budget. Personally, I don't like 
> it either. But it did not seem a good idea to forbid resetting timeout 
> at all or to forbid setting it amid of a transaction: we needed both 
> this functionalities for "SET transaction_timeout = x;" to work.
>
> It's hard to change anything radically in shipped feature. But, if 
> possible, please, tell more about usage patterns beyond pg_sleep(), 
> maybe our assumptions were not accurate enough and we could do better 
> in future.
>
>
> Best regards, Andrey Borodin.