Z SQL Methods Frequently Asked Questions

This document is here to try and answer questions that people have had reguarding how to use Z SQL Methods, and also how they are written. If you wish to ask a question that is not here, please send it to Chris for inclusion.

Does Zope do "connection pooling" to Oracle/Sybase/etc?

The short answer is yes. The long answer is a bit more complicated, but much more interesting (from Jim Fulton):

Zope maintains a pool of ZODB database connections. Currently this pool size is set to 7 (for non-version connections). (There ought to be a way to adjust this without hacking Python code, and there will be in 2.2.) Practically speaking, the ZODB connection pool size is the lesser of the number of threads and 7. What does this have to do with Oracle or Postgres connections?

When you define a database connection object in Zope, there could be a copy of the connection object for each ZODB connection, depending on how heavily the connection object is used. Each copy of the connection object will have it's own connection to the underlying database. This means that there will be an RDBMS connection pool for each Zope database connection object. The size of this connection pool is the lesser of the number of zope threads and the size of the ZODB connection pool. RDBMS connections will be added and removed from the connection pool based on need.

Consider the following example. Suppose we have a Zope process using ZServer with the default thread/pool configuration and a single ZOracleDA database connection object. If the site is handling alot of requests that require access to Oracle, then there will probably be 4 oracle connections, since the default number of Zope threads is 4. If the nuber of requests requiring Oracle support drops off, then will the number of Oracle connections. In fact, the number of Oracle connections could drop to zero. If the number of requests pick up, then so will the number of Oracle connections. This all happens automatically as a by-product of the standard Zope management of ZODB database objects.

One caveate is that to get the benefit of connection pooling, the database adapter needs to be fully threaded. This is the case for Oracle. I'm not sure what the statis is for the Postgres DA.

We plan to put together a more extensive explanation of this in the form of a white paper or how to. I hope this explanation helps in the mean time.

If a user cancels their web request, is the database connection aborted?

Unfortunately, no. Currently, there is no way to interrupt a call to the database while it is pending return. This is a combination of lack of plumbing in Zope as well as the inability to abort a pending C-level database call.