On Jun 2, 2010, at 5:02 PM, Nick Retallack wrote: > I have a lot of questions, so bear with me. I've been having some > doubts about whether I'm really using sqlalchemy in a good way. > > -- > > Is there any use case for having more than one session active in the > same thread?
yes, if you wanted to have one set of data committed to the DB while the other set is still in an open transaction. Such as, you'd like to log the steps of an operation to a logging table, and those rows would be committed even if the operation itself fails an issues a ROLLBACK. > Or does everyone use threadlocal sessions? You probably want to default to using scoped_session(), yes. If you needed a second session for some ad-hoc commit of data without interrupting the main operation, you could call your sessionmaker function distinctly. > If you bind > different tables to different engines in the same metadata, can one > session service them all? yes, this is "vertical partitioning". > If not, this would be a use case for > multiple sessions, which would make threadlocal sessions inconvenient, > unless you made a different threadlocal session for each engine. if your data is vertically partitioned, the Session handles multiple engines, using bound metadata, or the binds argument. If OTOH you're switching between different copies of the same database, such as write master/read slave, you might want to establish a single bind for the life of an operation. > > Is it a common practice to pass the current session into the > constructor of an ORM model? no. The session consumes your objects, not the other way around. An object can always get its current session via object_session(self). > At this point, it can't be in a session > yet, so Session.object_session(self) wont help you. So what if the > constructor needs to do some queries, perhaps to find-or-create some > related models? Is this why pylons uses threadlocal sessions? the scoped session does serve as a way for all areas of an application to get to "the session", yes. But methods on an object should probably use object_session(self) for portability. > > Is connection pooling the sqlalchemy way really what we want? shrugs. you can disable it if you'd rather use pgpool or something. I certainly think the pooling is well worth it, though. > Say for > example I have a variety of projects running on the same machine, all > using sqlalchemy. Since the connection pool is in the engine > instance, there is no way these projects would be sharing information > about the connection pool, so how could you know how many connections > your server is actually generating? you'd do a netstat or otherwise use the monitoring tools provided with your database. the max size of the pool is configurable. > The problem gets worse if you're > running those applications with multiple processes or instances, > because then you really have no idea how many connections there could > be. you have an exact idea. The max size of the pool is deterministic and explicit. > > Would it be better to use a connection pooling solution external to my > python applications? One that had shared knowledge of all of them? the django camp is big on pgpool. To me it seems unnecessary. I've run applications that spawn off lots of child forks, the child forks typically use one session for the life of the subprocess, so its pretty much one connection per subprocess. I don't see why it needs to be any more complicated than that. If your child processes are using more connections than they should be, pgpool doesn't solve that, you should usually have one connection per process per thread. > > If I'm running a web application with fastcgi, I'm already affected by > this framentation of connection pools, right? Considering fastcgi > uses multiple processes. Should I set my pool size to the size I > expect only a single process to use? I wouldn't expect a single > process to use more than one connection at a time, if no threading is > going on. that's correct, so if you limit the pool size to exactly 1 with no overflow, you'll get an error and a stack trace at immediately the point at which your app is opening a second connection, so that it can be corrected. -- 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.
