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

Hi Tatsuo

On 7/29/26 03:56, Tatsuo Ishii wrote:
>>> 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.
Cool thanks.
>
>> 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".

Yes there is an explicit begin, always followed by (as you can see in
the same log) :

[57366]  2026-07-28 09:30:11.089 qSMAdynacom amantzio(at)dynacom line:459
DETAIL:  statement: "", query: "COMMIT"

Yes, no matter how many 'y' I put in pgproto.data I didn't manage to get
the 'idle in transaction', but alone this does not prove there is no
problem.

The effect with the 'idle in transaction' without my patch is consistent.

Would you like to send you a tcpdump from pgpool -> pgsql , without my
patch and with my patch in order to spot the difference ?

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