Ed Rahn <[email protected]> wrote:
> > On 02/13/2015 11:30 PM, Michael Bayer wrote: >> Ed Rahn <[email protected]> wrote: >> >>> I have several programs that are Multi Process and long running, they open >>> up 30 or so connections and do selects periodically. For a select query, >>> the default behaviour is to begin a transaction if not currently in one, >>> but not commit afterwards. This leaves a large number of postgresql >>> processes with a status of "idle in transaction". Which means they “lock” >>> tables, so you can not drop them or alter them including add and drop >>> indexes. I have also seen some problems were connections do not get closed >>> if the connecting process exits, although I haven’t verified this is the >>> cause. >>> >>> Is this a problem others have had in the past or am I just being overly >>> worried? >> yeah that’s kind of bad. you want the connections to be “idle”, but >> definitely not “idle in transaction”. that will cause problems. >> >> >> >>> If it is a problem is there any other way to fix it beside commit()’ing >>> after each select query? I tried add an event handler after queries get >>> run, but I didn't see a way to get to the current transaction. >> Well at least on the connection itself, if its used in non-autocommit mode >> (by which I refer to psycopg2’s autocommit flag), the “idle in transaction” >> will remain until either commit() or rollback() is called on that >> connection. >> >> So if you have a Session, and want to stay at that level, your options are >> to commit() it, to rollback() it, to close() it which returns the connection >> to the pool which does a connection-level rollback, or you could use the >> Session in autocommit=True mode, which means after each query it returns the >> connection to the pool for the same effect. > I'd really like to not use autocommit mode. There are parts of the code that > I need to maintain DB consistency with transactions. > And I need to keep the objects attached to a session so automatically closing > it isn't an option. > So I guess my only option is to commit after each select, which seems like a > lot of work as the code base is fairly large. > > This seems like a fairly common use case, do people just not care about it or > how do they handle it? Usually the application is written such that the start/end of how a Session is used is just in one place in the app. Web applications can do this, and also approaches like using decorators or custom context managers may be used. Often, people just use the built in context manager of the Session, such as: with session.transaction: # do stuff I talk about this a lot in this section: http://docs.sqlalchemy.org/en/rel_0_9/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it at the end is an example of how to build a context manager to do what you need. >> If you want to turn off transactions completely with the DBAPI connection >> itself, even though this overhead is very minimal for Postgresql you could >> set it to isolation level of AUTOCOMMIT which for psycopg2 sets the >> “autocommit” flag on the connection. The commit()/ rollback() calls from >> SQLAlchemy would have no effect. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
