open session blocks metadata create_all method

[Faheem Mitha]

Hi,

When calling create_all on a metadata instance after a session has alrady
been opened causes the create_all to hang, I assume because the session is
blocking the create_all. Is there some way to get create_all to use the
existing session, or any other graceful way around this? Thanks.

I guess another option is to close and then reopen the session after the
create_all has been called, but I'd prefer not to do that if possible.

Regards, Faheem.

##########################################################################

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import MetaData
meta = MetaData()

def make_foo(meta):
foo = Table(
'foo', meta,
Column('id', Integer, nullable=False, primary_key=True),
)
return foo

def make_bar(meta):
bar = Table(
'bar', meta,
Column('id', Integer, ForeignKey('foo.id', onupdate='CASCADE',
ondelete='CASCADE'), nullable=False, primary_key=True),
)
return bar

dbuser =
password =
dbname =
dbstring = "postgres://%s:%s@localhost:5432/%s"%(dbuser, password, dbname)
from sqlalchemy import create_engine
db = create_engine(dbstring)
meta.bind = db
db.echo = 'debug'
make_foo(meta)
meta.create_all()
Session = sessionmaker()
session = Session(bind=db)
session.execute("select * from foo;")
make_bar(meta)
meta.create_all()

[Faheem Mitha]

On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha <fah...@email.unc.edu> wrote:
>
> Hi,

> When calling create_all on a metadata instance after a session has
> alrady been opened causes the create_all to hang, I assume because
> the session is blocking the create_all. Is there some way to get
> create_all to use the existing session, or any other graceful way
> around this? Thanks.

> I guess another option is to close and then reopen the session after
> the create_all has been called, but I'd prefer not to do that if
> possible.

Puting a session.close() before the create_all fixes the problem. I
assume this means that create_all doesn't work in the middle of a
transaction, or something like that?
Regards, Faheem

[Lance Edgar]

I can't speak to the underlying mechanics of create_all(), but calling session.close() prior to create_all() would work, as you say.  Another option would be to simply not use a session, but instead just a *connection*.  Sessions are specific to the ORM which, according to the code you posted, you are not using.  So if you really just need to make a SELECT call to a table, then instead of creating a session and calling .execute() on it, you could instead do this:

db = create_engine(dbstring)
meta.bind = db
db.echo = 'debug'
make_foo(meta)
meta.create_all()
db.connect().execute("select * from foo;")
make_bar(meta)
meta.create_all()

Lance

[Faheem Mitha]

Hi Lance,

On Wed, 28 Jul 2010 06:45:30 -0500, Lance Edgar <lance...@gmail.com> wrote:
> --=-dKyzuPx4woj1H0B5IT48
> Content-Type: text/plain; charset=ISO-8859-1

The example was just an example. After going back and forth a bit,
I've finally standardized on session as the thing to more around in my
application. The db.connect thing works, I think, because autocommit
is the default for connect.

I'd like to hear an explanation of why create_all is blocked here. I
periodically have my scripts hang for no apparent reason, almost
always because the db is blocking something, so would like to become
more educated on this issue.

Regards, Faheem

[Lance Edgar]

Are your sessions contextual (created with scoped_session()) ?  Not sure what kind of project you're working on (i.e. if you need a contextual session or not), but I use sessions and also have to create tables on the fly occasionally...but my sessions aren't contextual and I always create and close them immediately when finished.  See "When do I make a Session ?" at http://www.sqlalchemy.org/docs/session.html#frequently-asked-questions

But I also apologize if I'm telling you nothing new, certainly don't mean to insult.  Just trying to help.

I assume Michael will have to explain the blocking thing, but FWIW I couldn't reproduce that issue while using SQLite or MySQL, so it might have just as much to do with PostgreSQL as anything else...whether that means its SA dialect or the server configuration itself I couldn't say.

Lance

[Faheem Mitha]

In gmane.comp.python.sqlalchemy.user, you wrote:
> --=-QALfoiit8f0M60C7Zms+

I'm not curently using scoped_session, but plan to be in a bit,
because I'll try doing some multi-threading. Though I honestly have
only the foggiest idea what scoped_sesion is doing. I could close and
open the sessions, sure, but it seems easier just to pass around one
session between functions within my application, which reads and
writes stuff to one specific set of tables in one pass. Opening and
closing is just extra overhead.

I gather that session is an ORM thing, and I'm using some ORM
stuff. However, I could probably manage with just psycopg2, though it
wouldn't be pretty.

> But I also apologize if I'm telling you nothing new, certainly don't
> mean to insult. Just trying to help.

My understanding of the underlying technical details of SQLA is quite
fuzzy, so feel free to explain the obvious to me anytime. I might
learn something new. Thanks for your help.

> I assume Michael will have to explain the blocking thing, but FWIW I
> couldn't reproduce that issue while using SQLite or MySQL, so it
> might have just as much to do with PostgreSQL as anything
> else...whether that means its SA dialect or the server configuration
> itself I couldn't say.

Hmm. That's interesting.
Regards, Faheem.

[King Simon-NFHD78]

> >> On Wed, 28 Jul 2010 15:17:09 +0530 (IST), Faheem Mitha
> <fah...@email.unc.edu> wrote:
> >> >
> >> > Hi,
> >>
> >> > When calling create_all on a metadata instance after a
> session has
> >> > alrady been opened causes the create_all to hang, I
> assume because
> >> > the session is blocking the create_all. Is there some way to get
> >> > create_all to use the existing session, or any other graceful way
> >> > around this? Thanks.
> >>
> >> > I guess another option is to close and then reopen the
> session after
> >> > the create_all has been called, but I'd prefer not to do that if
> >> > possible.
> >>
> >> Puting a session.close() before the create_all fixes the problem. I
> >> assume this means that create_all doesn't work in the middle of a
> >> transaction, or something like that?
> >

You can tell meta.create_all() to use the same underlying DB connection
as the session by using the session.connection() method with the 'bind'
parameter to create_all().

Ie.

connection = session.connection()
meta.create_all(bind=connection)

See the docs at
http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s
essions and
http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche
my.schema.MetaData.create_all

Hope that helps,

Simon

[Faheem Mitha]

On Thu, 29 Jul 2010 11:36:43 +0100, King Simon-NFHD78 <simon...@motorola.com> wrote:

> You can tell meta.create_all() to use the same underlying DB connection
> as the session by using the session.connection() method with the 'bind'
> parameter to create_all().
>
> Ie.
>
> connection = session.connection()
> meta.create_all(bind=connection)
>
> See the docs at
> http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s
> essions and
> http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche
> my.schema.MetaData.create_all
>
> Hope that helps,

Hi Simon,

Thanks. Do you understand why this blocking takes place? I assume by
default create_all tries to make a different connection, and fails for
some reason?

Regards, Faheem.

[Wichert Akkerman]

On 7/29/10 17:18 , Faheem Mitha wrote:
> Hi Simon,
>
> Thanks. Do you understand why this blocking takes place? I assume by
> default create_all tries to make a different connection, and fails for
> some reason?

My guess is that it does not fail, but your database is blocking the
create_all statements while another transaction is active.

Wichert.

[Kyle Schaffrick]

On PostgreSQL what's probably happening is that the connection that the
session object is using is IDLE IN TRANSACTION but is holding some lock,
probably RowExclusiveLock from uncommitted UPDATE/INSERT/DELETEs.

Then, create_all(), when executed on a different connection, enters
into a lock wait because virtually all DDL changes automatically acquire
AccessExclusiveLock, which conflicts with locks the session is holding.
The blocking is because the DDL connection cannot commit until the lock
conflict is resolved, by either the session's connection rolling back
or committing (causing the DDL-containing transaction to commit or
abort, respectively).

Here's a PostgreSQL wiki page with an good query for troubleshooting
lock waits:

http://wiki.postgresql.org/wiki/Lock_dependency_information

Also, if you use pgAdminIII, it has an excellent lock monitor tool
built in. I suggest doing a run of your application such that it gets
blocked in create_all(), and then while it's blocked, check to see what
lock is blocking it using the above linked query.

Hope this helps..

-Kyle