autorollback on error

phil2phil2 Registered User

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...

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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)
    
  • phil2phil2 Registered User

    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?

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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:

    [[email protected] ~]$ 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;
    
    [[email protected] ~]$ vsql -f test.sql
    CREATE TABLE
    BEGIN
     OUTPUT
    --------
          1
    (1 row)
    
    vsql:test.sql:6: ERROR 3681:  Invalid input syntax for integer: "a"
    
    [[email protected] ~]$ vsql -c "SELECT * FROM t;"
     i
    ---
    (0 rows)
    

    No rows were committed...

  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert
    edited May 11

    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.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    @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)
    
  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert

    I said it might not apply to syntax errors, which is what you have here.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Ok, gotcha! :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file