Message-ID: <b5cc54cb-d340-4355-8b5d-166cee2da536@cloud.gatewaynet.com>
Date: Tue, 28 Jul 2026 07:34:14 +0300
MIME-Version: 1.0
Subject: Re: low level protocol, implicit transactions , "idle in transaction"
 issue
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgpool-general@lists.postgresql.org
References: <754bc89e-abab-43a9-bf7e-1b3acf73d931@cloud.gatewaynet.com>
 <20260724.075234.1074150663970997670.ishii@postgresql.org>
 <22c751e9-fbf5-4dc0-b254-5b8e8adac818@cloud.gatewaynet.com>
 <20260728.100131.1277286284360653494.ishii@postgresql.org>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <20260728.100131.1277286284360653494.ishii@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b5cc54cb-d340-4355-8b5d-166cee2da536%40cloud.gatewaynet.com>
Precedence: bulk

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