Re: PostgreSQL server "idle in transaction"

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(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?

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(dot)klaver(at)aklaver(dot)com