Re: psycopg2 vs psycopg1: the _v_database_connection problem in Zope 2

Il giorno dom, 21/09/2008 alle 18.02 +0200, Marco Bizzarri ha scritto:
> Hi all.
> 
> After hunting this problem a lot, in the end I was able to pin it.
> 
> ZPsycopgDA for psycopg1, as many others database adapters in Zope,
> uses the _v_database_connection attribute to keep a connection per
> thread.
> 
> The problem is that, under heavy load, the ZPsycopgDA object can be
> "ghostified", i.e. unloaded from the cache, and be recreated only
> later, when it is needed again. This, unfortunately, can happen within
> one transaction, thus potentially creating a situation where:
> 
> a (Zope) transaction begins,
> a query to postgresql is issued, creating a new RDBMS transaction;
> an heavy load causes the ZPsycopgda to be ghostified;
> a new query causes the ZPsycopgDA to be loaded again,
> a new connection to the database is established;
> 
> Of course, this can cause any sort of troubles to an application.
> 
> 
> Therefore, I'm asking if the psycopg2 adapter can suffer from the same
> problem; if it does not, it could be the "final" push for me to switch
> to psycopg2, after so many years of faithful psycopg1 use :-)

psycopg2 uses a connection pool but still uses a volatile (_v_) variable
to hold the active connection, so the issue is still there. I don't know
why Zope unloads from cache an object that called self._register() and
registered for transaction management (sounds crazy to me) but if that
happens the current connection is rolled back and closed. Really, I
don't understand why Zope is so dumb to serialize a live object that
registered for the current transaction.

The problem here is that psycopg connections hold references to system
resources (like sockets) and can't be easily serialized, so putting then
in anything else that a volatile attribute will raise a serialization
error. Keeping a socket open while the connection object is serialized
and removed from memory is probably possible but will require quite some
work in the C module and, sincerely, I don't see any sensible reason do
to that. Sorry.

federico

-- 
Federico Di Gregorio                         http://people.initd.org/fog
Debian GNU/Linux Developer                                [email protected]
INIT.D Developer                                           fog-NGVKUo/i/[email protected]
  99.99999999999999999999% still isn't 100% but sometimes suffice. -- Me

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkjXd0cACgkQvcCgrgZGjesFOwCghpzkchDRj9tv5cLFkq4JC9Xo
/lkAn0ZZalypz4iLkX1HNEtOpQ87z61v
=vP2w
-----END PGP SIGNATURE-----