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
