References: 
 <CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ@mail.gmail.com>
 <ME3P282MB31665F69A72FE8427EE8C49FB694A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <CAEG8a3K8kD0mQDFzj=v0zV7h+m4J4TDBLkXSJJS3+VGWNvnDCQ@mail.gmail.com>
 <ME3P282MB3166AB8DF0381F27C077AF70B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB31663557EEF80E7CA97FBC32B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB316691F6AD09F4F8EA5578C1B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <CAEG8a3+nXPnceKAyFT12XBwDvEe3POvre9Z=h6j2cCiK+zL7vg@mail.gmail.com>
 <ME3P282MB31664A5018CEC4782D2DA7DAB69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <79905780-F316-4DD3-812E-654D460E381C@yandex-team.ru>
 <CAEG8a3KDAWrCY5coVdfO6PNFbNJNgLOusNKE7advB8fy7_6jMw@mail.gmail.com>
 <68965408-F5A3-459F-A84E-5F4F2C237A5B@yandex-team.ru>
 <CAEG8a3J6mSPXgHq_aqC7yk4xq1e9XF9B1Q+QVBr48fzdw1uKqw@mail.gmail.com>
 <D9412036-76BA-4B31-8DB7-F7BE37CA0437@yandex-team.ru>
 <CF08A21B-5CC3-4065-A44E-623553616150@yandex-team.ru>
 <03431609-49D0-445E-97F0-ACF6C526F3EB@yandex-team.ru>
 <59B505DD-1931-49A3-9716-2DD7A002CCC6@yandex-team.ru>
 <F7EABA32-E0A7-4A3F-A953-2ECD4813F726@yandex-team.ru>
 <ME3P282MB3166A9BFA45239291EBC5F1CB667A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
 <DD2C0791-FCDB-4073-848C-7107CAB55EAE@yandex-team.ru>
 <CAHut+PuOdzypMrK3HsSr0F2fyn8VO5z2+EZ=6Sz39QNbBrisHQ@mail.gmail.com>
 <4C30F833-C0F2-4664-B0FF-192FD8AB2E2E@yandex-team.ru>
User-agent: mu4e 1.4.15; emacs 28.1
From: Japin Li <japinli@hotmail.com>
To: "Andrey M. Borodin" <x4mmm@yandex-team.ru>
Cc: Peter Smith <smithpb2250@gmail.com>, Junwang Zhao <zhjwpku@gmail.com>,
 =?utf-8?B?6YKx5a6H6Iiq?= <iamqyh@gmail.com>, Fujii Masao
 <masao.fujii@oss.nttdata.com>, Andrey
 Borodin <amborodin86@gmail.com>, Andres Freund <andres@anarazel.de>,
 Michael Paquier <michael.paquier@gmail.com>, Nikolay Samokhvalov
 <samokhvalov@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Transaction timeout
In-reply-to: <4C30F833-C0F2-4664-B0FF-192FD8AB2E2E@yandex-team.ru>
Date: Fri, 26 Jan 2024 22:58:34 +0800
Message-ID: 
 <ME3P282MB316697D91841CF4EE86CF882B6792@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: ME3P282MB3166:EE_|ME3P282MB0867:EE_
X-MS-Office365-Filtering-Correlation-Id: 8b343e76-e129-4b02-785d-08dc1e7f48d7
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	9Wb+6mpRMYEU9+zmQzzn6YVk8K6b8/54vpS1+N1RhumFShkmLFldxcye8NHwChQI+TvNCjpqoBriJcCn0SU63B0QhxvuXs3od2o9K6IIGP/F0ovE7cInvfrSdxtXqyUYNvj7FQvdqGKRXmrDey8I7TBTtqw8HWfbtHDAyTQp0OwvcWodY69fuK07dHsx9pvCZp8pj2EcxMwk5218EgTPlV47tyOQ2rfOflXZbFo212tY+vvx5CxF+f5LS1ufhmBTR/ARhpOaHnInl7rZpkGU4KB9kJqj6mfX/rSTexia4MA9xyBX+nbsvfQkDd358uS3H91/hMHIXoin2zR6n14tSRuilESLuxlFP4OawgFqfqQPsvtmxrEQznOjIb3SOCoN/vQ9+3nGDKPX979jDrc4cLPuQLSV6gK31gOVEKNGm8FsDFFGzr6g+l8Dhtg/k7lhHuHa4OnzotNAqvtVeS8GqkKEKAop4nk/ZfLbVMPnrBukeN4XQ3jZlnaae/BtVM4bpJ/wMj9LnvTZSkxHvEtAWci1IN9tNWV5iL1DP5+8c5f+XICu17yCrivOCJ7inYya
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?WUlTdEhIaWdQVDM0elhobHpmNnpKMXZrMmRxZkRoM09ncXR3aEZpZFByRGpm?=
 =?utf-8?B?OTFLNWZoYzVYdUU2MTdjbVZYeUM3TzVqOWtNRDA0SjBsRXBaMk95N1ppN2Vu?=
 =?utf-8?B?Tll6OXNWSDRBdzM1TkVaM29DOFNCMHcxNnlWa1RUVlBWaFEzMzVvS2RWY2o4?=
 =?utf-8?B?eHFhQ1ZoK3JuTW1YaU5DYm5NcVl4VktER2JxRHZZU2JSVHhTQnZmaHd5bllV?=
 =?utf-8?B?Q2FyTGJEbUdSWVNsdkFrbXVMRWVuTUZHR2orTVFCME5Cb0ZoVC82NHBrWkpq?=
 =?utf-8?B?aGNteVpZbE0vU3ZtYXJTaXIrTXlqTWFFdWsvbjc1ck92WkFONVZWRTNIMVBq?=
 =?utf-8?B?MjY1Undpa3I0a0gzMThjS2hIbjJkTjNNSFpmRGVwU05uNjlVamRDLzVuQk9X?=
 =?utf-8?B?aDZPaVQzMmM0THhSaUZQMjNFb2t6NzZoRFJPZTdHRUszSSs1a3ZQOWFycGVF?=
 =?utf-8?B?dnNiUDN6RzMvTmRaRm5qVVlWQXFaRXZZYXIyVDQ0ZVkxS1RCWGFOd0I0Q0RT?=
 =?utf-8?B?SmRyUTJrT2c5ZE1RT2YyRitwY2J1VTZOV1RuRkNWclhqcTJJQkVqb2JFUmda?=
 =?utf-8?B?WlpFMGJ5cWEwSWtvVUVMSjRJb0hRNzFvV2VudWtoL3NkeTRlUnZoVWZUMmhV?=
 =?utf-8?B?QTNCL041OE93cGJreXZjZmdsVkU0U05TYUlnT1NPYVUxZ2oyek5WdEV1bXpB?=
 =?utf-8?B?bExUWE5NM2VMWCsxSU5USk9WWXoyN09UN3RvaUJiNjJjVmJWOTdWQmw1STR5?=
 =?utf-8?B?elRYWFBpd3RRMWY3MEV5RjZXaG81bmZ4ZEtBU0RySUkwQzFxcUJHazd6bXFu?=
 =?utf-8?B?ZXh6OGp6b3BOdzEyNkpjZ0hmUFV0bm43cnY4aTdpTmJpQ0lLSnJ6WlZrSUxw?=
 =?utf-8?B?RlpNU3NwenhZWkg2Z1c1US9ReVhNTmdGdFhCVytXZ0VUMmxQb3lyeVprRDJD?=
 =?utf-8?B?RVNoMFFGOFBkL2QrNllHY21nMGV4Nk9sd2FDVWpCekwzOTBzcjFCN2ROcjF3?=
 =?utf-8?B?MDQzSWd4d1J2eEs2b1FBandoOEtIbjEvSUovcVNlOTcvd3U1Szk5UXJZNHFq?=
 =?utf-8?B?WjQwVysxWUVEdE5YN3I2MUV4ZnNKTndjY1dQQVFyRmxtdThPQUc2b1FJOVIr?=
 =?utf-8?B?NElrdlRUT0NCbks4cTA2dEJzV2czd0o1aFFjbFppN3pPRXBCd0tSZHIwQkxX?=
 =?utf-8?B?YzVxUmFQejZzcTkzVlZubmZWTk9OaGFUd1BqODZPYVhJNGdzWFQ1bFgvWkk4?=
 =?utf-8?B?Mm83d0theWFGUXM3dmZZNGNrcFhnS0lJd2xLS291RlhMUVdmcGRaNEJvdzg1?=
 =?utf-8?B?Ri9vWTI4Rzkvd1l6RWlIZFN6OWJIMDlhbSsyN2VUOVN6cnVSQVZtWDlYTUV4?=
 =?utf-8?B?M3d4QWgzVDBlcnM4OVJ4L0g0V1A1ajlkMEFnS1U4Z0VpcHl3M28xb0tFcHJH?=
 =?utf-8?B?bmlXYjh0NUxWTFE0UlNZeUJzb1RTbmd1MWF3amVIU2ROZmc3MFFHeGlBS29i?=
 =?utf-8?B?cXBqanVpdUQwMnVLcVZhNDVCMzJQRW5kUlVBTWVNK08vdUdGQlpyM25mTHZ1?=
 =?utf-8?B?OWRZVFM2WTliUjl3SFdVaXppcTdLc0JEcjdpU2ZjMW4xVWZVZWRKZUFoNjdj?=
 =?utf-8?B?SlNJRWJUckZZRGRFT2dlMlltNHZYSHc9PQ==?=
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-746f3.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 8b343e76-e129-4b02-785d-08dc1e7f48d7
X-MS-Exchange-CrossTenant-AuthSource: ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 26 Jan 2024 14:58:42.6770
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME3P282MB0867
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/ME3P282MB316697D91841CF4EE86CF882B6792%40ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
Precedence: bulk


On Fri, 26 Jan 2024 at 14:44, Andrey M. Borodin <x4mmm@yandex-team.ru> wrot=
e:
>> On 22 Jan 2024, at 11:23, Peter Smith <smithpb2250@gmail.com> wrote:
>>
>> Hi, This patch has a CF status of "Needs Review" [1], but it seems
>> there was a CFbot test failure last time it was run [2]. Please have a
>> look and post an updated version if necessary.
> Thanks Peter!
>

Thanks for updating the patch.  Here are some comments for v24.

+       <para>
+        Terminate any session that spans longer than the specified amount =
of
+        time in transaction. The limit applies both to explicit transactio=
ns
+        (started with <command>BEGIN</command>) and to implicitly started
+        transaction corresponding to single statement. But this limit is n=
ot
+        applied to prepared transactions.
+        If this value is specified without units, it is taken as milliseco=
nds.
+        A value of zero (the default) disables the timeout.
+       </para>
The sentence "But this limit is not applied to prepared transactions" is re=
dundant,
since we have a paragraph to describe this later.

+
+       <para>
+        If <varname>transaction_timeout</varname> is shorter than
+        <varname>idle_in_transaction_session_timeout</varname> or <varname=
>statement_timeout</varname>
+        <varname>transaction_timeout</varname> will invalidate longer time=
out.
+       </para>
+

Since we are already try to disable the timeouts, should we try to disable
them even if they are equal.

+
+       <para>
+        Prepared transactions are not subject for this timeout.
+       </para>

Maybe wrap this with <note> is a good idea.

> I=E2=80=99ve inspected CI fails and they were caused by two different pro=
blems:
> 1. It=E2=80=99s unsafe for isaoltion tester to await transaction_timeout =
within a query. Usually it gets
> FATAL: terminating connection due to transaction timeout
> But if VM is a bit slow it can get occasional
> PQconsumeInput failed: server closed the connection unexpectedly
> So, currently all tests use =E2=80=9Cpassive waiting=E2=80=9D, in a sessi=
on that will not timeout.
>
> 2. In some cases pg_sleep(0.1) were sleeping up to 200 ms. That was makin=
g s7 and s8 fail, because they rely on this margin.

I'm curious why this happened.

> I=E2=80=99ve separated these tests into different test timeouts-long and =
increased margin to 300ms. Now tests run horrible 2431 ms. Moreover I=E2=80=
=99m afraid that on buildfarm we can have much randomly-slower machines so =
this test might be excluded.
> This test checks COMMIT AND CHAIN and flow of small queries (Nik=E2=80=99=
s case).
>
> Also I=E2=80=99ve verified that every "enable_timeout_after(TRANSACTION_T=
IMEOUT)=E2=80=9D and =E2=80=9Cdisable_timeout(TRANSACTION_TIMEOUT)=E2=80=9D=
 is necessary and found that case of aborting "idle in transaction (aborted=
)=E2=80=9D is not covered by tests. I=E2=80=99m not sure we need a test for=
 this.

I see there is a test about idle_in_transaction_timeout and transaction_tim=
eout.

Both of them only check the session, but don't check the reason, so we cann=
ot
distinguish the reason they are terminated.  Right?

> Japin, Junwang, what do you think?

However, checking the reason on the timeout session may cause regression te=
st
failed (as you point in 1), I don't strongly insist on it.

--
Best regards,
Japin Li.