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

>>> 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!

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 = on
log_min_messages = debug1
reset_query_list = '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 backend_type = 'client backend'" test

state | query
--------+--------------------------------------------------------------------------------
idle | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'
active | select state,query from pg_stat_activity where backend_type = '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 = 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