Re: Transaction timeout - Mailing list pgsql-hackers

On 2023-09-06 20:32, Andrey M. Borodin wrote:
> Thanks for looking into this!
> 
>> On 6 Sep 2023, at 13:16, Fujii Masao <masao.fujii@oss.nttdata.com> 
>> wrote:
>> 
>> While testing v4 patch, I noticed it doesn't handle the COMMIT AND 
>> CHAIN case correctly.
>> When COMMIT AND CHAIN is executed, I believe the transaction timeout 
>> counter should reset
>> and start from zero with the next transaction. However, it appears 
>> that the current
>> v4 patch doesn't reset the counter in this scenario. Can you confirm 
>> this?
> Yes, I was not aware of this feature. I'll test and fix this.
> 
>> 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?
> AFAIR I had been testing that behaviour of "idle in transaction" was
> intact. I'll check that again.
> 
>> 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.
> Yes, this is exactly how I was expecting the feature to behave: empty
> up max_connections slots for long-hanging transactions.
> 
> Thanks for your findings, I'll check and post new version!
> 
> 
> Best regards, Andrey Borodin.
Hi,

Thank you for implementing this nice feature!
I tested the v4 patch in the interactive transaction mode with 3 
following cases:

1. Start a transaction with transaction_timeout=0 (i.e., timeout 
disabled), and then change the timeout value to more than 0 during the 
transaction.

=# SET transaction_timeout TO 0;
=# BEGIN;    //timeout is not enabled
=# SELECT pg_sleep(5);
=# SET transaction_timeout TO '1s';
=# SELECT pg_sleep(10);    //timeout is enabled with 1s
In this case, the transaction timeout happens during pg_sleep(10).

2. Start a transaction with transaction_timeout>0 (i.e., timeout 
enabled), and then change the timeout value to more than 0 during the 
transaction.

=# SET transaction_timeout TO '1000s';
=# BEGIN;    //timeout is enabled with 1000s
=# SELECT pg_sleep(5);
=# SET transaction_timeout TO '1s';
=# SELECT pg_sleep(10);    //timeout is not restarted and still running 
with 1000s
In this case, the transaction timeout does NOT happen during 
pg_sleep(10).

3. Start a transaction with transaction_timeout>0 (i.e., timeout 
enabled), and then change the timeout value to 0 during the transaction.

=# SET transaction_timeout TO '10s';
=# BEGIN;    //timeout is enabled with 10s
=# SELECT pg_sleep(5);
=# SET transaction_timeout TO 0;
=# SELECT pg_sleep(10);    //timeout is NOT disabled and still running 
with 10s
In this case, the transaction timeout happens during pg_sleep(10).

The first case where transaction_timeout is disabled before the 
transaction begins is totally fine. However, in the second and third 
cases, where transaction_timeout is enabled before the transaction 
begins, since the timeout has already enabled with a certain value, it 
will not be enabled again with a new setting value.

Furthermore, let's say I want to set a transaction_timeout value for all 
transactions in postgresql.conf file so it would affect all sessions. 
The same behavior happened but for all 3 cases, here is one example with 
the second case:

=# BEGIN; SHOW transaction_timeout; select pg_sleep(10); SHOW 
transaction_timeout; COMMIT;
BEGIN
  transaction_timeout
---------------------
  15s
(1 row)

2023-09-07 11:52:50.510 JST [23889] LOG:  received SIGHUP, reloading 
configuration files
2023-09-07 11:52:50.510 JST [23889] LOG:  parameter 
"transaction_timeout" changed to "5000"
  pg_sleep
----------

(1 row)

  transaction_timeout
---------------------
  5s
(1 row)

COMMIT

I am of the opinion that these behaviors might lead to confusion among 
users. Could you confirm if these are the intended behaviors?

Additionally, I think the short description should be "Sets the maximum 
allowed time to commit a transaction." or "Sets the maximum allowed time 
to wait before aborting a transaction." so that it could be more clear 
and consistent with other %_timeout descriptions.

Also, there is a small whitespace error here:
src/backend/tcop/postgres.c:3373: space before tab in indent.
+                                                               
stmt_reason, comma2, tx_reason)));

On a side note, while testing the patch with pgbench, it came to my 
attention that in scenarios involving the execution of multiple 
concurrent transactions within a high contention environment and with 
relatively short timeout durations, there is a potential for cascading 
blocking. This phenomenon can lead to multiple transactions exceeding 
their designated timeouts, consequently resulting in a degradation of 
transaction processing performance. No?
Do you think this feature should be co-implemented with the existing 
concurrency control protocol to maintain the transaction performance 
(e.g. a transaction scheduling mechanism based on transaction timeout)?

Regards,
Tung Nguyen