LISTEN considered dangerous

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

When A continues with an other transaction it will never get the event
from B and thus will keep using the cached foo data, clearly this is not
what you'd want.

The workaround is to commit after the listen, but that too is broken
because then you'd commit all the changes up to that point, also not a
desirable situation.

The only real way to work around the problem is to LISTEN to every
single object that could ever be interesting to cache and commit right
after connecting the first time.

The reason for this is that LISTEN is implemented by inserting into a
table that's under transaction control (well naturally), so the actual
listening doesn't start until the transaction has been committed.

I'm quite lucky I didn't get any corrupted data from this gotcha, but I
did get som annoyed users, so let this be a warning to other pg users.

The correct behaviour would be to start listening at the begining of the
transaction, when committed, IMHO.

To allow this the entire implementation needs to change so all events
from all transactions are stored until all connections with earlier
started transactions have started new transactions.

This way we could even have wildcard listens, imagine doing a listen %
and getting all the generated events:)

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