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.
_______________________________________________ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev