Re: low level protocol, implicit transactions , "idle in transaction" issue

Good day Tatsuo ! It must be noon in Japan now!

On 7/24/26 01:52, Tatsuo Ishii wrote:

> Hi,
>
>> sorry I forgot to mention,
>>
>> before the patch, disabling the query cache in pgpool the problem went
>> 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 queries
>>> (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,  I got into the temptation to use our local AI cli agent
>>> (against our local GLM-5.2-NVFP4 ) 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 and
>>> 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 Know 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 hard.
>>>
>>> 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!

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