On Wed, Sep 6, 2023 at 1:16 AM Fujii Masao <masao.fu...@oss.nttdata.com> wrote: > With the v4 patch, I found that timeout errors no longer occur during the > idle in > transaction phase. Instead, they occur when the next statement is executed. > Is this > the intended behavior? I thought some users might want to use the transaction > timeout > feature to prevent prolonged transactions and promptly release resources > (e.g., locks) > in case of a timeout, similar to idle_in_transaction_session_timeout.
I agree – it seems reasonable to interrupt transaction immediately when the timeout occurs. This was the idea – to determine the maximum possible time for all transactions that is allowed on a server, to avoid too long-lasting locking and not progressing xmin horizon. That being said, I also think this wording in the docs: + Setting <varname>transaction_timeout</varname> in + <filename>postgresql.conf</filename> is not recommended because it would + affect all sessions. It was inherited from statement_timeout, where I also find this wording too one-sided. There are certain situations where we do want global setting to be set – actually, any large OLTP case (to be on lower risk side; those users who need longer timeout, can set it when needed, but by default we do need very restrictive timeouts, usually < 1 minute, like we do in HTTP or application servers). I propose this: > Setting transaction_timeout in postgresql.conf should be done with caution > because it affects all sessions. Looking at the v4 of the patch, a couple of more comments that might be helpful for v5 (which is planned, as I understand): 1) it might be beneficial to add tests for more complex scenarios, e.g., subtransactions 2) In the error message: + errmsg("canceling statement due to %s%s%s%s%s", lock_reason, comma1, + stmt_reason, comma2, tx_reason))); – it seems we can have excessive commas here 3) Perhaps, we should say that we cancel the transaction, not statement (especially in the case when it is happening in the idle-in-transaction state). Thanks for working on this feature!