Thread: Notify enhancement

Notify enhancement

From
Andrew Dunstan
Date:
Josh Berkus asked me recently what cool things I would be working on 
now. I have just received the go ahead to work on this TODO item for the 
next release:
 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.



cheers

andrew




Re: Notify enhancement

From
Alvaro Herrera
Date:
Andrew Dunstan wrote:

> Josh Berkus asked me recently what cool things I would be working on 
> now. I have just received the go ahead to work on this TODO item for the 
> next release:
> 
>  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.

Great!

Are you also planning to do the removal of the pg_listener catalog?
There were some ideas on how to do this, but nothing thoroughly
designed.

Neil Conway talked about using SLRU as a mechanism for storing listeners
and messages.  Maybe you can find that discussion in the archives.  Part
of it was via IRC though, and I don't remember how much of it was.  So
details on the archives may be thin.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: Notify enhancement

From
Andrew Dunstan
Date:
Alvaro Herrera wrote:
> Andrew Dunstan wrote:
>
>   
>> Josh Berkus asked me recently what cool things I would be working on 
>> now. I have just received the go ahead to work on this TODO item for the 
>> next release:
>>
>>  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.
>>     
>
> Great!
>
> Are you also planning to do the removal of the pg_listener catalog?
> There were some ideas on how to do this, but nothing thoroughly
> designed.
>
> Neil Conway talked about using SLRU as a mechanism for storing listeners
> and messages.  Maybe you can find that discussion in the archives.  Part
> of it was via IRC though, and I don't remember how much of it was.  So
> details on the archives may be thin.
>
>   

I have no fixed plans. The only thing I know is a) we need a message and 
b) we need to change the semantics slightly so that events are no longer 
collapsed.

If anyone has bright ideas for this, now is the time to air them. I 
expect to begin work on it in a couple of weeks.

cheers

andrew


Re: Notify enhancement

From
Matteo Beccati
Date:
Andrew Dunstan wrote:
> I have no fixed plans. The only thing I know is a) we need a message and 
> b) we need to change the semantics slightly so that events are no longer 
> collapsed.
> 
> If anyone has bright ideas for this, now is the time to air them. I 
> expect to begin work on it in a couple of weeks.

I havo no bright ideas on this topic, but I'll willingly try update the 
PHP API once things have settled down.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com


Re: Notify enhancement

From
Guillaume Lelarge
Date:
Alvaro Herrera a ecrit le 01/12/2006 00:19:
> Andrew Dunstan wrote:
> 
>> Josh Berkus asked me recently what cool things I would be working on 
>> now. I have just received the go ahead to work on this TODO item for the 
>> next release:
>>
>>  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.
> 
> Great!
> 

+1

> Are you also planning to do the removal of the pg_listener catalog?
> There were some ideas on how to do this, but nothing thoroughly
> designed.
> 

I would love to see this coming (the removal of the pg_listener 
catalog). It would help me to put this future release in my clients' 
servers.

> Neil Conway talked about using SLRU as a mechanism for storing listeners
> and messages.  Maybe you can find that discussion in the archives.  Part
> of it was via IRC though, and I don't remember how much of it was.  So
> details on the archives may be thin.
> 


-- 
Guillaume.


Re: Notify enhancement

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I have no fixed plans. The only thing I know is a) we need a message and
> b) we need to change the semantics slightly so that events are no longer
> collapsed.

As long as things are backwards compatible, I'm fine with that, although
the "no longer collapsed" makes me a little nervous.

> If anyone has bright ideas for this, now is the time to air them. I
> expect to begin work on it in a couple of weeks.

Not necessarily related to your work, per se, but it would be nice if we
could finally liberate notify strings from the relation constraint and
make them a real string.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200612031756
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFc1YcvJuQZxSWSsgRAlrVAJ4hJiff3T/bGxg+FLAIH0X4e1GAJQCg0T5f
/TK3B/tMsRzoGYerK3W+fvg=
=i0qd
-----END PGP SIGNATURE-----




Re: Notify enhancement

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Not necessarily related to your work, per se, but it would be nice if we
> could finally liberate notify strings from the relation constraint and
> make them a real string.

What "relation constraint"?  They can be any identifier you want.
        regards, tom lane


Re: Notify enhancement

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Tom Lane asked:

> What "relation constraint"?  They can be any identifier you want.

To clarify, it would be nice to use any arbitrary string (perhaps up
to NAMEDATALEN), rather than bumping into the rules for valid identifiers:

LISTEN notify.name.like.this;

LISTEN table.mutation;

It would also make things like this act more intuitively:

LISTEN nosuchschema.abc;

NOTIFY foo.bar.abc;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200612031856
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFc2WSvJuQZxSWSsgRAkQOAJ93c1ZiTx9h9Bho0YLpFmNwrRHVzACgp/tl
hi1sQRbEcFRpMNNbQQ7xnhM=
=LAI2
-----END PGP SIGNATURE-----




Re: Notify enhancement

From
"Andrew Dunstan"
Date:
Greg Sabino Mullane wrote:
>
> Tom Lane asked:
>
>> What "relation constraint"?  They can be any identifier you want.
>
> To clarify, it would be nice to use any arbitrary string (perhaps up
> to NAMEDATALEN), rather than bumping into the rules for valid identifiers:
>
> LISTEN notify.name.like.this;
>
> LISTEN table.mutation;
>
> It would also make things like this act more intuitively:
>
> LISTEN nosuchschema.abc;
>
> NOTIFY foo.bar.abc;
>


If it's going to be an arbitrary string it will have to be single quoted,
like other string literals, and unlike identifiers. But I honestly can't
see why we would introduce such an unnecessary backwards incompatibility.

You can use $ as a delimiter if you want some sort of hierarchy of events,
e.g.

LISTEN foo$bar;

Incidentally, I think we will also need to limit the length of the message
string if we're going to store this in shared memory. I'm currently
thinking of NAMEDATALEN per message, but I am open to argument.

cheers

andrew




Re: Notify enhancement

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> To clarify, it would be nice to use any arbitrary string (perhaps up
> to NAMEDATALEN), rather than bumping into the rules for valid identifiers:
> LISTEN notify.name.like.this;

So use double quotes:

LISTEN "notify.name.like.this";

I can't imagine why we'd change the syntax from "identifier" to "string
literal" --- it would break existing programs for essentially zero gain.
        regards, tom lane


Re: Notify enhancement

From
Tom Lane
Date:
"Andrew Dunstan" <andrew@dunslane.net> writes:
> Incidentally, I think we will also need to limit the length of the message
> string if we're going to store this in shared memory. I'm currently
> thinking of NAMEDATALEN per message, but I am open to argument.

I suppose you're envisioning a ring of fixed-size message buffers
similar to the sinval implementation.  With two NAMEDATALEN items
per message this would be 128 bytes each, a lot of which would go unused
in typical applications ... but on the other hand I can foresee some
apps wishing they could send payload strings longer than NAMEDATALEN.

Seems like it would not be that much harder to allow variable-length
messages, remove the padding and avoid any hard limit on message size.
You'd have to track the "fill" and "empty" pointers at the level of
bytes not message numbers, but so what?
        regards, tom lane


Re: Notify enhancement

From
Andrew Dunstan
Date:
Tom Lane wrote:
> "Andrew Dunstan" <andrew@dunslane.net> writes:
>   
>> Incidentally, I think we will also need to limit the length of the message
>> string if we're going to store this in shared memory. I'm currently
>> thinking of NAMEDATALEN per message, but I am open to argument.
>>     
>
> I suppose you're envisioning a ring of fixed-size message buffers
> similar to the sinval implementation.  With two NAMEDATALEN items
> per message this would be 128 bytes each, a lot of which would go unused
> in typical applications ... but on the other hand I can foresee some
> apps wishing they could send payload strings longer than NAMEDATALEN.
>
> Seems like it would not be that much harder to allow variable-length
> messages, remove the padding and avoid any hard limit on message size.
> You'd have to track the "fill" and "empty" pointers at the level of
> bytes not message numbers, but so what?
>
>     

Ok. But I think the buffer size as a whole needs to be fixed, no? And if 
so, we probably need some limit on message size to prevent "NOTIFY 
some_event 'a really long string'; " from filling up the buffer in one hit.

I'm also trying to figure out what a reasonable default buffer size will 
be. Thinking of the needs for which I will be providing (one listener, 
small names/payloads), 256Kb or 512Kb would be ample, possibly even 
excessive. But other users might have bigger needs.

cheers

andrew


Re: Notify enhancement

From
Alvaro Herrera
Date:
Andrew Dunstan wrote:

> Ok. But I think the buffer size as a whole needs to be fixed, no? And if 
> so, we probably need some limit on message size to prevent "NOTIFY 
> some_event 'a really long string'; " from filling up the buffer in one hit.
> 
> I'm also trying to figure out what a reasonable default buffer size will 
> be. Thinking of the needs for which I will be providing (one listener, 
> small names/payloads), 256Kb or 512Kb would be ample, possibly even 
> excessive. But other users might have bigger needs.

Make it configurable via GUC.

What will happen when the ring is full?  NOTIFY blocks?

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


Re: Notify enhancement

From
Andrew Dunstan
Date:
Alvaro Herrera wrote:
> Andrew Dunstan wrote:
>
>   
>> Ok. But I think the buffer size as a whole needs to be fixed, no? And if 
>> so, we probably need some limit on message size to prevent "NOTIFY 
>> some_event 'a really long string'; " from filling up the buffer in one hit.
>>
>> I'm also trying to figure out what a reasonable default buffer size will 
>> be. Thinking of the needs for which I will be providing (one listener, 
>> small names/payloads), 256Kb or 512Kb would be ample, possibly even 
>> excessive. But other users might have bigger needs.
>>     
>
> Make it configurable via GUC.
>   

That was my intention. The question was what the default should be.

> What will happen when the ring is full?  NOTIFY blocks?
>
>   
An error, I think, don't you? Blocking seems totally unacceptable.

cheers

andrew


Re: Notify enhancement

From
Alvaro Herrera
Date:
Andrew Dunstan wrote:
> Alvaro Herrera wrote:

> >What will happen when the ring is full?  NOTIFY blocks?
> >
> An error, I think, don't you? Blocking seems totally unacceptable.

Actually, to me the thing should be spilling to disk :-)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: Notify enhancement

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> Alvaro Herrera wrote:
>> What will happen when the ring is full?  NOTIFY blocks?
>> 
> An error, I think, don't you? Blocking seems totally unacceptable.

That seems like a curious answer.  Imagine the documentation:
"NOTIFY may fail for no reason whatsoever."  I'd vote for blocking.
Otherwise, apps have to treat NOTIFY as an unreliable communication
mechanism, which reduces its use-case to approximately nil.
        regards, tom lane


Re: Notify enhancement

From
Andrew Dunstan
Date:
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>   
>> Alvaro Herrera wrote:
>>     
>>> What will happen when the ring is full?  NOTIFY blocks?
>>>
>>>       
>> An error, I think, don't you? Blocking seems totally unacceptable.
>>     
>
> That seems like a curious answer.  Imagine the documentation:
> "NOTIFY may fail for no reason whatsoever."  I'd vote for blocking.
> Otherwise, apps have to treat NOTIFY as an unreliable communication
> mechanism, which reduces its use-case to approximately nil.
>
>
>   

OK, good point. I guess I'm a bit concerned about providing a possible 
DOS vector, but it could be either way.

How about Alvaro's suggestion of spilling to disk?

cheers

andrew





Re: Notify enhancement

From
Tom Lane
Date:
Gregory Stark <stark@enterprisedb.com> writes:
> This will run into the same issue that bidirectional pipes run into with
> deadlocks. The usual approach in Unix for dealing with this is having the
> application always -- even while blocked trying to write -- read any pending
> input and buffer it in user-space until it has enough to proceed. This may be
> hard to arrange in SQL? I think you would need a way for a PL/pgsql to escape
> a blocking write and read any pending notifications.

None of that is any different from the situation with sinval
messaging.

Note BTW that writes to the buffer will happen only in a very
circumscribed place (at COMMIT) so your worries about plpgsql
seem misplaced.
        regards, tom lane


Re: Notify enhancement

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> How about Alvaro's suggestion of spilling to disk?

Won't that reintroduce most of the problems we're hoping to get rid of
by removing the pg_listener table?  Certainly I'd not recommend putting
that into the first iteration.
        regards, tom lane


Re: Notify enhancement

From
"Florian G. Pflug"
Date:
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> How about Alvaro's suggestion of spilling to disk?
> 
> Won't that reintroduce most of the problems we're hoping to get rid of
> by removing the pg_listener table?  Certainly I'd not recommend putting
> that into the first iteration.

At least for my usecases, a blocking "notify" would be a serious 
problem. It basically means that one broken that stops calling
"listen ..." for whatever reason can cause all other clients to
stop..

With the current implementation, things just gradually slow down,
giving me time to react. So at least for me, blocking listen/notify
would be a regression...

Just my 0.02 eurocents, greetings
Florian Pflug


Re: Notify enhancement

From
Gregory Stark
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Andrew Dunstan <andrew@dunslane.net> writes:
>> Alvaro Herrera wrote:
>>> What will happen when the ring is full?  NOTIFY blocks?
>>> 
>> An error, I think, don't you? Blocking seems totally unacceptable.
>
> That seems like a curious answer.  Imagine the documentation:
> "NOTIFY may fail for no reason whatsoever."  I'd vote for blocking.
> Otherwise, apps have to treat NOTIFY as an unreliable communication
> mechanism, which reduces its use-case to approximately nil.

This will run into the same issue that bidirectional pipes run into with
deadlocks. The usual approach in Unix for dealing with this is having the
application always -- even while blocked trying to write -- read any pending
input and buffer it in user-space until it has enough to proceed. This may be
hard to arrange in SQL? I think you would need a way for a PL/pgsql to escape
a blocking write and read any pending notifications.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: Notify enhancement

From
Tom Lane
Date:
"Florian G. Pflug" <fgp@phlo.org> writes:
> At least for my usecases, a blocking "notify" would be a serious 
> problem. It basically means that one broken that stops calling
> "listen ..." for whatever reason can cause all other clients to
> stop..

Nonsense.  This is not about the clients, it's about the backends.
        regards, tom lane


Re: Notify enhancement

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> My current (possibly naive) thought is that I'll need two structures, 
> one of <pid, event> listeners and one of <pid, event, message> 
> notifications waiting to be picked up, each protected by a lock. In the 
> case of the second structure, we would just separate the event and the 
> message by a null byte. Does that seem reasonable?

No.  I think you should do it like sinval: the message ring carries
*all* messages and it's up to the readers to take or discard individual
messages.  A backend would read the buffer at reasonable intervals and
cache the messages it was interested in locally, for delivery to the
client after the next transaction end (similar to current semantics).
This way, the information about who is listening to what doesn't need to
be in shared memory, and there's only one configuration parameter, the
message ring buffer size, which the DBA can size based on estimates of
message traffic rate.
        regards, tom lane


Re: Notify enhancement

From
Andrew Dunstan
Date:
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>   
>> My current (possibly naive) thought is that I'll need two structures, 
>> one of <pid, event> listeners and one of <pid, event, message> 
>> notifications waiting to be picked up, each protected by a lock. In the 
>> case of the second structure, we would just separate the event and the 
>> message by a null byte. Does that seem reasonable?
>>     
>
> No.  I think you should do it like sinval: the message ring carries
> *all* messages and it's up to the readers to take or discard individual
> messages.  A backend would read the buffer at reasonable intervals and
> cache the messages it was interested in locally, for delivery to the
> client after the next transaction end (similar to current semantics).
>   

Are we keeping use of SIGUSR2 in this scheme?

> This way, the information about who is listening to what doesn't need to
> be in shared memory, and there's only one configuration parameter, the
> message ring buffer size, which the DBA can size based on estimates of
> message traffic rate.
>   

I don't understand how we decide that everybody who needs a given 
event+message has got it, if we don't know who (if anyone) is listening? 
How do we decide that we no longer need the info in the shmem buffer? 
Timeout? sinval issues a reset if the buffer becomes full, but we can't 
do that here.

I assume you don't intend that we keep one copy per backend regardless 
of whether or not it is listening.

I'm feeling a little dense here ... there must be something I'm not getting.

cheers

andrew



Re: Notify enhancement

From
Alvaro Herrera
Date:
Andrew Dunstan wrote:


> I don't understand how we decide that everybody who needs a given 
> event+message has got it, if we don't know who (if anyone) is listening? 
> How do we decide that we no longer need the info in the shmem buffer? 

Keep a pointer in shared memory for each listener backend, saying how
far it has scanned the ring?  There would be a single writing pointer,
so it's trivial to see when the ring is "full".

> Timeout? sinval issues a reset if the buffer becomes full, but we can't 
> do that here.

Just have NOTIFY block when the buffer is full, and maybe issue a
warning so that the user knows that he should increase the ring size.


> Are we keeping use of SIGUSR2 in this scheme?

What for?  Just protect the write pointer with a lwlock and have
listeners check whether somebody has written something.


Re: Notify enhancement

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Andrew Dunstan wrote:
>> I don't understand how we decide that everybody who needs a given 
>> event+message has got it, if we don't know who (if anyone) is listening? 
>> How do we decide that we no longer need the info in the shmem buffer? 

> Keep a pointer in shared memory for each listener backend, saying how
> far it has scanned the ring?  There would be a single writing pointer,
> so it's trivial to see when the ring is "full".

Right.  Read the code in src/backend/storage/ipc/sinvaladt.c, especially
SIInsertDataEntry, SIGetDataEntry, SIDelExpiredDataEntries.

>> Are we keeping use of SIGUSR2 in this scheme?

> What for?  Just protect the write pointer with a lwlock and have
> listeners check whether somebody has written something.

You do want something comparable to SIGUSR2 to prod active backends to
consume messages, in case they are busy doing a query and hence not
checking the ring.  I'm envisioning something like having the SIGUSR2
signal handler set a flag that's checked by CHECK_FOR_INTERRUPTS(),
and if set then ProcessInterrupts will go off and absorb messages.
Onlookers can tell who's falling behind by noting where their read
pointers are, and can issue SIGUSR2 to the laggards --- in particular,
any backend that finds itself unable to insert a NOTIFY into the ring
for lack of space can SIGUSR2 the laggards and then sleep a little.
        regards, tom lane


Re: Notify enhancement

From
Andrew Dunstan
Date:
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>   
>> Andrew Dunstan wrote:
>>     
>>> I don't understand how we decide that everybody who needs a given 
>>> event+message has got it, if we don't know who (if anyone) is listening? 
>>> How do we decide that we no longer need the info in the shmem buffer? 
>>>       
>
>   
>> Keep a pointer in shared memory for each listener backend, saying how
>> far it has scanned the ring?  There would be a single writing pointer,
>> so it's trivial to see when the ring is "full".
>>     
>
> Right.  Read the code in src/backend/storage/ipc/sinvaladt.c, especially
> SIInsertDataEntry, SIGetDataEntry, SIDelExpiredDataEntries.
>
>   
>>> Are we keeping use of SIGUSR2 in this scheme?
>>>       
>
>   
>> What for?  Just protect the write pointer with a lwlock and have
>> listeners check whether somebody has written something.
>>     
>
> You do want something comparable to SIGUSR2 to prod active backends to
> consume messages, in case they are busy doing a query and hence not
> checking the ring.  I'm envisioning something like having the SIGUSR2
> signal handler set a flag that's checked by CHECK_FOR_INTERRUPTS(),
> and if set then ProcessInterrupts will go off and absorb messages.
> Onlookers can tell who's falling behind by noting where their read
> pointers are, and can issue SIGUSR2 to the laggards --- in particular,
> any backend that finds itself unable to insert a NOTIFY into the ring
> for lack of space can SIGUSR2 the laggards and then sleep a little.
>
>     
>   

OK, thanks. Will study further.

cheers

andrew



Re: Notify enhancement

From
Andrew Dunstan
Date:
Tom Lane wrote:
>>> Are we keeping use of SIGUSR2 in this scheme?
>>>       
>
>   
>> What for?  Just protect the write pointer with a lwlock and have
>> listeners check whether somebody has written something.
>>     
>
> You do want something comparable to SIGUSR2 to prod active backends to
> consume messages, in case they are busy doing a query and hence not
> checking the ring.  I'm envisioning something like having the SIGUSR2
> signal handler set a flag that's checked by CHECK_FOR_INTERRUPTS(),
> and if set then ProcessInterrupts will go off and absorb messages.
> Onlookers can tell who's falling behind by noting where their read
> pointers are, and can issue SIGUSR2 to the laggards --- in particular,
> any backend that finds itself unable to insert a NOTIFY into the ring
> for lack of space can SIGUSR2 the laggards and then sleep a little.
>
>             
>   

I just wondered idly if we could piggyback on the existing 
WAKEN_CHILDREN/SIGUSR1 mechanism? It might mean we signal more children 
than necessary, but most won't have much to do anyway.

cheers

andrew



Re: Notify enhancement

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> I just wondered idly if we could piggyback on the existing 
> WAKEN_CHILDREN/SIGUSR1 mechanism?

I'd be inclined to keep it separate.  There isn't currently any benefit
to merging them, and I think it would convolute the code.  In particular
I'm not convinced that we want to do both sorts of cleanups at the same
times.

That's not to say that we might not want to use the postmaster as an
intermediary, but I'd still want it to be a separate signal.
        regards, tom lane