On 11/16/22 12:51 AM, Matthias Apitz wrote:
El día Dienstag, November 15, 2022 a las 10:28:11 -0500, Tom Lane escribió:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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?
I don't use ecpg, but the docs mention:
"The embedded SQL interface also supports autocommit of transactions
(similar to psql's default behavior)"
In psql:
test_(postgres)(5432)=# select 1/0;
ERROR: division by zero
test_(postgres)(5432)=# rollback ;
WARNING: there is no transaction in progress
ROLLBACK
test_(postgres)(5432)=# select 1/1;
?column?
----------
1
versus explicit transaction:
test_(postgres)(5432)=# begin ;
BEGIN
test_(postgres)(5432)=# select 1/0;
ERROR: division by zero
test_(postgres)(5432)=# select 1/1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
test_(postgres)(5432)=# rollback ;
ROLLBACK
test_(postgres)(5432)=# select 1/1;
?column?
----------
1
Thanks again
matthias
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
