Обсуждение: LISTEN / NOTIFY enhancement request for Postgresql
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
My request is to implement the same or similar feature in Postgresql.
Thank you.
-Sev
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
On Thu, Oct 24, 2013 at 11:41:57AM -0400, Sev Zaslavsky wrote: > 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. This does seem useful and pretty easy to implement. Should we add a TODO? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes: >> • 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. > > This does seem useful and pretty easy to implement. Should we add a > TODO? I think we should consider the ltree syntax in that case, as documented in the following link: http://www.postgresql.org/docs/9.3/interactive/ltree.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hello, Dimitri. You wrote: DF> Bruce Momjian <bruce@momjian.us> writes: >>> • 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. >> >> This does seem useful and pretty easy to implement. Should we add a >> TODO? DF> I think we should consider the ltree syntax in that case, as documented DF> in the following link: DF> http://www.postgresql.org/docs/9.3/interactive/ltree.html Great idea! Thanks for link. DF> Regards, DF> -- DF> Dimitri Fontaine DF> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- With best wishes,Pavel mailto:pavel@gf.microolap.com
Thank you all for considering my feature request. Dimitri's suggestion is a very good one - I feel it will accomplish the goal of allowing more granularity in the "Listen". We might also want to add a flag in postgresql.conf to disable this enhancement so that we don't break existing code. On 11/15/2013 8:19 AM, Pavel Golub wrote: > Hello, Dimitri. > > You wrote: > > DF> Bruce Momjian <bruce@momjian.us> writes: >>>> • 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. >>> This does seem useful and pretty easy to implement. Should we add a >>> TODO? > DF> I think we should consider the ltree syntax in that case, as documented > DF> in the following link: > > DF> http://www.postgresql.org/docs/9.3/interactive/ltree.html > > Great idea! Thanks for link. > > DF> Regards, > DF> -- > DF> Dimitri Fontaine > DF> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > > > >
Hello, Sev. You wrote: SZ> Thank you all for considering my feature request. SZ> Dimitri's suggestion is a very good one - I feel it will accomplish the SZ> goal of allowing more granularity in the "Listen". SZ> We might also want to add a flag in postgresql.conf to disable this SZ> enhancement so that we don't break existing code. I suppose it should be GUC variable (not only global entry) for per session settings. SZ> On 11/15/2013 8:19 AM, Pavel Golub wrote: >> Hello, Dimitri. >> >> You wrote: >> >> DF> Bruce Momjian <bruce@momjian.us> writes: >>>>> • 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. >>>> This does seem useful and pretty easy to implement. Should we add a >>>> TODO? >> DF> I think we should consider the ltree syntax in that case, as documented >> DF> in the following link: >> >> DF> http://www.postgresql.org/docs/9.3/interactive/ltree.html >> >> Great idea! Thanks for link. >> >> DF> Regards, >> DF> -- >> DF> Dimitri Fontaine >> DF> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support >> >> >> >> >> -- With best wishes,Pavel mailto:pavel@gf.microolap.com
On Mon, Nov 18, 2013 at 09:15:39AM -0500, Sev Zaslavsky wrote: > Thank you all for considering my feature request. > > Dimitri's suggestion is a very good one - I feel it will accomplish > the goal of allowing more granularity in the "Listen". > > We might also want to add a flag in postgresql.conf to disable this > enhancement so that we don't break existing code. TODO added. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +