Re: [sqlalchemy] Re: SQLite: Rolling back DDL requests
Hi Michael, I only noticed the activity on this thread today. On Sat, 2010-08-14 at 11:07 -0400, Michael Bayer wrote: reproduce the problem and it suggests the problem stems from some behaviour of transactions or of the engine.base.Connection class. I don't quite know what to make of it yet, but I think it shows that the effect of passing it through is being counteracted by something else. I ran this on Python 2.6.5 with SA 0.6.3 on Windows. Postgresql and MS-SQL support transactional DDL, and I use this feature all the time with SQLAlchemy which does nothing special to support them. That is out of the question. This seems only related to SQLite. In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. Per their documentation, some statements will fail if executed in a transaction - SQLAlchemy does not want to be involved in reorganizing how the DBAPI wants to approach things, emitting manual 'begin' and 'commit' strings, etc. Reading the pysqlite source, all that this magic isolation_level setting does is to emit an begin statement in front of UPDATE, DELETE, INSERT and REPLACE commands. And implicitly commit whenever any other non-select statement is executed (which includes SAVEPOINT commands!). I don't think it makes a huge difference for SQLAlchemy to rely on this insertion of begin statements. References: Emitting begin (via _pysqlite_connection_begin): http://code.google.com/p/pysqlite/source/browse/src/cursor.c?r=2.6.0#598 That's the only invocation to _pysqlite_connection_begin. After reading a bit more, I can see your point. Setting isolation_level to None actually disables the commit and rollback methods on cursor objects. Whee! So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. See http://docs.python.org/library/sqlite3.html#controlling-transactions Agreed. Greetings, Torsten -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: SQLite: Rolling back DDL requests
On Nov 2, 2010, at 5:15 PM, Torsten Landschoff wrote: In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. Per their documentation, some statements will fail if executed in a transaction - SQLAlchemy does not want to be involved in reorganizing how the DBAPI wants to approach things, emitting manual 'begin' and 'commit' strings, etc. Reading the pysqlite source, all that this magic isolation_level setting does is to emit an begin statement in front of UPDATE, DELETE, INSERT and REPLACE commands. And implicitly commit whenever any other non-select statement is executed (which includes SAVEPOINT commands!). I don't think it makes a huge difference for SQLAlchemy to rely on this insertion of begin statements. References: Emitting begin (via _pysqlite_connection_begin): http://code.google.com/p/pysqlite/source/browse/src/cursor.c?r=2.6.0#598 That's the only invocation to _pysqlite_connection_begin. After reading a bit more, I can see your point. Setting isolation_level to None actually disables the commit and rollback methods on cursor objects. Whee! we get occasional requests to work around Pysqlite's bugs regarding transactional behavior, since they seem to be extremely slow in fixing them. I'm very uncomfortable bypassing normal DBAPI behavior by default, but I would support a flag in 0.7 to the pysqlite dialect manual_transactions which sets isolation_level=None, emits BEGIN, ROLLBACK, COMMIT. That way users who insist on doing it this way are the only ones exposed to unforeseen issues, and they can contribute tests and patches to make it work better. Some initial testing showed that it definitely had issues, so it remains to be seen how much of an impact this has. As we come across new caveats with this manual mode and add further adjustments, we would in effect be reimplementing pysqlite's own feature. So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. See http://docs.python.org/library/sqlite3.html#controlling-transactions Agreed. Greetings, Torsten -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: SQLite: Rolling back DDL requests
On Aug 13, 2010, at 10:22 PM, Peter Hansen wrote:
On Jun 24, 1:23 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 24, 2010, at 12:35 PM, Torsten Landschoff wrote:
That's what I thought but it does not cure my problem.
e.raw_connect().isolation_levelis in fact None, but the rollback is not
done anyway. :-(
its passing it through.dont know what else we can do there
I ran into this myself today and after struggling for a few hours I
came across this thread. I then ended up creating a test case to
reproduce the problem and it suggests the problem stems from some
behaviour of transactions or of the engine.base.Connection class. I
don't quite know what to make of it yet, but I think it shows that the
effect of passing it through is being counteracted by something
else. I ran this on Python 2.6.5 with SA 0.6.3 on Windows.
Postgresql and MS-SQL support transactional DDL, and I use this feature all the
time with SQLAlchemy which does nothing special to support them.
In addition, isolation_level=None with pysqlite disables the DBAPIs entire
transactional system. SQLAlchemy relies upon this system to handle proper
transactional behavior. Per their documentation, some statements will fail if
executed in a transaction - SQLAlchemy does not want to be involved in
reorganizing how the DBAPI wants to approach things, emitting manual 'begin'
and 'commit' strings, etc.
So I consider this a pysqlite bug, and they should offer a mode by which there
is no implicit commit for CREATE TABLE.
See http://docs.python.org/library/sqlite3.html#controlling-transactions
'''Test showing unexpected behaviour with sqlalchemy and sqlite3 and
isolation_level settings. The test creates one table outside of
a transaction (to detect potential problems with the test not
executing
properly) then creates a second table inside a transaction which it
immediately rolls back.
test01() fails basically as expected, since the sqlite3 DBAPI layer
appears to need isolation_level==None to properly roll back DDL
statements.
test02() succeeds because isolation_level==None now. This test and
test01()
both use a connection from engine.raw_connection(), which is a
sqlalchemy.pool._ConnectionFairy() object.
test03() tries again with isolation_level==None but using a
transaction
created from a connection returned by engine.connect(), which is a
sqlalchemy.engine.base.Connection() object. This test fails in spite
of the isolation_level setting.
'''
import unittest
from sqlalchemy import create_engine
DBPATH = 'sqlite://'
DDL = 'create table %s (id integer primary key)'
class TestCase(unittest.TestCase):
def setup(self, **kwargs):
self.engine = create_engine(DBPATH, **kwargs)
self.engine.execute(DDL % 'foo')
def rollback_raw(self):
conn = self.engine.raw_connection()
try:
conn.execute('begin')
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
conn.rollback()
def rollback_txn(self):
conn = self.engine.connect()
try:
txn = conn.begin()
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
txn.rollback()
def get_table_names(self):
conn = self.engine.raw_connection()
return [x[0] for x in conn.execute('select name from
sqlite_master')]
def test01(self):
'''use engine with default isolation_level'''
self.setup()
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test02(self):
'''use raw_connection with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test03(self):
'''use transaction with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_txn()
self.assertEqual(['foo'], self.get_table_names())
if __name__ == '__main__':
unittest.main()
--
Peter Hansen
Engenuity Corporation
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: SQLite: Rolling back DDL requests
On Aug 14, 11:07 am, Michael Bayer mike...@zzzcomputing.com wrote: In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. Michael, I don't doubt that you're right, but the puzzling thing (for me and, I think, Torsen) is that (if you set echo='debug') you see a BEGIN and ROLLBACK statement apparently being issued through the DBAPI layer, and if there's an implicit COMMIT going on we don't actually see it and don't understand why isolation_level=None isn't preventing it as it apparently does in the other case (test02). I suspect this is because with the logging on, we aren't actually seeing the operations performed, but merely sqlalchemy's report of what it is about to ask for. If the DBAPI is doing something under the covers, we don't know what and therefore can't find a workaround. To be clear, in test02 I believe we're effectively telling sqlite3 BEGIN; CREATE TABLE ...; ROLLBACK, and in test03 (with the same isolation_level setting) that's exactly what the logging reports is happening, yet the behaviour is different. (I was hoping to put some debugging in the DBAPI layer but unfortunately that's a C extension so harder to deal with. Maybe there's also some feature in sqlite3 itself (not the Python module) which can be configured for debug purposes to show what's really happening here.) -- Peter Hansen Engenuity Corporation -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: SQLite: Rolling back DDL requests
On Aug 14, 2010, at 12:04 PM, Peter Hansen wrote: On Aug 14, 11:07 am, Michael Bayer mike...@zzzcomputing.com wrote: In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. Michael, I don't doubt that you're right, but the puzzling thing (for me and, I think, Torsen) is that (if you set echo='debug') you see a BEGIN and ROLLBACK statement apparently being issued through the DBAPI layer, The BEGIN is just a logging message emitted by SQLAlchemy. We don't ever send that string, and DBAPI has no begin() - this is a critical aspect of DBAPI that confuses everyone - it doesn't support ad-hoc transaction creation. There always a transaction going on as far as DBAPI is concerned, its just that the transaction might not matter if the connection has disabled transactions. ROLLBACK is a message we emit when we call connection.rollback(). and if there's an implicit COMMIT going on we don't actually see according to sqlite3's docs, there is, we of course can't see it unless you got sqlite3 to log its interactions. it and don't understand why isolation_level=None isn't preventing it as it apparently does in the other case (test02). test02 would appear to succeed because you are manipulating the transaction directly by emitting 'begin'. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: SQLite: Rolling back DDL requests
On Jun 24, 1:23 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 24, 2010, at 12:35 PM, Torsten Landschoff wrote:
That's what I thought but it does not cure my problem.
e.raw_connect().isolation_levelis in fact None, but the rollback is not
done anyway. :-(
its passing it through.dont know what else we can do there
I ran into this myself today and after struggling for a few hours I
came across this thread. I then ended up creating a test case to
reproduce the problem and it suggests the problem stems from some
behaviour of transactions or of the engine.base.Connection class. I
don't quite know what to make of it yet, but I think it shows that the
effect of passing it through is being counteracted by something
else. I ran this on Python 2.6.5 with SA 0.6.3 on Windows.
'''Test showing unexpected behaviour with sqlalchemy and sqlite3 and
isolation_level settings. The test creates one table outside of
a transaction (to detect potential problems with the test not
executing
properly) then creates a second table inside a transaction which it
immediately rolls back.
test01() fails basically as expected, since the sqlite3 DBAPI layer
appears to need isolation_level==None to properly roll back DDL
statements.
test02() succeeds because isolation_level==None now. This test and
test01()
both use a connection from engine.raw_connection(), which is a
sqlalchemy.pool._ConnectionFairy() object.
test03() tries again with isolation_level==None but using a
transaction
created from a connection returned by engine.connect(), which is a
sqlalchemy.engine.base.Connection() object. This test fails in spite
of the isolation_level setting.
'''
import unittest
from sqlalchemy import create_engine
DBPATH = 'sqlite://'
DDL = 'create table %s (id integer primary key)'
class TestCase(unittest.TestCase):
def setup(self, **kwargs):
self.engine = create_engine(DBPATH, **kwargs)
self.engine.execute(DDL % 'foo')
def rollback_raw(self):
conn = self.engine.raw_connection()
try:
conn.execute('begin')
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
conn.rollback()
def rollback_txn(self):
conn = self.engine.connect()
try:
txn = conn.begin()
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
txn.rollback()
def get_table_names(self):
conn = self.engine.raw_connection()
return [x[0] for x in conn.execute('select name from
sqlite_master')]
def test01(self):
'''use engine with default isolation_level'''
self.setup()
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test02(self):
'''use raw_connection with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test03(self):
'''use transaction with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_txn()
self.assertEqual(['foo'], self.get_table_names())
if __name__ == '__main__':
unittest.main()
--
Peter Hansen
Engenuity Corporation
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
