Re: LISTEN considered dangerous

On 8/1/06, Flemming Frandsen <ff(at)partyticket(dot)net> wrote:
> I have an application that does aggresive caching of data pulled from
> the database, it even keeps the objects cached between transactions.
>
> Normally this works very well and when the cache is warmed up about 90%
> of the database time is saved.
>
> However that leaves the problem of how to notice that my cached objects
> have turned stale, luckily pg has the listen/notify feature so I have
> triggers on all tables that do a notify, as you do.
>
> 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.