Re: LISTEN considered dangerous

"Ian Harding" <harding.ian@gmail.com> writes:

> > 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
> >
> From the docs:.
>
> NOTIFY interacts with SQL transactions in some important ways.
> Firstly, if a NOTIFY is executed inside a transaction, the notify
> events are not delivered until and unless the transaction is
> committed. This is appropriate, since if the transaction is aborted,
> all the commands within it have had no effect, including NOTIFY. But
> it can be disconcerting if one is expecting the notification events to
> be delivered immediately.

Note that he's not complaining about when the NOTIFY takes effect. He's
complaining about when the LISTEN takes effect.

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.




--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com