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

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(at)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(at)%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!(at)#$%^'
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
>
>