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

On 8/2/26 09:50, Tatsuo Ishii wrote:

> Hi Achilleas,
>
>>> 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".
> Patch pushed to all supported branches.
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=e4a3a0c13e4b5e1a015aca3db238b21b88e73e2f
>
> As I wroite in the commit messages, I hoped the patch fixes "DISCARD
> ALL cannot run inside a transaction block" error.
>
> However, the original intension of the patch was to fix "idle in
> transaction" left in pg_stat_activity. Please try the 4.7 patch if you
> like.

Thank you Tatsuo for the hard work you are putting into pgpool !

I don't quite feel right about the course of events during this thread,
meaning me resorting to our local AI to pull the iron out of the fire, I
hope to more personal involvement next round! at least I wish so.

>
> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bc3689a2d62f2083699b86feb267e90296913c26
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp