Create database and transactional blocks strangeness
Wolodja Wentland
I am having a problem getting database creation on PostgreSQL done
correctly in an API that I am writing. I am using a svn checkout of SA trunk
from yesterday if that is important.
I have use the following code to create the database:
--- snip ---
...
try:
import psycopg2.extensions as e
ISOLATION_LEVEL_AUTOCOMMIT = e.ISOLATION_LEVEL_AUTOCOMMIT
ISOLATION_LEVEL_READ_COMMITTED = e.ISOLATION_LEVEL_READ_COMMITTED
ISOLATION_LEVEL_SERIALIZABLE = e.ISOLATION_LEVEL_SERIALIZABLE
del e
except ImportError, imp_err:
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_COMMITTED = 1
ISOLATION_LEVEL_SERIALIZABLE = 2
...
def __init__(....):
...
self._admin_engine = create_engine(
'%s+%s://%s:%s@%s/postgres'%(self.vendor, self.driver, self.user,
self.password, self.host))
self._AdminSession = sessionmaker(bind=self._admin_engine)
...
@property
def admin_session(self):
if self._admin_session is None:
self._admin_session = self._AdminSession()
return self._admin_session
...
def create(self):
"""Create this database"""
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
I can create the database just fine within the interpreter:
--- snip ---
>>> import mwdb
>>> db = mwdb.orm.database.PostgreSQLDatabase('psycopg2', 'babilen', 'PASSWORD', 'localhost', 'test', 'zh')
>>> db.all_databases()
['template1', 'template0', 'postgres']
>>> db.create()
>>> db.all_databases()
['template1', 'template0', 'postgres', 'test']
>>> db.drop()
>>> db.all_databases()
['template1', 'template0', 'postgres']
--- snip ---
But this fails miserably when the API is used within a program:
--- snip ---
dump_db = mwdb.orm.database.PostgreSQLDatabase(
self.options.pg_driver,
self.options.pg_username,
self.options.password,
self.options.pg_host,
self._database_name(dump_info),
dump_info['language'])
if self._database_name(dump_info) not in dump_db.all_databases():
LOG.info('Create database: %s' % self._database_name(dump_info))
dump_db.create()
--- snip ---
Traceback:
--- snip ---
Traceback (most recent call last):
File "/home/babilen/.virtualenvs/wp-import/bin/wp-import", line 185, in <module>
pg_importer.import_from_directory(ARGS[0])
File "/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py", line 147, in import_from_directory
self._import_dump(dump_info)
File "/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py", line 103, in _import_dump
dump_db.create()
File "/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/mwdb/orm/database.py", line 515, in create
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/orm/session.py", line 739, in execute
clause, params or {})
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 975, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1037, in _execute_clauseelement
return self.__execute_context(context)
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1060, in __execute_context
self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1122, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1120, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File "/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/default.py", line 181, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) CREATE DATABASE cannot run inside a transaction block
'CREATE DATABASE wp_zh_20091023' {}
--- snip ---
Do you have any idea why this is happening?
Is the .connection.connection.set_isolation_level() the right way to do this?
Why do I have to write connection.connection? This used to (?) be different.
Michael Bayer
>
> def create(self):
> """Create this database"""
> # set isolation level to AUTOCOMMIT
> # postgres can't CREATE databases within a transaction
> self._admin_engine.connect().connection.connection.set_isolation_level(
> ISOLATION_LEVEL_AUTOCOMMIT)
>
> self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute("CREATE DATABASE")
on the connection returned by self._admin_engine.connect().
Wolodja Wentland
You are right! I changed the code to this:
--- snip ---
def create(self):
"""Create this database"""
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
conn = self._admin_engine.connect()
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
conn.execute('CREATE DATABASE %s'%(self.name))
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
and it works like a charm.
But i still have some little questions...
* Is there an even better way to do this? ;-)
* Is it necessary to set the isolation level to the value it had
before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
explicitly setting that?
(I will change the code so it remembers the value of isolation_level
and use that instead of it to ISOLATION_LEVEL_READ_COMMITTED
explicitly)
* Why the .connection.connection ? I remember that I had to write just
one .connection in the past.
And one more word... This is the fastest mailing list I have ever used.
Thank you so much for reacting so fast on this ML, thank you very much
for SA and thanks for the solution to my problem!
have a great afternoon
Wolodja Wentland
Michael Bayer
create_engine() for PG supports an "isolation_level" parameter. But it
only does the four levels PG provides, it doesn't yet have a hook for
Psycopg2's "autocommit" mode.
>
> * Is it necessary to set the isolation level to the value it had
> before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
> connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
> explicitly setting that?
the way you have it, yes. Alternatively, you can call detach() on the
connection you returned and it will be de-associated from the connection
pool. Otherwise I would absolutely use try/finally above so that the
isolation level is returned to normal if the CREATE DATABASE fails.
>
> * Why the .connection.connection ? I remember that I had to write just
> one .connection in the past.
it should only need to be conn.connection. That returns a wrapper that
will pass all method calls down to the psycopg2 connection. would be
interested to know the error otherwise.
>
> And one more word... This is the fastest mailing list I have ever used.
> Thank you so much for reacting so fast on this ML, thank you very much
> for SA and thanks for the solution to my problem!
youre welcome
Wolodja Wentland
>
> Wolodja Wentland wrote:
> > * Is there an even better way to do this? ;-)
> create_engine() for PG supports an "isolation_level" parameter. But it
> only does the four levels PG provides, it doesn't yet have a hook for
> Psycopg2's "autocommit" mode.
I will keep that in mind for other problems, but it is not applicable
here.
> > * Is it necessary to set the isolation level to the value it had
> > before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
> > connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
> > explicitly setting that?
>
> the way you have it, yes. Alternatively, you can call detach() on the
> connection you returned and it will be de-associated from the connection
> pool. Otherwise I would absolutely use try/finally above so that the
> isolation level is returned to normal if the CREATE DATABASE fails.
Yes, I will definately use try: ... finally: here. What was I thinking?
I might just write a context manager for that.
> > * Why the .connection.connection ? I remember that I had to write just
> > one .connection in the past.
> it should only need to be conn.connection. That returns a wrapper that
> will pass all method calls down to the psycopg2 connection. would be
> interested to know the error otherwise.
There is no error actually. The time when I used just one connection I
just copied code from somewhere else. This evening I digged into the
connection object in the interpreter and found the .set_isolation_level
method only within the second .connection so I adapted my code to
reflect that.
So the first .connection is the SA one and the second the one provided
by psycopg2?
> > And one more word... This is the fastest mailing list I have ever used.
> > Thank you so much for reacting so fast on this ML, thank you very much
> > for SA and thanks for the solution to my problem!
> youre welcome
I mean it. This is amazing! No wonder #sqlalchemy is so dead. Which
really is a pity BTW. Are you on IRC as well?
Wolodja