[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 - [phone]
...............................................................................
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>

[Shane Hathaway]

Post by Simone Deponti
1. Certain tables remain locked and automatic cleanup functions (e.g.
AUTOVACUUM) can't properly run

Are you using the "poll-interval" option? That option tells RelStorage
to leave the transaction open. In practice, it's just wrong (although it
doesn't corrupt any data.) I think we should remove that option.

Shane

[Simone Deponti]

Are you using the "poll-interval" option? That option tells RelStorage to leave the transaction open. In practice, it's just wrong (although it doesn't corrupt any data.) I think we should remove that option.

No, I haven't set poll-interval explicitly in my configuration and
according to the doc this should be set to 0.

However, I failed to mention that I am not using any memcached (the
site is heavy on writes, and I thought it was not ideal).

Here is my config as extracted from zope.conf

<zodb_db main>
# Main database
cache-size 30000
%import relstorage
<relstorage>
keep-history true
blob-dir /mnt/cmsimages/production/raw
replica-conf /srv/cms/production/var/replicas.conf
pack-gc false
<postgresql>
dsn dbname='prod_zope' user='zope' port='5432'
</postgresql>
</relstorage>
mount-point /
</zodb_db>

Also, this is used in a PostgreSQL HA (streaming replication) environment.
--
Simone Deponti
Project manager

_

abstract.it - [phone]
...............................................................................
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.