[Zope] Problem with ZPsyopg

[Dieter Maurer]

David wrote at 2005-6-1 12:12 +0100:
> ...
>Is there a way how I can configure my system, so that idle connections 
>expire when not used for a certain time?

In some sense, this is the case -- also not directly controlled
by time.

  Idle connections "expire" when the DA object is flushed from the
  ZODB cache.

  The ZODB cache is not controlled by inactivity time but the
  the number of object that may be active in the cache.
  If the cache has too many objects, least recently used objects
  are flushed at transaction boundaries.

This gives you 2 easy options to (partially) control the
number of connections:

 * You can reduce the number of Zope worker threads (default 4).

   Each thread has its own ZODB cache. Therefore, the maximal
   number of needed connections is number of DA instances
   times number of threads

 * You can reduce the ZODB cache capacity (to increase the likelyhood
   that connections are expired).


Your idea to expire connections after some inactivity time
it difficult to implement on Zope's side. The standard Zope
is passive -- there are no active components such as a scheduler
which would supervise timeouts.
Of course, a scheduler could be implemented (there are "scheduler"
products around), but it would need to know which DAs should 
be supervised and how to expire them.

A better chance would be to implement this on the Postgres Server side
(although I do not know whether Postgres supports such an option).


Finally, "ZmxODBCDA" (commercial, but reasonably priced)
provides a DA which a connection pool. I do not know "ZmxODBCDA"
in more detail, but I expect that the pool would be able
to effectively restrict the number of concurrently open
connections. Have a look at it. Even when you do not use
it, you could learn how to implement a pool for yourself.

-- 
Dieter