[pgpool-general: 6516] 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.

> Our Postgres servers are on Amazon Aurora, and we are using pgpool for load
> balancing only.
> 
> PostgreSQL version 9.6.9
> pgpool-II version 3.7.8
> 
> Initially we had client_idle_limit set to 180, changed to 0, but did not
> have any effect.

Surely it has no effect on this issue since setting it to 0 will
disregard idle time in clients, which could make the situation worse.

> We also tested with connection_cache on and off, with the
> same result.

> The logs don't show much even with log level set to debug --
> we can see the query logged, but nothing else from that PID afterwards, it
> appears to silently exit once Postgres drops the connection due to the
> timeout.

I am not sure if Auroa behaves differently in this regard but I see
following if I set idle_in_transaction_session_timeout to very small
value (10).

test=# begin;
BEGIN
test=# select 1;
FATAL:  terminating connection due to idle-in-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.

Have you tried the same setting with pure PostgreSQL (not Aurora)?

> Just wanted to reach out to see if any high level insight or guidance can
> be provided. If there is a better place for this, or if more details are
> needed, let me know.
> 
> Thanks,
> J