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

Hi Tatsuo

On 7/31/26 10:56, Tatsuo Ishii wrote:
>>> 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

Ok but then how can we explain the system complaining about :

"DISCARD ALL cannot run inside a transaction block" ?

Apparently there was inside a transaction somehow, and upon hitting the
home page the app (Quarkus) apart from the xaction in the logging table
didn't start any other explicitly.

Also the problem never manifested when against plain vanilla postgresql,
or pgbouncer -> postgresql,

And only when against pgbouncer -> pgpool ->
postgresqlmemory_cache_enabled = true

>
> 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.
Anyway , Thanks for the patch Tatsuo !
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English:http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp