Date: Tue, 28 Jul 2026 10:01:31 +0900 (JST)
Message-Id: <20260728.100131.1277286284360653494.ishii@postgresql.org>
To: a.mantzios@cloud.gatewaynet.com
Cc: pgpool-general@lists.postgresql.org
Subject: Re: low level protocol, implicit transactions , "idle in
 transaction" issue
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <22c751e9-fbf5-4dc0-b254-5b8e8adac818@cloud.gatewaynet.com>
References: <754bc89e-abab-43a9-bf7e-1b3acf73d931@cloud.gatewaynet.com>
	<20260724.075234.1074150663970997670.ishii@postgresql.org>
	<22c751e9-fbf5-4dc0-b254-5b8e8adac818@cloud.gatewaynet.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20260728.100131.1277286284360653494.ishii%40postgresql.org>
Precedence: bulk

>>> sorry I forgot to mention,
>>>
>>> before the patch, disabling the query cache in pgpool the problem w=
ent
>>> away.
>>>
>>> On 7/22/26 17:32, Achilleas Mantzios wrote:
>>>> Dear pgpool team
>>>>
>>>> This is our setup :
>>>>
>>>> Quarkus (Agroal) -> pgbouncer-1.25.1 -> pgpool 4.7.2 -> pgsql 18.3=

>>>>
>>>> We noticed that with no explicit transactions, at start up SQL que=
ries
>>>> (application_name, search_path, etc), we experienced random 'idle =
in
>>>> transaction' connections from pgpool -> pgsql.
>>>>
>>>> Bypassing pgpool, i.e. pgbouncer -> directly to pgsql , the issue =
was
>>>> not there.
>>>>
>>>> So, bypassing the past excellent relation and collaboration I had =
with
>>>> you guys,=A0 I got into the temptation to use our local AI cli age=
nt
>>>> (against our local GLM-5.2-NVFP4=A0) from within pgpool 4.7.2 code=
 base
>>>> and try to see what's going on.
>>>>
>>>> So it gave it a thorough examination, looked at past issues, etc a=
nd
>>>> it came up with a patch. It applied the patch , compiled and
>>>> installed, and I only had to restart pgpool.
>>>>
>>>> The problem seems gone .
>>>>
>>>> I attach the patch. Please review and tell me your thoughts. I Kno=
w I
>>>> should maybe go the classic route, mailing list, advice from you, =
full
>>>> logging, etc till I demo the issue, but the time is pushing us har=
d.
>>>>
>>>> Thank you!
>> So your problem occurs only if following conditions are all met:
>>
>> 1) query cache is enabled
>> 2) no explicit transaction is used
>> 3) extended query protocol is used
>>
>> Am I correct?
> =

> Yes exactly!

So I trited to reprodce the issue using pgproto. Tool chain is:

pgproto->pgpool->PostgreSQL

pgpool.conf is set up by pgpool_setup and I added followings to pgpool.=
conf.

memory_cache_enabled =3D on
log_min_messages =3D debug1
reset_query_list =3D 'ABORT'

I set reset_query_list to exclude "DISCARD ALL" since if it's
included, PostgreSQL complains that DISCARD ALL cannot be executed
inside transaction. This issue needs to be solved but I think it's not
related to your issue.

Here is the test data for pgproto.

'P'	""	"SELECT * FROM t1"	0
'B'	""	""	0	0	0
'D'	'P'	""
'E'	""	0
'S'
'Y'
'X'

This executes parse (SELECT * FROM t1, using unnamed statement), bind
(using unnamed portal), Describye, Execute and Sync.

Here is the result from pg_stat_activity.

psql -p 11002 -c "select state,query from pg_stat_activity where backen=
d_type =3D 'client backend'" test

 state  |                                     query                    =
                  =

--------+--------------------------------------------------------------=
------------------
 idle   | SELECT oid FROM pg_catalog.pg_database WHERE datname =3D 'tes=
t'
 active | select state,query from pg_stat_activity where backend_type =3D=
 'client backend'
(2 rows)

So the "state" was "idle", not "idle in transaction". I am wondering
why you get "idle in trasaction" without issuing an explicit
transaction from your side. Is it possible that your tool chain always
start a trasanction internally? What would happen if you bypass
pgbouncer?

Anyway, adding:
log_client_messages =3D on
will record helpful pgpool.log.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp