[Modeling-users] Attention psycopg users: never-ending transactions / additional info

[Sebastien B. <sbi...@us...>]

Remember I wrote on Sat Feb 22 2003:
> [...]
> Important (I almost forgot, silly me): if you're using the framework in
> --------- a long-standing process (being alive for more than 24 hours),
>           I strongly suggest that you upgrade your copy and that you do
>           NOT set MDL_PERMANENT_DB_CONNECTION (see changelog below) ; I
>           have experienced strange behaviour with long-standing
>           processes (w/ python2.1 & Zope 2.6, psycopg v1.0.14 and
>           1.0.15, and PG server 7.2), such as committed changes being
>           correctly issued by the psycopg adaptor but never committed
>           within the postgresql server, hence causing the corresponding
>           transaction to be *lost* (when this happens, you see a
>           postgresql 'idle transaction in progress', never ending until
>           the python process itself dies)
>=20
>   While the exact reason for this problem is not completely clear at the
>   moment, it was solved by the new behaviour (which closes the
>   connection to the db as soon as no more adaptor channels are opened).

Current status: even with psycopg 1.1.4 and MDL_PERMANENT_DB_CONNECTION
unset, the problem still appears on one project using modeling and zope
(despite what I wrote at that time, saying it could be resolved).

This *only* affects modeling/psycopg in a multi-threaded environment
(such as with zope).

  Other users also reported the problem, for example:
    http://lists.initd.org/pipermail/psycopg/2002-August/001308.html
    http://lists.initd.org/pipermail/psycopg/2003-June/002079.html
  My initial report is at:
    http://lists.initd.org/pipermail/psycopg/2003-March/thread.html#1885

A few days ago I worked again on this and I may have found the reason
for this, along with a test prg. reproducing the pb. and a possible
correction; I'm still waiting for comments from the psycopg
mailing-list. May it be of some interest to you, you'll find my analysis
and proposals at:
    http://lists.initd.org/pipermail/psycopg/2003-June/002086.html

  In brief: until an other solution is validated by the psycopg guys, I
just removed the Py_BEGIN_ALLOW_THREADS and Py_END_ALLOW_THREADS
statements in cursor.c:_psyco_curs_execute() and the problem seems to
disappear (note that by doing this, if you have some sql queries taking
a long time to execute, all the other threads will be *blocked* until the
query finishes).

  Please keep in mind that for the moment being this is /my/ analysis
  and definitely not an authoritative answer to the bug.

I'll report here when additional infos are available. I've thought you'd
appreciate to be kept informed.

-- S=E9bastien.