Re: LISTEN considered dangerous

On Tue, Aug 01, 2006 at 07:50:19PM -0400, Gregory Stark wrote:
> > > However that just doesn't work, because listen is broken, allow me to
> > > illustrate, here A and B are two clients:
> > >
> > > A: BEGIN
> > > A: SELECT * FROM foo and cache the result.
> > > A: LISTEN foochange
> > > B: BEGIN
> > > B: update foo
> > > B: NOTIFY foochange
> > > B: COMMIT
> > > A: COMMIT

> I haven't used LISTEN/NOTIFY myself yet and I do indeed find the behaviour he
> shows somewhat surprising. Normally in read-committed mode uncommitted
> transactions are affected by other transactions when they commit. In this case
> the uncommitted LISTEN is not being affected by the committed NOTIFY.

Eh? At the point the LISTEN is run, the NOTIFY hasn't committed, so a
row is inserted. At the time the NOTIFY is committed, the LISTEN hasn't
committed yet so won't be visible. Only LISTEN is stored, not NOTIFY so
there's nothing wrong with the read-committed semantics.

It's slightly surprising though. I havn't seen anyone else complain
about this before though. The only way to fix this is to make the
LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs
also.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.