Re: Transaction timeout

On Fri, 22 Dec 2023 at 20:29, Junwang Zhao <zhjwpku(at)gmail(dot)com> wrote:
> On Fri, Dec 22, 2023 at 1:39 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>>
>>
>> On Tue, 19 Dec 2023 at 22:06, Japin Li <japinli(at)hotmail(dot)com> wrote:
>> > On Tue, 19 Dec 2023 at 18:27, Andrey M. Borodin <x4mmm(at)yandex-team(dot)ru> wrote:
>> >>> On 19 Dec 2023, at 13:26, Andrey M. Borodin <x4mmm(at)yandex-team(dot)ru> wrote:
>> >>>
>> >>> I don’t have Windows machine, so I hope CF bot will pick this.
>> >>
>> >> I used Github CI to produce version of tests that seems to be is stable on Windows.
>> >
>> > It still failed on Windows Server 2019 [1].
>> >
>> > diff -w -U3 C:/cirrus/src/test/isolation/expected/timeouts.out C:/cirrus/build/testrun/isolation/isolation/results/timeouts.out
>> > --- C:/cirrus/src/test/isolation/expected/timeouts.out 2023-12-19 10:34:30.354721100 +0000
>> > +++ C:/cirrus/build/testrun/isolation/isolation/results/timeouts.out 2023-12-19 10:38:25.877981600 +0000
>> > @@ -100,7 +100,7 @@
>> > step stt3_check_stt2: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/stt2'
>> > count
>> > -----
>> > - 0
>> > + 1
>> > (1 row)
>> >
>> > step itt4_set: SET idle_in_transaction_session_timeout = '1ms'; SET statement_timeout = '10s'; SET lock_timeout = '10s'; SET transaction_timeout = '10s';
>> >
>> > [1] https://api.cirrus-ci.com/v1/artifact/task/4707530400595968/testrun/build/testrun/isolation/isolation/regression.diffs
>>
>> Hi,
>>
>> I try to split the test for transaction timeout, and all passed on my CI [1].
>>
>> OTOH, I find if I set transaction_timeout in a transaction, it will not take
>> effect immediately. For example:
>>
>> [local]:2049802 postgres=# BEGIN;
>> BEGIN
>> [local]:2049802 postgres=*# SET transaction_timeout TO '1s';
> when this execute, TransactionTimeout is still 0, this command will
> not set timeout
>> SET
>> [local]:2049802 postgres=*# SELECT relname FROM pg_class LIMIT 1; -- wait 10s
> when this command get execute, start_xact_command will enable the timer

Thanks for your exaplantion, got it.

>> relname
>> --------------
>> pg_statistic
>> (1 row)
>>
>> [local]:2049802 postgres=*# SELECT relname FROM pg_class LIMIT 1;
>> 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.
>> The connection to the server was lost. Attempting reset: Succeeded.
>>
>> It looks odd. Does this is expected? I'm not read all the threads,
>> am I missing something?
>
> I think this is by design, if you debug statement_timeout, it's the same
> behaviour, the timeout will be set for each command after the second
> command was called, you just aren't aware of this.
>

I try to set idle_in_transaction_session_timeout after begin transaction,
it changes immediately, so I think transaction_timeout should also be take
immediately.

> I doubt people will set this in a transaction.

Maybe not,

--
Regrads,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.