Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction

On 5/13/20 11:16 AM, Matthias Apitz wrote:
> El día Mittwoch, Mai 13, 2020 a las 08:15:40 -0700, Adrian Klaver escribió:
> 
>> In your original post you had:
>>
>> "We're facing in our ESQL/C written application a situation where a
>> commit'ed INSERT into a table is rolled back. I have here the ESQL/C
>> logging of the problem:"
>> ...
>>
>> "The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line)
>> and a later rollback (last line) seems to roll it back, at least the row
>> isn't in the table.
>>
>> Any ideas? The connection is not set to AUTOCOMMIT."
>>
>> You then included a sequence of log messages that ended with a "rollback".
>> Within that sequence was the INSERT to swd_auftrag. It seemed reasonable to
>> ask whether that INSERT rolled back also. That is if the intent of this
>> thread is to figure out why the INSERT was rolled back. If the thread has
>> changed to fixing ESQL/C logging then ignore the above.
> 
> The intention of my original post was to understand why the INSERT was
> rolled back. I do know this now: because I overlooked that the cancel of
> the transaction was done after the INSERT by CLOSE of a non open CURSOR.

Huh, this message:

https://www.postgresql.org/message-id/20200513101301.GC26063%40sh4-5.1blu.de

got delayed in the ether somewhere. It showed up recently, so now I see 
the issue.

> 
> We're fixing this now already by checking in pg_cursors if the CURSOR is
> still open before issue the CLOSE. I don't know how expensive this is,
> but it seems that there is no other option to check this.
> 
> The side step about fixing ESQL/C logging should be handled in another
> thread.
> 
> Thanks all for your help
> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com