Ed Rahn <edsr...@gmail.com> 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. 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
