[pgpool-general: 6517] Re: terminating connection due to idle-in-transaction timeout when using pgpool

[Tatsuo Ishii]

>> Hello,
>> 
>> We are testing out pgpool, and overall it is working fine, but sometimes we
>> are running into idle-in-transaction timeouts. These same transactions are
>> able to complete without any issues when we are hitting the DB directly. If
>> we increase idle_in_transaction_session_timeout on the DB, it works, but
>> seems like a bandaid since we don't run into this without pgpool.
> 
> idle_in_transaction_session_timeout kills a session if PostgreSQL
> thinks the session is idle for specified time. My guess is the
> difference of the behavior is caused by Pgpool-II to take longer time
> for processing. You can disable idle_in_transaction_session_timeout by
> setting it to 0.

I have tested with ordinary PostgreSQl and succeeded in reproducing
the issue. Here is the script to reproduce the problem using two node
streaming replication cluster.

set idle_in_transaction_session_timeout to 1800;
begin;
select pg_sleep(1);
select pg_sleep(1);
end;

I found that with default setting of pgpool.conf, the probability to
have the issue is 50%.  If I set backend_weight1 to 0, the probability
is 100%. If I set backend_weight0 to 0, the probability is 0%.

Let me explain why.

(1) If load balance node is 1, "begin" is sent to both node 0 and
1. So both backends start to count down the timer. Following two
"select pg_sleep(1);" are sent to node 1 which reset the counter to 0
each time, so everything is ok. However on node 0, the transaction
keeps idle for 2000 ms, which exceeds the timeout.

(2) If load balance node is 0, "begin" is sent to node 0
only. Following two "select pg_sleep(1);" are sent to node 0 which
reset the counter to 0 each time, so everything is ok

If backend_weight0 is 0, (1) always happen.  If backend_weight1 is 0,
(2) always happen.  With default configuration both backend_weight0
and 1 is 1, the probability for (1) or (2) is 50%.

In summary, to deal with problem followings are possible workarounds.

a) disable idle_in_transaction_session_timeout

b) set backend_weight1 to 0. This effectively is same as
load_balance_mode = off.

Since you are using Pgpool-II for load balancing, probably b) would
not be acceptable. So the only the choice would be a).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp