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
&

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 have never used BEGIN TRANSACTION (at least not in Ingres), I actually had to look it up! I guess I'm still a youngster in Ingres-years.

In the Ingres docs I notice BEGIN TRANSACTION, END TRANSACTION and ABORT are listed as old statements. Their new equivalents are COMMIT and ROLLBACK, noting there is no new equivalent statement for BEGIN TRANSACTION.

Your code is using BEGIN TRANSACTION, COMMIT and ROLLBACK. Could the problem be the mixing old statements with new ones?

https://docs.actian.com/ingres/11.0/index.html#page/SQLRef/Substitute_Statements.htm

Steve

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)