AUTOCOMMIT OFF ignored by tm

[Paul White]

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
&

[G Jones]

My take on this: The error rolls back the failing statement, \nocontinue is set so processing terminates, with II_TM_EXIT_ON_ERROR not set to 'rollback' an implicit commit is issued as tm exits.
GJ

[Steve]

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

[Paul White]

On Tuesday, February 14, 2023 at 7:19:47 PM UTC+10, G Jones wrote:
> My take on this: The error rolls back the failing statement, \nocontinue is set so processing terminates, with II_TM_EXIT_ON_ERROR not set to 'rollback' an implicit commit is issued as tm exits.
> GJ

Thanks Geraint,
I will be looking to change the default behaviour to align with other mainstream DBMS scripting languages. It would be great if the behaviour matched the documentation.

In my opinion, an explicit transaction which fails needs to rollback. In some circumstances I choose AUTOCOMMIT ON expecting the last completed statement to have automatically committed.
More on this when I have time to play...
Paul

[Paul White]

Hi Steve

Replying to your deleted mail :-)
I think that appendix has been around 20 years or so. I forgotten it existed. Ingres R3?

Yes, the missing column was intentional to demonstrate the problem. There needs to be an acceptable method to abort and automatically roll back a batch SQL - normal behaviour in other mainstream DBMS scripted languages.

I will check back after some more research.

Paul