[Info-ingres] AUTOCOMMIT OFF ignored by tm

[Steve]

On Tuesday, February 14, 2023 at 12:46:38 AM UTC+11, Paul White wrote:
> Can someone give me a shake and point out the obvious error? 
> 
> An ad hoc data maintenance script I am developing in the test environment failed but committed an incomplete transaction instead of rolling back. It is problematic because reversing failed transaction at this point is impossible without restoring from backup. 
> 
> I have been developing/testing in this way for years and not come across this feature. ie run some data updates, view results and then roll back to refine the data fix. Did I miss something in Ingres 101? 
> 
> The behaviour is unchanged if I remove the BEGIN TRANSACTION. I am not using II_TM_SWITCH OR II_TM_ONERROR. Behaviour seems to be the same in Ingres 10.2 and ActianX 11.2. It is inconsistent with OpenROAD. Also inconsistent with other DBMSs such as mariadb, mysql and MSSQL. 
> 
> From OpenSQL guide: 
> 
> AUTOCOMMIT OFF 
> Causes an explicit COMMIT statement to be required to commit a transaction. 
> 
> From SQLRef 
> 
> SET AUTOCOMMIT OFF, the default, means an explicit COMMIT or ROLLBACK statement 
> or terminating the session is required to terminate a transaction 
> 
> Here is a test script with a rollback. There is an intentional error. 
> 
> $ cat a.sql 
> set autocommit off 
> \p\g 
> \nocontinue 
> \p\g 
> create table pw1 ( 
> v varchar(10) not null, 
> i integer not null, 
> d ingresdate not null 
> ) 
> \p\g 
> 
> insert into pw1 values 
> ('a',1,'01/01/2023' ), 
> ('b',2,'01/01/2023' ), 
> ('c',3,'02/01/2023' ), 
> ('d',4,'03/01/2023' ) 
> \p\g 
> 
> select * from pw1 \p\g 
> commit \p\g 
> 
> BEGIN TRANSACTION 
> \p\g 
> update pw1 set v = 'z' where v = 'a' \p\g 
> update pw1 set v = 'y' where v = 'b' \p\g 
> update pw1 set v = 'x' where v = 'c' \p\g 
> update pw1 set v = 'w' where v1 = 'd' \p\g 
> select * from pw1 \p\g 
> rollback \p\g 
> 
> 
> $ sql tmwp < a.sql 
> INGRES TERMINAL MONITOR Copyright 2021 Actian Corporation 
> Actian X Linux Version II 11.2.0 (a64.lnx/100) login 
> Fri Feb 10 12:49:06 2023 
> Enter \g to execute commands, "help help\g" for general help, 
> "help tm\g" for terminal monitor help, \q to quit 
> 
> continue 
> * * /* SQL Startup File */ 
> set autocommit off 
> Executing . . . 
> 
> continue 
> * * 
> Executing . . . 
> 
> continue 
> * * * * * * create table pw1 ( 
> v varchar(10) not null, 
> i integer not null, 
> d ingresdate not null 
> ) 
> Executing . . . 
> 
> continue 
> * * * * * * * 
> insert into pw1 values 
> ('a',1,'01/01/2023' ), 
> ('b',2,'01/01/2023' ), 
> ('c',3,'02/01/2023' ), 
> ('d',4,'03/01/2023' ) 
> Executing . . . 
> 
> (4 rows) 
> continue 
> * * 
> select * from pw1 
> Executing . . . 
> 
> │v │i │d │ 
> │a │ 1│01/01/23 │ 
> │b │ 2│01/01/23 │ 
> │c │ 3│02/01/23 │ 
> │d │ 4│03/01/23 │ 
> (4 rows) 
> continue 
> * commit 
> Executing . . . 
> 
> continue 
> * * * 
> BEGIN TRANSACTION 
> Executing . . . 
> 
> continue 
> * update pw1 set v = 'z' where v = 'a' 
> Executing . . . 
> 
> (1 row) 
> continue 
> * update pw1 set v = 'y' where v = 'b' 
> Executing . . . 
> 
> (1 row) 
> continue 
> * update pw1 set v = 'x' where v = 'c' 
> Executing . . . 
> 
> (1 row) 
> continue 
> * update pw1 set v = 'w' where v1 = 'd' 
> Executing . . . 
> 
> E_US0836 line 1, Column 'v1' not found in any specified table. 
> (Fri Feb 10 12:49:07 2023) 
> 
> - Terminated by Errors 
> Your SQL statement(s) have been committed. 
> 
> Actian X Version II 11.2.0 (a64.lnx/100) logout 
> Fri Feb 10 12:49:07 2023 
> 
> $ sql tmwp < a1.sql 
> INGRES TERMINAL MONITOR Copyright 2021 Actian Corporation 
> Actian X Linux Version II 11.2.0 (a64.lnx/100) login 
> Fri Feb 10 12:49:16 2023 
> Enter \g to execute commands, "help help\g" for general help, 
> "help tm\g" for terminal monitor help, \q to quit 
> 
> continue 
> * * /* SQL Startup File */ 
> set autocommit off 
> Executing . . . 
> 
> continue 
> * * * 
> 
> select * from pw1 
> Executing . . . 
> 
> 
> │v │i │d │ 
> │z │ 1│01/01/23 │ 
> │y │ 2│01/01/23 │ 
> │x │ 3│02/01/23 │ 
> │d │ 4│03/01/23 │ 
> (4 rows) 
> continue 
> * commit 
> Executing . . . 
> 
> continue 
> * * * 
> Actian X Version II 11.2.0 (a64.lnx/100) logout 
> Fri Feb 10 12:49:16 2023 
> $ 
> 
> 
> 
> Paul 
> &
Hi Paul

I posted an answer, but subsequently deleted it as it was wrong. GJ's answer is correct.

> continue 
> * update pw1 set v = 'w' where v1 = 'd' 
> Executing . . . 
> 
> E_US0836 line 1, Column 'v1' not found in any specified table. 
> (Fri Feb 10 12:49:07 2023) 

The above update failed because there was no such column, however this doesn't automatically cause a rollback (or commit for that matter).  

> - Terminated by Errors 
> Your SQL statement(s) have been committed. 

The above indicates Terminal Monitor exited and a commit was issued (for reasons GJ mentioned), resulting in the three successful update operations being committed.

Steve