Re: LISTEN considered dangerous

Martijn van Oosterhout wrote:
>>>>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
>
> 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.

No, that's all well and good it behaves as you'd expect when you know
how it's implemented, that doesn't make it more usable though.

> It's slightly surprising though. I havn't seen anyone else complain
> about this before though.

I'm likely to do that:)

> The only way to fix this is to make the
> LISTEN completely atransactional, so NOTIFY can see uncomitted LISTENs
> also.

Not at all.

The listen should simply listen for events issued at the start of the
transaction it's executed in.

To do that without timetravel we'd need to store all events from all
transactions, but that's not too bad if it's implemented right.

--
Regards Flemming Frandsen - http://dion.swamp.dk - YAPH