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

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

> multiple statements inside a query

is not supported in extended query protocol.

> pipelining

Yes, extended query protocol (including pipelining) with no explicit
transaction will start an implicit transaction in the backend. In fact
I get following error on DISCARD ALL (which was executed because
pgpool's reset_query_list) with no explicit transaction started.

2026-07-29 09:40:19.975: pgproto pid 447427: DEBUG: do_query: extended:1 query:"SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'"
2026-07-29 09:36:06.853: pgproto pid 447075: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 447113 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"

Question is, whether pg_stat_activity.state shows the query state in
the (implicit) transactions as "idle in transaction" or not. To
confirm this, I modified pgproto to allow the pgproto frontend sleep
so that I can check what pg_stat_activity.state looks like after
sending extended query (SELECT) and flush message, and before sending
Sync message[1].

psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
" test
state | backend_xid | query
--------+-------------+---------------------------------------------------------------
active | | SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'

The state was "active". After the Sync message was sent:

psql -p 11002 -c "select state,backend_xid, query from pg_stat_activity where pid <> pg_backend_pid();
" test
state | backend_xid | query
--------+-------------+-----------------------------------------
idle | | DISCARD ALL

So I don't see "idle in trasaction" here.

>> Anyway, adding:
>> log_client_messages = on
>> will record helpful pgpool.log.

I looked in the pgpool.log and found this:

[57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio(at)dynacom line:383 LOG: Query message from frontend.
[57366] 2026-07-28 09:30:11.086 qSMAdynacom amantzio(at)dynacom line:384 DETAIL: query: "BEGIN"

So I suspect the reason "idle in trasaction" was seen is, client
actually create an explicit transaction by sending "BEGIN".

[1] pgproto.data (modification to pgproto is attached)
#----------------------------------------------------------
'P' "" "SELECT 1" 0
'B' "" "" 0 0 0
'D' 'P' ""
'E' "" 0

'P' "" "SELECT 2" 0
'B' "" "" 0 0 0
'D' 'P' ""
'E' "" 0

'H'

# sleep 60000 milli seconds (= 60 seconds)
's' 60000

'S'
'Y'
'X'
#----------------------------------------------------------

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp