autocommit on for DDL
A.M.
While working on a database test with nose, I dug into sqlalchemy 0.6.6 until I found these lines:
class DDLElement(expression.Executable, expression.ClauseElement):
"""Base class for DDL expression constructs."""
_execution_options = expression.Executable.\
_execution_options.union({'autocommit':True})
In my nose test against postgresql, I emit a bunch of DDL, perform the test and then roll back the whole shebang which conveniently makes it look like I didn't touch the database. Obviously, the emitted commits were getting in my way, so I wrote this:
class NoCommitDDL(DDL):
def __init__(self,*args,**kw):
super(NoCommitDDL,self).__init__(*args,**kw)
unfrozen = dict(self._execution_options)
del unfrozen['autocommit']
self._execution_options = frozendict(unfrozen)
DDL = NoCommitDDL
I still feel like I am missing something though. I understand that PostgreSQL is perhaps one of few databases to allow for transaction-aware DDL, but why is a commit emitted for the DDL for any database, when the database makes it implied anyway?
Thanks.
Cheers,
M
Michael Bayer
Several databases offer transactional DDL including Postgresql. SQLA doesn't differentiate between these backends - it emits COMMIT after any statement that it considers to be an "autocommit" statement. "autocommit" means, "commit this statement after completion, if a transaction is not in progress".
From this it follows that if you'd like to emit several DDL statements in a transaction, the usage is no different for DDL expressions than for any other kind of DML statement (i.e insert/update/delete). Use connection.begin()/transaction.commit() as documented at:
http://www.sqlalchemy.org/docs/core/connections.html#using-transactions .
autocommit is described right after that:
http://www.sqlalchemy.org/docs/core/connections.html#understanding-autocommit
Also the public API for _execution_options is the execution_options() generative call:
A.M.
On Jan 26, 2011, at 5:45 PM, Michael Bayer wrote:
>
>
> From this it follows that if you'd like to emit several DDL statements in a transaction, the usage is no different for DDL expressions than for any other kind of DML statement (i.e insert/update/delete). Use connection.begin()/transaction.commit() as documented at:
>
> http://www.sqlalchemy.org/docs/core/connections.html#using-transactions .
>
> autocommit is described right after that:
>
> http://www.sqlalchemy.org/docs/core/connections.html#understanding-autocommit
>
> Also the public API for _execution_options is the execution_options() generative call:
>
> http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=execution_options#sqlalchemy.sql.expression.Executable.execution_options .
Thanks for the prodding- I figured out my bug. Here is sample code that demonstrates a little surprise.
First, this code that emits:
BEGIN (implicit)
SELECT 1
ROLLBACK
=========================
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL
engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))
DDL("SELECT 1").execute(bind=session)
session.rollback()
=========================
and here is the buggy code which emits:
SELECT 1
COMMIT
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL
engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))
DDL("SELECT 1").execute(bind=engine)
session.rollback()
=========================
Can you spot the difference? The DDL in the first code is bound to the session and the latter code mistakenly binds to the engine for execution resulting in two different execution paths. In hindsight, I guess it makes sense, but it certainly was not easy to find...
Cheers,
M
A.M.
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL,MetaData,Table
engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))
metadata = MetaData()
metadata.bind = engine
Table('test1',metadata)
Table('test2',metadata)
metadata.create_all()
2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select version()
2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select current_schema()
2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': u'test1'}
2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': u'test2'}
2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0
CREATE TABLE test1 (
)
2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,292 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0
CREATE TABLE test2 (
)
2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,294 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
Michael Bayer
On Jan 26, 2011, at 6:10 PM, A.M. wrote:
>
> Thanks for the prodding- I figured out my bug. Here is sample code that demonstrates a little surprise.
>
> First, this code that emits:
> BEGIN (implicit)
> SELECT 1
> ROLLBACK
>
> =========================
> from sqlalchemy.engine import create_engine
> from sqlalchemy.orm.session import sessionmaker
> from sqlalchemy.orm import scoped_session
> from sqlalchemy.schema import DDL
>
> engine = create_engine('postgresql://localhost/test',echo=True)
> session = scoped_session(sessionmaker(bind=engine))
>
> DDL("SELECT 1").execute(bind=session)
that's some surprise - a Session was never intended to be used as a "bind" and that argument on DDL.execute() is documented as expecting a Connection or Engine. Its kind of just coincidence it happens to call .execute() on the thing it gets and it "works". Dynamic typing FTW I guess
Michael Bayer
On Jan 26, 2011, at 6:32 PM, A.M. wrote:
> Well, I spoke too soon :( What is the mistake in the following sample code which causes the COMMITs to be emitted? Setting autocommit to either True or False emits the same SQL. I think this is a case of staring at the same code too long causing brain damage- thanks for your patience and help!
>
> from sqlalchemy.engine import create_engine
> from sqlalchemy.orm.session import sessionmaker
> from sqlalchemy.orm import scoped_session
> from sqlalchemy.schema import DDL,MetaData,Table
>
> engine = create_engine('postgresql://localhost/test',echo=True)
> session = scoped_session(sessionmaker(bind=engine))
> metadata = MetaData()
> metadata.bind = engine
>
> Table('test1',metadata)
> Table('test2',metadata)
> metadata.create_all()
metadata.create_all() looks at the "bind" attribute, then uses it to execute each DDL statement. The "bind" here is an engine so it uses connectionless execution. connectionless execution is usually autocommit as documented here: http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution
to emit create_all() in a transaction:
conn = engine.connect()
with conn.begin():
metadata.create_all(conn)
>
> 2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select version()
> 2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
> 2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select current_schema()
> 2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
> 2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
> 2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': u'test1'}
> 2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
> 2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': u'test2'}
> 2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0
> CREATE TABLE test1 (
> )
>
>
> 2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
> 2011-01-26 18:27:48,292 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
> 2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0
> CREATE TABLE test2 (
> )
>
>
> 2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
> 2011-01-26 18:27:48,294 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
A.M.
On Jan 26, 2011, at 7:47 PM, Michael Bayer wrote:
>
> On Jan 26, 2011, at 6:32 PM, A.M. wrote:
>
>> Well, I spoke too soon :( What is the mistake in the following sample code which causes the COMMITs to be emitted? Setting autocommit to either True or False emits the same SQL. I think this is a case of staring at the same code too long causing brain damage- thanks for your patience and help!
>>
>> from sqlalchemy.engine import create_engine
>> from sqlalchemy.orm.session import sessionmaker
>> from sqlalchemy.orm import scoped_session
>> from sqlalchemy.schema import DDL,MetaData,Table
>>
>> engine = create_engine('postgresql://localhost/test',echo=True)
>> session = scoped_session(sessionmaker(bind=engine))
>> metadata = MetaData()
>> metadata.bind = engine
>>
>> Table('test1',metadata)
>> Table('test2',metadata)
>> metadata.create_all()
>
> metadata.create_all() looks at the "bind" attribute, then uses it to execute each DDL statement. The "bind" here is an engine so it uses connectionless execution. connectionless execution is usually autocommit as documented here: http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution
>
> to emit create_all() in a transaction:
>
> conn = engine.connect()
> with conn.begin():
> metadata.create_all(conn)
Ugh- thanks for being patient with a noob- I had erroneously assumed that the creation of a session would assume responsibility for transaction management like the zope transaction handler. It makes sense now that the session is exclusively specific to ORM management- the section "Joining a Session into an External Transaction" helped to clear things up- there is indeed some interaction between connection and session transactions.
Cheers,
M
Daniel Holth
According to http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis
- PostgreSQL - yes
- MySQL - no; DDL causes an implicit commit
- Oracle Database 11g Release 2 and above - yes (something called edition-based redefinition)
- Older versions of Oracle - no; DDL causes an implicit commit
- SQL Server - yes
- Sybase Adaptive Server - yes
- DB2 - yes
- Informix - yes
- Firebird (Interbase) - yes