[ZODB-Dev] RelStorage, postgresql 9.2 and zc.async (plone.app.async)

[Simone Deponti]

Hello all,

I have a system using RelStorage with postgresql (9.2) and plone.app.async
(which is installed only on a dedicated "worker" instance).
While debugging a nasty network problem where idle connections where being
forcefully terminated, I stumbled upon this:

postgres=# select datname, usename, query_start, state_change, state, query
from pg_stat_activity;
      datname       | usename  |          query_start          |
state_change          |        state        |

          query
--------------------+----------+-------------------------------+-------------------------------+---------------------+--------------------------------
---------------------------------------------------------
 replication_repmgr | repmgr   | 2014-03-13 15:34:40.536227+01 | 2014-03-13
15:34:40.536327+01 | idle                | SELECT 1
 prod_zope          | zope     | 2014-03-13 15:34:41.079448+01 | 2014-03-13
15:34:41.079497+01 | idle in transaction | EXECUTE get_latest_tid
 stage_zope         | zope     | 2014-03-13 15:34:40.631671+01 | 2014-03-13
15:34:40.631705+01 | idle in transaction | EXECUTE get_latest_tid
 stage_zope         | zope     | 2014-03-13 15:34:06.37716+01  | 2014-03-13
15:34:06.38005+01  | idle                | COMMIT
 prod_zope          | zope     | 2014-03-13 13:36:24.287554+01 | 2014-03-13
13:36:24.287568+01 | idle                | ROLLBACK
 prod_zope          | zope     | 2014-03-13 13:36:23.887335+01 | 2014-03-13
13:36:23.891213+01 | idle                | COMMIT

Note: the connection are one each for the production and preproduction
database, and is consistent with the fact that we have a worker for
preproduction and one for production.

Which roughly means that I have two connections where the following
happened:
BEGIN TRANSACTION;
EXECUTE get_latest_tid;

And then the connection went idle, without any further command sent to
postgres (a COMMIT or ROLLBACK). This has a number of side effects, namely:

   1. Certain tables remain locked and automatic cleanup functions (e.g.
   AUTOVACUUM) can't properly run
   2. In my case, when this connection gets terminated postgres gets
   reasonably upset

If i turn off the worker instance(s), these connections that are left as
"idle in transaction" disappear. My wild guess is that the worker, relying
as it is on a reactor (Twisted, if I'm not mistaken), doesn't quite follow
the pattern that RelStorage expects (and probably the difference between an
RDBMS and ZEO here comes into play).

What I want to know is:

   1. Someone else experienced this same problem?
   2. My diagnosis makes sense, or am I assuming too much[1]?
   3. Are there any other solutions or workarounds besides "use ZEO" or
   "use some other queue system"?

Thanks everyone,

[1] This has been known to happen frequently :)

-- 
*Simone Deponti*
Project manager

*>_*
abstract.it - +39 06 92 94 69 38
...............................................................................
Registro Imprese di Napoli 788429 / Cap. Soc. 10.000 Euro I.V.
Avvertenze Legali - D. Lgs. 196/03 Tutela dei dati personali. Le
informazioni
contenute in questo messaggio e in ogni eventuale allegato sono riservate e
ne è vietata ogni forma di diffusione. Se avete ricevuto questa
comunicazione
per errore, Vi preghiamo di informare immediatamente il mittente del
messaggio
e di eliminare l'e-mail.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.zope.org/pipermail/zodb-dev/attachments/20140313/d4afb1cf/attachment.html>