"Ian Harding" <harding.ian@xxxxxxxxx> 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
