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, and can 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 then the 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 and can
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