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

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

I don't believe the 'idle in transaction' is caused by an implicit
transaction. I think the AI generated comment below is a
hallucination.

+ * returns. This is observable with clients that use the extended
+ * protocol with implicit transactions (e.g. Quarkus/Agroal) behind
+ * pgbouncer, and only when memory_cache_enabled = on, because that
+ * is what makes pgpool issue these internal catalog lookups via
+ * do_query() outside of an explicit transaction. Sync closes the

I plan to apply attached patch (rebased for master branch) with
modified comment by me. The reason I am going to apply is, not prevent
the "idle in transaction" symptom reported by you (because I cannot
reproduce it); the patch prevent the error when DISCARD ALL is
executed in a reset_query_list:

2026-07-31 16:37:28.194: pgproto pid 503581: LOG: pool_send_and_wait: Error or notice message from backend: DB node id: 0 backend pid: 503597 statement: "DISCARD ALL" message: "DISCARD ALL cannot run inside a transaction block"

When extended queries are executed:
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Parse: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Bind: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: D message
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Execute: SELECT 1
2026-07-31 16:40:58.484: pgproto pid 504738: LOG: DB node id: 1 backend pid: 504770 statement: Sync
2026-07-31 16:40:58.487: pgproto pid 504738: LOG: DB node id: 0 backend pid: 504771 statement: SELECT oid FROM pg_catalog.pg_database WHERE datname = 'test'

If query cache is enabled, even frontend sends a sync message,
do_query sends query "SELECT oid FROM pg_catalog.pg_database WHERE
datname = 'test" to get database oid because it needs the data to
register the query cache result. The query is executed in extended
query protocol because do_extended query mode continues. As a result
the implicit transaction started by do_query is not closed and the
error occurs.

The patch closes the implicit transaction started by do_query and the
error is gone.

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

It's obvious that with the patch pgpool send "sync" instead of
"flush". You don't need to check the tcpdump output.

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