Re: Listen / Notify rewrite

Josh Berkus wrote:
> Payloads are also quite useful even in a lossy environment, where you
> understand that LISTEN is not a queue.  For example, I'd like to be
> using LISTEN/NOTIFY for cache invalidation for some applications; if it
> misses a few, or double-counts them, it's not an issue.  However, I'd
>   
Not sure how missing a few can not be an issue for cache invalidation, 
but never mind.

In the use-cases I've considered I've used a trigger to write a change 
notification to a table
and a notify to indicate that notification record(s) have been changed. 
The notifications
contain copies of the primary keys and the action so the cache processor 
knows what's
changed and the notify is a a wakeup signal.

If this is in fact the most common use case, perhaps an alternative 
approach would be
to automate it directly, so that writing the triggers (and using the 
trigger processing engines)
would be unecessary, so:- the queue definition can be automated with reference to the parent 
table, by DDL stating that one is required- the notification 'name' is effectively the queue name and the 
subscription says 'tell me when a change note is placed in the queue'

Doing this in the database engine core allows a number of potential 
optimisations:- the mechanism does not require general trigger execution- the queue does not have to be a real table,
andcan have custom 
 
semantics: it may not actually be necessary to store copies of the primary key data if it 
can refer to the rows so the data can be retrieved as the queue is consumed- if there are no subscribers to the queue
thenthe insertion to it can 
 
be elided- if the server crashes, connected consumers should assume caches are 
invalid and theer is no ACID requirement for the queue data- if the queue fills then slow consumer(s) can be dropped
andcan 
 
receive a data loss indicator
> like to be able to send message like players_updated|45321 where 45321
> is the ID of the player updated.
>   
Indeed.

Just a thought, anyway.  (FWIW I was initially concerned about the lack 
of payload, but
with any sort of lossy compression I figured it wasn't, actually, and I 
needed a notification
queue)


James