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

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.

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