Re: Postgresql Caching

On 10/15/06, mark@mark.mielke.cc <mark@mark.mielke.cc> wrote:
> Using memcache, I've had problems with consistency brought right to
> the front. Both of these have failed me:
>
>     1) When updating a PostgreSQL record, I invalidate the memcache record.
>        If another process comes along in parallel before I commit, notices
>        that the memcache record is invalidated, it queries the data from
>        SQL, and updates the memcache record back to the old value. :-(
>
>     2) When updating a PostgreSQL record, I updated the memcache record
>        to the new value. If another process comes along in parallel before
>        I commit, that is still looking at an older view, cross-referencing
>        may not work as expected.
>
> I'm currently settled on 2), but setting a short timeout (5 seconds) on
> the data. Still an imperfect compromise between speed and accuracy, but
> it isn't causing me problems... yet.

use advisory locks for 'race sensitive' data. (or user locks in <
8.2).  or, just use tables, becuase you need mvcc, not performance :)

merlin