Peewee: Outer transaction does not roll back inner transaction (savepoints)
<p>Running the following python script twice throws this error:</p>

<pre><code>peewee.ProgrammingError: relation "test_table" already exists
</code></pre>

<p>because the table does not get removed on <em>.rollback()</em>. Removing the inner transaction (<em>with test_db.atomic()</em>) works. Why does the inner transaction (which are just savepoints according to the <a href="http://docs.peewee-orm.com/en/latest/peewee/transactions.html#nesting-transactions" rel="nofollow">documentation</a>) not get rolled back?</p>

<pre><code>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()
</code></pre>

<p><strong>Versions</strong></p>

<pre><code>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
</code></pre>

<p>Depending on the database you're using, DDL (schema changes) may or may not be possible to rollback.</p>

<p>Postgresql and SQLite both appear to support rolling back DDL, but MySQL does not.</p>

<p>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: <a href="http://bugs.python.org/issue10740" rel="nofollow">http://bugs.python.org/issue10740</a> .</p>

<p>Also take a look at pysqlite2, which is essentially the same as the standard-library sqlite3: </p>

<p><a href="https://github.com/ghaering/pysqlite/blob/ca5dc55b462a7e67072205bb1a9a0f62a7c6efc4/src/cursor.c#L537-L547" rel="nofollow">https://github.com/ghaering/pysqlite/blob/ca5dc55b462a7e67072205bb1a9a0f62a7c6efc4/src/cursor.c#L537-L547</a></p>

python transactions peewee