Running the following python script twice throws this error:
peewee.ProgrammingError: relation "test_table" already exists
because the table does not get removed on .rollback(). Removing the inner transaction (with test_db.atomic()) works. Why does the inner transaction (which are just savepoints according to the documentation) not get rolled back?
from datetime import datetime
from peewee import Model, DateTimeField
from playhouse.postgres_ext import PostgresqlExtDatabase
"""
CREATE ROLE test WITH LOGIN;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test WITH OWNER test;
"""
CREDENTIALS = {
"database": "test",
"user": "test",
"password": None,
"host": "localhost",
"port": 5432,
"register_hstore": False,
}
test_db = PostgresqlExtDatabase(**CREDENTIALS)
test_db.connect()
test_db.set_autocommit(False)
test_db.begin() # start transaction
class TestTable(Model):
timestamp = DateTimeField(null=False, default=datetime(1970,1,1,0,0,0,))
class Meta:
db_table = "test_table"
database = test_db
with test_db.atomic():
TestTable.create_table()
TestTable.create()
test_db.rollback() # rollback transaction
print TestTable.get().timestamp
test_db.close()
Versions
peewee==2.8.3
psycopg2==2.6.2
PostgreSQL 9.5.1 on x86_64-apple-darwin15.3.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit
Depending on the database you're using, DDL (schema changes) may or may not be possible to rollback.
Postgresql and SQLite both appear to support rolling back DDL, but MySQL does not.
However, the Python driver for SQLite has a bug which causes it to emit a COMMIT when you issue DDL. The first patch was submitted in 2010: http://bugs.python.org/issue10740 .
Also take a look at pysqlite2, which is essentially the same as the standard-library sqlite3: