RE: [sqlalchemy] Re: open session blocks metadata create_all method
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 -- 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: open session blocks metadata create_all method
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. -- 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: open session blocks metadata create_all method
On Thu, 29 Jul 2010 15:18:33 + (UTC) Faheem Mitha fah...@email.unc.edu wrote: On Thu, 29 Jul 2010 11:36:43 +0100, King Simon-NFHD78 simon.k...@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. 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 -- 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: open session blocks metadata create_all method
On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: 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? 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 -- 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: open session blocks metadata create_all method
On Wed, 2010-07-28 at 17:17 +, Faheem Mitha wrote: Hi Lance, On Wed, 28 Jul 2010 06:45:30 -0500, Lance Edgar lance.ed...@gmail.com wrote: --=-dKyzuPx4woj1H0B5IT48 Content-Type: text/plain; charset=ISO-8859-1 On Wed, 2010-07-28 at 11:33 +, Faheem Mitha wrote: 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? 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() 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. 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 -- 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.
