Notify enhancement
Andrew Dunstan
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Alvaro Herrera
> 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Andrew Dunstan
> 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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
Matteo Beccati
> 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
Guillaume Lelarge
> 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.
Greg Sabino Mullane
-----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 gr...@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-----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
Tom Lane
> 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
Greg Sabino Mullane
-----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 gr...@turnstep.com
PGP Key: 0x14964AC8 200612031856
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFc2WSvJuQZxSWSsgRAkQOAJ93c1ZiTx9h9Bho0YLpFmNwrRHVzACgp/tl
hi1sQRbEcFRpMNNbQQ7xnhM=
=LAI2
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Tom Lane
> 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
Andrew Dunstan
>
> 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
Andrew Dunstan
> "Andrew Dunstan" <and...@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
Alvaro Herrera
> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Andrew Dunstan
> 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
Alvaro Herrera
> 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.
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Andrew Dunstan
> Andrew Dunstan <and...@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
Florian G. Pflug
> Andrew Dunstan <and...@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
Gregory Stark
"Tom Lane" <t...@sss.pgh.pa.us> writes:
> Andrew Dunstan <and...@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
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
Tom Lane
> 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
---------------------------(end of broadcast)---------------------------
Andrew Dunstan
> Andrew Dunstan <and...@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
---------------------------(end of broadcast)---------------------------
Alvaro Herrera
> 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.
Tom Lane
> 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
Andrew Dunstan
OK, thanks. Will study further.
cheers
andrew
---------------------------(end of broadcast)---------------------------
Andrew Dunstan
>>> 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
---------------------------(end of broadcast)---------------------------
Tom Lane
> 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.