autorollback on error

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!