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

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

It does not necessarily mean pg_stat_activity shows it as "idle in
transaction". From my experience, without issuing an explicit
transaction from client, pg_stat_activity shows "idle" or "active",
but never "idle in transaction". I guess PostgreSQL distinguish an
explicit transaction and an implicit transaction.

> 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

Yes, in the case above, pgpool issues do_query which causes open
implicit transaction. But again, I think it does not cause
pg_stat_activity showing "idle in transaction".

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