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.klaver@aklaver.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.klaver@aklaver.com