sqlite3 is non-transactional??

[Michele Simionato]

I know that CREATE queries are non-transactional in sqlite, as documented, but I finding something really strange in INSERT queries.

Consider this example:

$ cat example.py
import os
import shutil
import sqlite3

script0 = '''\
CREATE TABLE test (
   id SERIAL PRIMARY KEY,
   description TEXT NOT NULL);
'''

script1 = '''\
INSERT INTO test (id, description)
VALUES (1, 'First');
INSERT INTO test (id, description)
VALUES (2, 'Second');
'''

script2 = '''\
INSERT INTO test (id, description)
VALUES (1, 'Conflicting with the First');
'''


def main(test_dir):
    if os.path.exists(test_dir):
        shutil.rmtree(test_dir)
    os.mkdir(test_dir)
    path = os.path.join(test_dir, 'db.sqlite')
    conn = sqlite3.connect(path)
    conn.executescript(script0)  # this is committing implicitly
    try:
        conn.executescript(script1)  # this should not be committing
        conn.executescript(script2)  # this one has an error
    except:
        conn.rollback()
    curs = conn.execute('select * from test')
    for row in curs:  # no rows should have been inserted
        print(row)


if __name__ == '__main__':
    main('/tmp/test')

I am creating the test table in script0, populating it in script1, then trying to insert another row with a primary key violation. I would have expected the rollback to remove the rows inserted in script1, since they are part of the same transaction. Instead they are not removed!

Can somebody share some light on this? I discover the issue while porting some code from PostgreSQL to sqlite3, with Postgres doing the right thing and sqlite
failing.

I am puzzled,

                     Michele Simionato