From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgpool-general@lists.postgresql.org
Cc: Achilleas Mantzios <itdev@gatewaynet.com>
Subject: Re: low level protocol, implicit transactions , "idle in transaction" issue
Date: Tue, 28 Jul 2026 12:14:09 +0300
Message-ID: <23fee34e-1d0a-4592-9add-1a6744f80d34@cloud.gatewaynet.com> (raw)
In-Reply-To: <b5cc54cb-d340-4355-8b5d-166cee2da536@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>
<20260728.100131.1277286284360653494.ishii@postgresql.org>
<b5cc54cb-d340-4355-8b5d-166cee2da536@cloud.gatewaynet.com>
Hi Tatsuo
On 7/28/26 07:34, Achilleas Mantzios wrote:
> 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.
I re-tested without the patch.
Here is our config :
pgpool@smadevnu:~ % egrep -e '^[a-z]+' /usr/local/pgpool/etc/pgpool.conf
backend_clustering_mode = streaming_replication
listen_addresses = '*'
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_data_directory0 = '/usr/local/var/lib/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'smadevnu'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
ssl = on
ssl_key = '/usr/local/pgpool/etc/server.key'
ssl_cert = '/usr/local/pgpool/etc/server.crt'
num_init_children = 200
max_pool = 4
log_line_prefix = '%r [%p] %c %m %a %u@%d line:%l ' # printf-style
string to output at beginning of each log line.
log_connections = on
log_disconnections = on
log_statement = off
log_client_messages = on
log_min_messages = debug1 # values in order of decreasing
detail:
logging_collector = on
log_directory = '/usr/local/pgpool/log'
log_filename = 'pgpool-%Y-%m-%d.log'
log_truncate_on_rotation = on
connection_cache = on
sr_check_user = 'periodic'
sr_check_password = 'foo4foo!@#$%^'
memory_cache_enabled = on
Here is the pgpool log, pls focus on pid = 57366
>>
>> Regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>
>
Attachments:
[text/x-log] sample_pgpool.log (382.1K, ../23fee34e-1d0a-4592-9add-1a6744f80d34@cloud.gatewaynet.com/3-sample_pgpool.log)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgpool-general@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, pgpool-general@lists.postgresql.org, itdev@gatewaynet.com
Subject: Re: low level protocol, implicit transactions , "idle in transaction" issue
In-Reply-To: <23fee34e-1d0a-4592-9add-1a6744f80d34@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox