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

On 7/28/26 04:01, Tatsuo Ishii wrote:

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

IMHO, the only chance for this behavior , is to have multiple statements
inside a query, or maybe the effect of pipelining comes into effect , as
per :

https://www.postgresql.org/docs/current/protocol-flow.html

>
> Anyway, adding:
> log_client_messages = on
> will record helpful pgpool.log.
Thank you.
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp