Feature request: NOTIFY enhancement

Dear developers,

With the existing single-notification-value mechanism, not only
SELECT'ing tables are necessary in some cases but DELETE'ing rows and
synchronizing the DELETE are also required in many cases where there are
multiple client connections to the same database.

As an example, supposing there are 3 client connections to a database,
c1, c2, and c3. C1 updates a row with primary key value "cust A" on
table "customer". Then c1 notifies all clients to refresh their local
data only for "cust A". The present implementation requires c2 and c3 to
read all rows from table "customer" instead of just one row that has
primary key value "cust A".

Probably one will implement an additional table, say tableX, so that c1
will first insert value "cust A" into tableX and then do the
notification. Sure c2 and c3 can first check tableX so they can
determine how to proceed. Problem is who is responsible for the delete
of row from tableX, and when and which row! This is really complicate
because c1, c2, or c3 can update the row for "cust B" any time later,
too.

I am not sure if I am asking too much but does it make sense, and is it
possible, to enhance NOTIFY that process "name/value" pair? Like this:

NOTIFY "MyName=MyValue";

With the capability of name/value pair, the listener will not have to
check notification details by reading tables.

Thank you!

CN

--
http://www.fastmail.fm - The professional email service

> I am not sure if I am asking too much but does it make sense, and is it
> possible, to enhance NOTIFY that process "name/value" pair? Like this:
>
> NOTIFY "MyName=MyValue";
>
> With the capability of name/value pair, the listener will not have to
> check notification details by reading tables.

TODO already has:

* Add optional textual message to NOTIFY

This would allow an informational message to be added to the notify
message, perhaps indicating the row modified or other custom
information.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hello,

On Thu, 3 Jan 2008 11:21:37 -0500 (EST) Bruce Momjian wrote:

> > I am not sure if I am asking too much but does it make sense, and is it
> > possible, to enhance NOTIFY that process "name/value" pair? Like this:
> >
> > NOTIFY "MyName=MyValue";
> >
> > With the capability of name/value pair, the listener will not have to
> > check notification details by reading tables.
>
> TODO already has:
>
> * Add optional textual message to NOTIFY
>
> This would allow an informational message to be added to the notify
> message, perhaps indicating the row modified or other custom
> information.

It is possible, that some notifies, if following in a very short time
frame, can get lost.

In case we want to send extra text messages with NOTIFY, we should make
sure, that no notify get lost.

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

Andreas 'ads' Scherbaum wrote:
> > > With the capability of name/value pair, the listener will not have to
> > > check notification details by reading tables.
> >
> > TODO already has:
> >
> > * Add optional textual message to NOTIFY
> >
> > This would allow an informational message to be added to the notify
> > message, perhaps indicating the row modified or other custom
> > information.
>
> It is possible, that some notifies, if following in a very short time
> frame, can get lost.
>
> In case we want to send extra text messages with NOTIFY, we should make
> sure, that no notify get lost.

Agreed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andreas 'ads' Scherbaum wrote:

> It is possible, that some notifies, if following in a very short time
> frame, can get lost.
>
> In case we want to send extra text messages with NOTIFY, we should make
> sure, that no notify get lost.

Right. Currently, NOTIFY acts like Unix signals -- consecutive signals
can get "collapsed" into a single one, and the listening process is
responsible for ensuring that it gets the communication details from
elsewhere.

If the idea is to convert NOTIFY into a full-blown communication system,
then no collapsing can take place. This means the additional data
(which can be of unbounded size) must be stored elsewhere, probably on
disk.

So far (AFAIK), the only detailed design proposal came from Neil Conway,
which used SLRU for storage, but AFAIK he didn't take it much further.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Andreas 'ads' Scherbaum wrote:
>
> > It is possible, that some notifies, if following in a very short time
> > frame, can get lost.
> >
> > In case we want to send extra text messages with NOTIFY, we should make
> > sure, that no notify get lost.
>
> Right. Currently, NOTIFY acts like Unix signals -- consecutive signals
> can get "collapsed" into a single one, and the listening process is
> responsible for ensuring that it gets the communication details from
> elsewhere.
>
> If the idea is to convert NOTIFY into a full-blown communication system,
> then no collapsing can take place. This means the additional data
> (which can be of unbounded size) must be stored elsewhere, probably on
> disk.
>
> So far (AFAIK), the only detailed design proposal came from Neil Conway,
> which used SLRU for storage, but AFAIK he didn't take it much further.

TODO has:

* Allow LISTEN/NOTIFY to store info in memory rather than tables?

Currently LISTEN/NOTIFY information is stored in pg_listener. Storing
such information in memory would improve performance.

I have added this to TODO:

* Allow multiple identical NOTIFY events to always be communicated to the
client, rather than sent as a single notification to the listener

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

bruce(at)momjian(dot)us (Bruce Momjian) writes:
>> I am not sure if I am asking too much but does it make sense, and is it
>> possible, to enhance NOTIFY that process "name/value" pair? Like this:
>>
>> NOTIFY "MyName=MyValue";
>>
>> With the capability of name/value pair, the listener will not have to
>> check notification details by reading tables.
>
> TODO already has:
>
> * Add optional textual message to NOTIFY
>
> This would allow an informational message to be added to the notify
> message, perhaps indicating the row modified or other custom
> information.

I keep wondering if this isn't just going to grow into pgq...

<http://docs.huihoo.com/api/skytools/pgq-module.html>

In effect, once you start defining more semantics for this, I think it
rapidly transforms into a message queueing system, and you might as
well go straight to something full-fledged like pgq...
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/lisp.html
STATED REASON DOES NOT COMPUTE WITH PROGRAMMED FACTS...

On 1/3/08, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
> bruce(at)momjian(dot)us (Bruce Momjian) writes:
> >> I am not sure if I am asking too much but does it make sense, and is it
> >> possible, to enhance NOTIFY that process "name/value" pair? Like this:
> >>
> >> NOTIFY "MyName=MyValue";
> >>
> >> With the capability of name/value pair, the listener will not have to
> >> check notification details by reading tables.
> >
> > TODO already has:
> >
> > * Add optional textual message to NOTIFY
> >
> > This would allow an informational message to be added to the notify
> > message, perhaps indicating the row modified or other custom
> > information.
>
> I keep wondering if this isn't just going to grow into pgq...
>
> <http://docs.huihoo.com/api/skytools/pgq-module.html>
>
> In effect, once you start defining more semantics for this, I think it
> rapidly transforms into a message queueing system, and you might as
> well go straight to something full-fledged like pgq...

Sligtly better link for PgQ:
https://developer.skype.com/SkypeGarage/DbProjects/SkyTools#head-65d9b2775e38524d7ce3f3940700cdf924480bc6

--
marko

On Thu, 03 Jan 2008 14:20:41 -0500, "Chris Browne" <cbbrowne(at)acm(dot)org>
said:
> > TODO already has:
> >
> > * Add optional textual message to NOTIFY
> >
> > This would allow an informational message to be added to the notify
> > message, perhaps indicating the row modified or other custom
> > information.
>
> I keep wondering if this isn't just going to grow into pgq...
>
> <http://docs.huihoo.com/api/skytools/pgq-module.html>
>
> In effect, once you start defining more semantics for this, I think it
> rapidly transforms into a message queueing system, and you might as
> well go straight to something full-fledged like pgq...

While I believe pgq is feature rich, I prefer to see this TODO item be
implemented. When it happens, all I need will be some more libpq C
functions with which I will link my C client application as a single
executable file. The benefits from the embedded feature will make the
client lighter, easier to manage, and run faster.

Regards,

CN

--
http://www.fastmail.fm - One of many happy users:
http://www.fastmail.fm/docs/quotes.html

On 1/4/08, CN <cnliou9(at)fastmail(dot)fm> wrote:
> On Thu, 03 Jan 2008 14:20:41 -0500, "Chris Browne" <cbbrowne(at)acm(dot)org>
> said:
> > > TODO already has:
> > >
> > > * Add optional textual message to NOTIFY
> > >
> > > This would allow an informational message to be added to the notify
> > > message, perhaps indicating the row modified or other custom
> > > information.
> >
> > I keep wondering if this isn't just going to grow into pgq...
> >
> > <http://docs.huihoo.com/api/skytools/pgq-module.html>
> >
> > In effect, once you start defining more semantics for this, I think it
> > rapidly transforms into a message queueing system, and you might as
> > well go straight to something full-fledged like pgq...
>
> While I believe pgq is feature rich, I prefer to see this TODO item be
> implemented. When it happens, all I need will be some more libpq C
> functions with which I will link my C client application as a single
> executable file. The benefits from the embedded feature will make the
> client lighter, easier to manage, and run faster.

I don't understand you. You basically need only 3 SQL functions
to read events from PgQ:

SELECT * from pgq.next_batch();
SELECT * from pgq.fetch_batch_events();
SELECT * from pgq.finish_batch();

To publish events to PgQ you need 1 SQL function:

SELECT * from pgq.insert_event();

How do those 4 queries make your client "fat" ?

--
marko