In SQLite, wrong exception on trigger rollbacks

[Former user]

If a trigger in SQLite raises ROLLBACK, the exception that is propogated to the application is always "OperationalError: SQL logic error or missing database". Here is an example that illustrates the problem (I have attached this file):

from sqlalchemy import *
import sqlite3
DB_NAME = 'test.db3'

def set_up_database():
    **
    Make a simple table with one (primary key) column, and a trigger that 
    prevents deletion from the table.  Also puts a row into the table.
    **
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.executescript(**
    DROP TABLE IF EXISTS test_table;
    CREATE TABLE test_table(name VARCHAR(10) NOT NULL PRIMARY KEY);
    CREATE TRIGGER delete_test_table
        BEFORE DELETE ON "test_table"
        FOR EACH ROW BEGIN
          SELECT RAISE(ROLLBACK, 'Cannot delete from table "test_table"');
        END;    
    INSERT INTO test_table(name) VALUES('first');
    **)
    cursor.close()
    conn.close()

def test_direct_insert():
    **
    Attempt to duplicate a primary key using direct sqlite3 access.  The
    exception message should indicate the problem, and it does:
        test_direct() insert duplicate error: column name is not unique
    **
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute('INSERT INTO test_table(name) VALUES(?)', ('first',))
    except Exception, e:
        print 'test_direct() insert duplicate error: %s' % e
    finally:
        cursor.close()
        conn.close()

def test_direct_delete():
    **
    Attempt to delete a row using direct sqlite3 access, but the table's
    trigger prevents all deletion.  The exception message should indicate 
    the problem, and it does:
        test_direct() deletion error: Cannot delete from table "test_table"
    **
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        cursor.execute('DELETE FROM test_table WHERE name=?', ('first',))
    except Exception, e:
        print 'test_direct() deletion error: %s' % e
    finally:
        cursor.close()
        conn.close()

def test_sqlalchemy_insert():        
    **
    Attempt to duplicate a primary key through SQLAlchemy.  The
    exception message should indicate the problem, and it does:
        test_sqlalchemy() insert duplicate error: (IntegrityError) column name is
        not unique u'INSERT INTO test_table (name) VALUES (?)' ['first']('first')    
    **
    db = create_engine('sqlite:///%s' % DB_NAME)
    metadata = MetaData(db)
    table = Table('test_table', metadata, autoload=True)
    #metadata.engine.echo = True
    insert_statement = table.insert()
    try:
        insert_statement.execute(name='first')
    except Exception, e:
        print 'test_sqlalchemy() insert duplicate error: %s' % e

def test_sqlalchemy_delete():        
    **
    Attempt to delete a row through SQLAlchemy, but the table's trigger 
    prevents all deletion.  The exception message should indicate the 
    problem, BUT it displays instead:
        test_sqlalchemy() deletion error: (OperationalError) SQL logic error
        or missing database None None
    **
    db = create_engine('sqlite:///%s' % DB_NAME)
    metadata = MetaData(db)
    table = Table('test_table', metadata, autoload=True)
    #metadata.engine.echo = True
    delete_statement = table.delete()
    try:
        delete_statement.execute(name='first')
    except Exception, e:
        print 'test_sqlalchemy() deletion error: %s' % e

if __name__ == '__main__':
    set_up_database()
    test_direct_insert() # Works fine.
    test_direct_delete() # Works fine.
    test_sqlalchemy_insert() # Works fine.
    test_sqlalchemy_delete() # Doesn't work correctly.

I have tried to find the source of the problem, and it looks like it might be in sqlalchemy.engine.default.do_rollback(). Apparently, the connection.rollback() statement is raising the OperationalError. Note that the error happens only for triggers, not other constraints.

For now, I trap the OperationalError in do_rollback() and toss it, so the original IntegrityError gets propogated to my application. However, I'm not sure whether there is a more-serious underlying problem.

[Mike Bayer]

theres two real issues I can identify here:

1. your trigger is issuing a ROLLBACK when it probably should not be

2. pysqlite2 (i.e. sqlite3 module) and/or SQLite is buggy when dealing with rollbacks in tandem with a trigger-induced ROLLBACK.

Lets look at some examples; all entirely sqlite3/py2.4 style. This example produces the identical result of "SQL Logic Error":

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        try:
            cursor.execute('DELETE FROM test_table WHERE name=?', ('first',))
        except Exception, e:
            print 'test_direct() deletion error: %s' % e
            conn.rollback()
    finally:
        cursor.close()
        conn.close()

however, this code, which also raises a failure condition, does not produce the error:

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        try:
            cursor.execute("INSERT INTO test_table VALUES ('ed')")
            cursor.execute("INSERT INTO test_table VALUES ('ed')")
        except Exception, e:
            print 'test_direct() deletion error: %s' % e
            conn.rollback()
    finally:
        cursor.close()
        conn.close()

it instead produces the correct error "column name is not unique", and the rollback succeeds, because the failure of the INSERT operation does not assume an automatic ROLLBACK. If you continue to INSERT rows after the second INSERT of 'ed' fails, the transaction continues, and committing the transaction inserts whatever else was actually inserted.

So in the first place, I would argue that to remain consistent with normal database semantics and since your trigger does not BEGIN its own transaction, nor should it be issuing a ROLLBACK; changing the trigger to "SELECT RAISE(FAIL, 'Cannot delete from table "test_table"');" works.

But. for number 2, we still have an issue. which is that you should be able to call rollback() repeatedly. The below code works:

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    try:
        try:
            cursor.execute("INSERT INTO test_table VALUES ('ed')")
            cursor.execute("INSERT INTO test_table VALUES ('ed')")
        except Exception, e:
            print 'test_direct() deletion error: %s' % e
            conn.rollback()
            conn.rollback()
    finally:
        cursor.close()
        conn.close()

you can call rollback() as many times as you want. So I would argue that even when your trigger does issue a rollback, pysqlite should still be able to have a rollback() call; I think this should be reported as a bug with Pysqlite.

The only solution on the SA side would be to silently (or with a warning) not raise an exception for a rollback(), which worries me; i think if a rollback() fails, that should be seen as an application failure. if you truly cant change your trigger to issue FAIL instead of ROLLBACK, let me know and I'll look into adding an option of some kind to work around this issue.

[Former user]

Using FAIL instead of ROLLBACK makes sense, so that's what I'll do.

Sorry for the trouble and thank you for the quick response and excellent explanation.

[Mike Bayer]

glad that works for you; marking as fixed but this is a good one for people to come across in searches.