Re: PostgreSQL server "idle in transaction"

El día Dienstag, November 15, 2022 a las 10:28:11 -0500, Tom Lane escribió:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 11/15/22 04:28, Matthias Apitz wrote:
> >> I have below the full ESQL/C log and do not understand, why the
> >> PostgreSQL server is thinking "idle in transaction". For me with the
> >> "COMMIT" on the line below marked with ^^^^^ the transaction was closed.
>
> > From your log:
>
> > [6978] [15.11.2022 11:05:50:172]: ECPGtrans on line 1211: action
> > "commit"; connection "testdb"
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > [6978] [15.11.2022 11:05:50:173]: ecpg_execute on line 543: query:
> > select name from pg_cursors where name = $1 ; with 1 parameter(s) on
> > connection testdb
>
> > So that query is being executed after the COMMIT.
>
> Right. By default, ecpg would start a new transaction block for that.
> See
>
> https://www.postgresql.org/docs/current/ecpg-commands.html#ECPG-TRANSACTIONS
>

Thank you. This page makes it clear why the last search started a
transaction, which at the end is pending due to a missing COMMIT.

On the other hand, when we would set EXEC SQL SET AUTOCOMMIT TO ON, when does
the transaction block starts exactly (which could be rolled back), as
there is no EXEC SQL BEGIN?

Thanks again

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!