Re: fixing LISTEN/NOTIFY

Neil, Jim, All:

> Personally, I think delivering all notifications by default is simpler
> behavior for the application programmer to understand. If you want to
> avoid doing work for duplicate notifications, you realistically need to
> implement that yourself anyway.

I agree with Neil; I don't really see duplicate notifications as a problem, 
provided that we can deliver a message with the notice.  As long as we can 
deliver a PK id or other unique information, then we can still use NOTIFY to 
do, for example, asynchronous summary counts.

However, I really dislike the idea of blocking on out-of-memory NOTICEs.  
While I can see some DBAs wanting that, for most applications I'd want to 
implement, I'd rather that the NOTICE were thrown away than have it stop a 
query.  LISTEN/NOTIFY is a asynchronous messaging mechanism, after all -- the 
whole idea is for it *not* to slow down transactions.

Ideally, we'd have three options which could be set administratively (at 
startup) in postgresql.conf:
notify_mem = 16384 #in K
notify_mem_full = discard #one of: discard, block, or disk# discard = simply drop notices over the memory limit# block
=stop queries from committing until notice memory is open# disk = spill notices to the pg_notice file
 

You'll notice that I added a spill-to-disk on NOTIFY; I think that should be a 
TODO even if we don't implement it immediately.

We should also have a way to detect the situation of notify_mem being full, 
both through log notices and by command-line function.   Finally, we'd need a 
function for the superuser to clear all notices.

I'm thinking here of my personal application for a better LISTEN/NOTIFY: using 
pg_memcached to store fast asynchronous materialized views.  The idea would 
be that you could maintain a materialized view in memory that was no more 
than 30 seconds behind, by:
[every 15 seconds]:
check if there are any notices on the viewif so, regenerate the rows indicated in the notify messageif not, next
check if notify_mem is fullif so, regenerate all mattviews from scratchsend clear_notices();

Hopefully people get the idea?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco