From Newsgroup: comp.databases.ingres

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 . . .
roev roei roed roe
roea roe 1roe01/01/23 roe
roeb roe 2roe01/01/23 roe
roec roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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 . . .
roev roei roed roe
roez roe 1roe01/01/23 roe
roey roe 2roe01/01/23 roe
roex roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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
&
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

On Tuesday, February 14, 2023 at 12:46:38rC>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 . . .

roev roei roed roe
roea roe 1roe01/01/23 roe
roeb roe 2roe01/01/23 roe
roec roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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 . . .

roev roei roed roe
roez roe 1roe01/01/23 roe
roey roe 2roe01/01/23 roe
roex roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

On Tuesday, February 14, 2023 at 12:46:38rC>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 . . .

roev roei roed roe
roea roe 1roe01/01/23 roe
roeb roe 2roe01/01/23 roe
roec roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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 . . .

roev roei roed roe
roez roe 1roe01/01/23 roe
roey roe 2roe01/01/23 roe
roex roe 3roe02/01/23 roe
roed roe 4roe03/01/23 roe
(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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

On Tuesday, February 14, 2023 at 7:19:47rC>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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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
--- Synchronet 3.21b-Linux NewsLink 1.2