Re: Postgresql Caching

mark(at)mark(dot)mielke(dot)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.

Hmm, have you tried having a NOTIFY when the Postgres record is updated,
and having a listener daemon that updates the value when it gets the
notification?

One issue is that if the listener is not running you don't get to
update nor invalidate the record, which would cause consistency problems.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support