autorollback on error
phil2
✭
Hello
I just realized that there is no transaction level autorollback on error. Am I wrong?
Here is an example of what I'm saying about:
create local temp table t (i integer) order by i unsegmented all nodes;
set session autocommit = off;
begin;
insert into t values(1);
insert into t values('a');
insert into t values(2);
commit;
I expect that the whole begin-commit block is forced to rollback as it contains error in second insert (like postgres do for example). I figured out for now that I have to check every statement in transaction for error and invoke rollback explicitly if error happened.
So, is there any autorollback option in vertica? All I can find is ON_ERROR_STOP for vsql. Maybe I missed something...
0
Comments
Hi,
In Vertica a transaction will not rollback any DML (i.e. INSERT, UPDATE and DELETE) statements that succeed! That would be crazy! Imagine if you type a 100 inserts and then make a typo on the 101st. Vertica won't roll back those 100 successful inserts. In this respect, we do the same as databases like Oracle and MySQL. In PostGreSQL you have to enable ON_ERROR_ROLLBACK to avoid that annoying behavior. In Vertica, there is no need for that setting.
Example:
dbadmin=> create local temp table t (i integer) ON COMMIT PRESERVE ROWS order by i unsegmented all nodes; CREATE TABLE dbadmin=> insert into t values(1); OUTPUT -------- 1 (1 row) dbadmin=> insert into t values('a'); ERROR 3681: Invalid input syntax for integer: "a" dbadmin=> insert into t values(2); OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT dbadmin=> select * from t; i --- 1 2 (2 rows)Note that ON_ERROR_ROLLBACK setting only helps when executing a SQL script. If using Vertica interactively, enabling ON_ERROR_ROLLBACK, does not roll back any successful previous DML:
Example:
dbadmin=> DROP TABLE t; DROP TABLE dbadmin=> create local temp table t (i integer) ON COMMIT PRESERVE ROWS order by i unsegmented all nodes; CREATE TABLE dbadmin=> \set ON_ERROR_STOP on dbadmin=> insert into t values(1); OUTPUT -------- 1 (1 row) dbadmin=> insert into t values('a'); ERROR 3681: Invalid input syntax for integer: "a" dbadmin=> insert into t values(2); OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT dbadmin=> select * from t; i --- 1 2 (2 rows)Well, I dont agree with crazyness
Everything you pointed out is quite reasonable since there is no BEGIN in your snippet.
For me behaviour with commiting everything that gone well and rolling back everything that did not go well is crasy since I explicitly started a transaction block with BEGIN. So I expect that block BEGIN - COMMIT is a single atomic block with "all or nothing" behaviour. Otherwise BEGIN is useless. So for me it is clear now that BEGIN is a waste statement with no effect in vertica.
For example in postgresql I have "current transaction is aborted, commands ignored until end of transaction block" error and transaction block goes aborted and automatically rolled back. I'm not sure if it is a standard (I bet it is not) but it gives you an opportunity to write a very very clear and staight forward code as you dont have to manually check every statement output and explicitly call rollback if anything goes wrong. I disapointed so much now
The point is when issueing next statement in transaction block I can rely on previous statement to be successfully executed so I can maintain business logic in SQL. And if an error happens in a middle of transaction block all the following business logic goes wrong and it will bring harm if executed. It seems extrimly valid for developing application that transaction goes into erroneous state and get aborted to ensure it does not do anything wrong after an error.
So it seems like BEGIN has no effect in vertica. Is it right?
Hi,
Yeah, a BEGIN is not necessary. So even if I add a BEGIN statement, you get the same results...
dbadmin=> create local temp table t (i integer) ON COMMIT PRESERVE ROWS order by i unsegmented all nodes; CREATE TABLE dbadmin=> BEGIN; BEGIN dbadmin=> INSERT INTO t SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO t SELECT 'a'; ERROR 3681: Invalid input syntax for integer: "a" dbadmin=> INSERT INTO t SELECT 2; OUTPUT -------- 1 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> SELECT * FROM t; i --- 1 2 (2 rows)You could use the ON_ERROR_STOP setting if you set up transactions in SQL scripts:
[dbadmin@s18384357 ~]$ cat test.sql \set AUTOCOMMIT off \set ON_ERROR_STOP on create table t (i integer) order by i unsegmented all nodes; BEGIN; INSERT INTO t SELECT 1; INSERT INTO t SELECT 'a'; INSERT INTO t SELECT 2; COMMIT; [dbadmin@s18384357 ~]$ vsql -f test.sql CREATE TABLE BEGIN OUTPUT -------- 1 (1 row) vsql:test.sql:6: ERROR 3681: Invalid input syntax for integer: "a" [dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM t;" i --- (0 rows)No rows were committed...
The following may be helpful
bvandiver=> create table foo (a int); CREATE TABLE bvandiver=> insert into foo values (1); OUTPUT -------- 1 (1 row) bvandiver=> insert into foo select * from foo where 100/(a-1) > 1; ERROR 3117: Division by zero bvandiver=> select * from foo; a --- 1 (1 row) bvandiver=> select set_vertica_options('basic','TransactionLevelRollback'); set_vertica_options -------------------------------------------------------------------------- Basic Vertica Options -------------------- TRANSACTION_LEVEL_ROLLBACK (1 row) bvandiver=> insert into foo select * from foo where 100/(a-1) > 1; ROLLBACK 3117: Division by zero bvandiver=> select * from foo; a --- (0 rows)I think it may not apply to syntax errors specifically, so you might need a more complicated reproducer.
@Ben_Vandiver: I see the same behavior after setting the Vertica option:
dbadmin=> create table foo (a int); CREATE TABLE dbadmin=> select set_vertica_options('basic','TransactionLevelRollback'); set_vertica_options -------------------------------------------------------------------------- Basic Vertica Options -------------------- TRANSACTION_LEVEL_ROLLBACK (1 row) dbadmin=> insert into foo select 1; OUTPUT -------- 1 (1 row) dbadmin=> insert into foo select 'a'; ERROR 3681: Invalid input syntax for integer: "a" dbadmin=> insert into foo select 2; OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT dbadmin=> select * from foo; a --- 1 2 (2 rows)I said it might not apply to syntax errors, which is what you have here.
Ok, gotcha!