Skip site navigation (1) Skip section navigation (2)

LISTEN / NOTIFY enhancement request for Postgresql

From: Sev Zaslavsky <sevzas(at)gmail(dot)com>
To: pgsql-hackers(at)postgreSQL(dot)org
Cc: Bruce Momjian <bruce(at)momjian(dot)us>
Subject: LISTEN / NOTIFY enhancement request for Postgresql
Date: 2013-10-24 15:41:57
Message-ID: 52693FC5.7070507@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi pgsql-hackers,

The LISTEN / NOTIFY feature (along with the pg_notify() function) is a 
unique feature that differentiates Postgresql from nearly all other 
relational database systems.  With the exception of SQL Server, I know 
of no other RDBMSs that allow a client to be asynchronously notified by 
the database server.

This feature embodies the modern "push" approach and allows delivering 
timely data to the user as it changes, instead of the more traditional 
"pull" approach which requires the user to request the data at specific 
intervals.  Vendors are rolling out "push" technologies to meet market 
demand.  Microsoft recently introduced SignalR - which is a framework 
for pushing content to ASP.NET Web pages.  Similarly Complex Event 
Processing systems "push" information to users' dashboards in real-time.

In contrast with RDBMS's where asynchronous notification is a special 
feature, message broker software implementations live and breathe 
asynchronous notification.    So I feel that the LISTEN / NOTIFY feature 
is trying to deliver some of the asynchronous notification features of a 
message broker but it lacks some of the flexibility.

One particular shortcoming of LISTEN / NOTIFY is the fact that the 
channel specified on the LISTEN must _exactly _match the channel 
specified on the NOTIFY.  Here is an example of the problem:

I have two listeners:
      1. Interested in all stock quote updates
      2. Interested in stock quote updates for IBM only

There is a table that contains stock prices with a trigger proc that 
issues a NOTIFY using pg_notify() upon update.  There isn't a single 
channel that I can use that will deliver the message to both listeners.  
To get around the problem I could publish a message on channel "PRICE" 
and another message on channel "PRICE.IBM" but sending two notifications 
is far from optimal.

Message brokers have implemented a neat way to get around this issue.   
It is accomplished by allowing wildcards in message topic subscriptions.

Here is an example 
implementation:http://activemq.apache.org/nms/activemq-wildcards.html

  * is used to separate names in a path
  * * is used to match any name in a path
  *  > is used to recursively match any destination starting from this name

For example using the example above, these subscriptions are possible

Subscription 	Meaning
PRICE.> 	Any price for any product on any exchange
PRICE.STOCK.> 	Any price for a stock on any exchange
PRICE.STOCK.NASDAQ.* 	Any stock price on NASDAQ
PRICE.STOCK.*.IBM 	Any IBM stock price on any exchange


My request is to implement the same or similar feature in Postgresql.

Thank you.

-Sev

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2013-10-24 16:22:59
Subject: Re: Compression of full-page-writes
Previous:From: ktm@rice.eduDate: 2013-10-24 15:40:45
Subject: Re: Compression of full-page-writes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group