Thread: Problem with async notifications of table updates
Hi,
I am trying to set up a PostGreSQL database to send asynchronous notifications when certain inserts or updates are performed on the tables. The idea is I want to have publish / subscirbe model with the database in the centre as the information hub. An application will insert a record into a table and then a notification message is sent to all registered subscribers telling them that record number X has been added to table Y. Each subscriber can then chose to retrieve the record or ignore the notification. This should be near real-time (< 0.5 sec from insert / update to notification reception).
To do the notification I have ported the Spread (www.spread.org) interface for MySQL to Postgres (actually only the send_mesg() part of it). I then have a trigger function which calls the send_mesg() function on an insert or update to the table. All good -- except that when another application receives the message and queries the table the record that caused the notification is not there. It would appear that it only becomes available AFTER the trigger function that fired the message returns.
So the question is - how can I get my trigger function to flush the row so that I can be sure it is available for use prior to the return of the trigger function? Alternatively - how can I tell the trigger function to only execute the send_mesg() after the row is available?
I have not used the LISTEN / NOTIFY interface because:
a) It does not easily support sending any information (yes I know you can set up another table and insert a pointer to the record however that is far from ideal)
b) There is no guarantee on message delivery. Spread allows you to ensure that message is delivered.
Thanks in advance - Mark
IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email.
Tyler, Mark escribió: > > Hi, > > I am trying to set up a PostGreSQL database to send asynchronous > notifications when certain inserts or updates are performed on the > tables. The idea is I want to have publish / subscirbe model with the > database in the centre as the information hub. An application will > insert a record into a table and then a notification message is sent > to all registered subscribers telling them that record number X has > been added to table Y. Each subscriber can then chose to retrieve the > record or ignore the notification. This should be near real-time (< > 0.5 sec from insert / update to notification reception). > > To do the notification I have ported the Spread (_www.spread.org_ > <file://www.spread.org>) interface for MySQL to Postgres (actually > only the send_mesg() part of it). I then have a trigger function which > calls the send_mesg() function on an insert or update to the table. > All good -- except that when another application receives the message > and queries the table the record that caused the notification is not > there. It would appear that it only becomes available AFTER the > trigger function that fired the message returns. > > So the question is - how can I get my trigger function to flush the > row so that I can be sure it is available for use prior to the return > of the trigger function? Alternatively - how can I tell the trigger > function to only execute the send_mesg() after the row is available? > I am almost sure you've defined a BEFORE trigger and you need and AFTER trigger, so it's fired after commiting.
Attachment
Rodrigo Gonzalez wrote:
> I am almost sure you've defined a BEFORE trigger and
> you need and AFTER trigger, so it's fired after commiting.
No - I am definitely using an AFTER trigger. Following is a simplified
version of what I am trying to do.
/* messages - log messages */
CREATE TABLE messages
(id SERIAL PRIMARY KEY,
time TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
severity_level INTEGER NOT NULL,
severity TEXT NOT NULL, /*
ENUM('Info','Warning','Critical') */
facility CHAR(10) NOT NULL,
msg TEXT NOT NULL);
CREATE OR REPLACE FUNCTION message_alert() RETURNS TRIGGER AS
$message_alert$
BEGIN
PERFORM send_mesg('notify_channel', 'DB:Log:' || NEW.id
|| ':');
RETURN NULL;
END;
$message_alert$ LANGUAGE plpgsql;
CREATE TRIGGER message_alert AFTER INSERT ON messages
FOR EACH ROW
EXECUTE PROCEDURE message_alert();
I have a Python program which is waiting on the message being sent via
send_mesg(). The message is received correctly but if I do an immediate
"SELECT msg FROM messages WHERE id=<the message id that came via the
send_msg() call>;" then it returns a NULL set. If I put a small sleep
between receiving the message and doing the select then I get the data.
What I want to do is to guarantee that the row is available for
selection prior to sending the message.
Mark
IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of
section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender
anddelete the email.
"Tyler, Mark" <Mark.Tyler@dsto.defence.gov.au> writes:
> What I want to do is to guarantee that the row is available for
> selection prior to sending the message.
You cannot do that with an AFTER trigger, because whatever it does
necessarily happens before your transaction commits. I suggest
rethinking your dislike of NOTIFY.
regards, tom lane
Tom Lane wrote: >"Tyler, Mark" <Mark.Tyler@dsto.defence.gov.au> writes: >> What I want to do is to guarantee that the row is available for >> selection prior to sending the message. > > You cannot do that with an AFTER trigger, because whatever it > does necessarily happens before your transaction commits. I somehow suspected that this was the answer. It would be nice to have some sort of FINALLY style of clause for the trigger which was able to be initiated after the transaction was committed. Of course there would be very large restrictions on what sort of things could be done in such a clause. Clearly NOTIFY itself works around this very problem. I have not looked at the code but I suspect the NOTIFY command sets a flag that tells the server to fire the notification as soon as the transaction commits - thus the command can be inside the trigger context but have an effect after the trigger completes. > I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me to put far more smarts and state into the subscriber applications. This is because I cannot pass any information with the NOTIFY apart from the fact that "something happened". Due to this restriction my subscriber apps would have to go and look up some secondary table to get sufficient information to construct the real query. That is just plain ugly in my view. Secondly, the lack of any delivery guarantee means my subscriber applications may miss event notifications. This is a very bad thing for my particular application. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender anddelete the email.
"Tyler, Mark" <Mark.Tyler@dsto.defence.gov.au> writes:
> Secondly, the lack of any delivery guarantee means my subscriber
> applications may miss event notifications. This is a very bad thing for
> my particular application.
What makes you think NOTIFY doesn't guarantee delivery? If the
transaction commits then the notify update has happened.
Perhaps more to the point, have you reflected on the fact that your
technique has the opposite problem? Once you've given the message
to Spread, it'll deliver it whether your transaction subsequently
commits or not.
If you're really intent on re-inventing NOTIFY, you could use the
same synchronization trick it does: take out a lock on some otherwise
unused table just before sending the message, and have recipients lock
the same table on receipt of the message, before they go looking for
any effects in the database. The NOTIFY-side lock is held past commit
of its transaction, so once recipients can lock the table they must be
able to see the results of the NOTIFY's transaction. This is not
insanely great from a concurrency standpoint of course, but as long as
you keep the lock hold durations short it's workable.
regards, tom lane
Tom Lane wrote: >"Tyler, Mark" <Mark.Tyler@dsto.defence.gov.au> writes: >> Secondly, the lack of any delivery guarantee means my subscriber >> applications may miss event notifications. This is a very bad thing >> for my particular application. > > What makes you think NOTIFY doesn't guarantee delivery? If the > transaction commits then the notify update has happened. The description of NOTIFY in the manual led me to think this - especially the bit "if the same notification name is signaled multiple times in quick succession, recipients might get only one notification event". Re-reading the sentence I can see that I should be interpreting it as "guaranteed notification of one of a stream of signals". Is there any chance of loosing a notification if it occurs when I am handling a previous signal? I guess not but I am not that used to signal behaviour. My original thought was to use a single NOTIFY channel for notifications of all changes and then have some secondary table to carry the payload of the signalled message. If I don't get a notify for every change then I have to do more work at the app end to try and work out what actually happened. > Perhaps more to the point, have you reflected on the fact that your > technique has the opposite problem? Once you've given the message > to Spread, it'll deliver it whether your transaction subsequently > commits or not. Which is why I would like to be able to fire the Spread message after the transaction commits. If I can do that then all is good (I think). Mind you if the transaction does not commit then that is a relatively easy case to handle - any recipients of the message will just get a NULL set when they do a query on the key in the message. Given that I have to have that path in my subscriber apps anyway it is no overhead. > If you're really intent on re-inventing NOTIFY, you could use the > same synchronization trick it does: take out a lock on some > otherwise unused table just before sending the message, and have > recipients lock the same table on receipt of the message, before > they go looking for any effects in the database. The NOTIFY-side > lock is held past commit of its transaction, so once recipients can > lock the table they must be able to see the results of the NOTIFY's > transaction. This is not insanely great from a concurrency standpoint > of course, but as long as you keep the lock hold durations short it's workable. Thanks for the explanation of how NOTIFY and LISTEN work. I could take the same approach as you suggest but it would again put too much database-trickery into the subscriber apps for my taste. There is no a big advantage between doing this and using NOTIFY directly. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender anddelete the email.
On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: >> I suggest rethinking your dislike of NOTIFY. > > I have thought very hard about using NOTIFY for this but it has two > large problems (from my point of view). The first is that it forces me > to put far more smarts and state into the subscriber applications. > This > is because I cannot pass any information with the NOTIFY apart from > the > fact that "something happened". Due to this restriction my subscriber > apps would have to go and look up some secondary table to get > sufficient > information to construct the real query. That is just plain ugly in my > view. You will have the same problem if you want to send a message about a record change in combination with transactions. You can either send a message about an /uncommitted/ transaction and include what record changed, /or/ you send a message about a /committed/ transaction which possibly changed multiple of those records - in which case there's no possibility to send a single id along with your message. You could try sending a set after commit, equivalent to how INSERT RETURNING works, but you'll have to marshall those id's into your message yourself. And that's pretty similar to putting those id's in a table and fetch them from your application - it's just moving the work around. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47df69e69781418010441!
On Tue, Mar 18, 2008 at 01:28:36PM +1030, Tyler, Mark wrote: > This > is because I cannot pass any information with the NOTIFY apart from the > fact that "something happened". Oh, you can, you can calculate the name of the NOTIFY dynamically in the trigger sending the notify, for example embedding a primary key value. This may or may not be of help in your application though. The whole issue is on the TODO list anyhow, AFAIR. > Secondly, the lack of any delivery guarantee means my subscriber Only the *quantity* is not guaranteed. You will always get *one* of several notifies of the same type (provided you listen). Again, this may or may not be sufficient for your application but it's certainly not "lack of *any* guarantee". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Mar 17, 2008, at 10:58 PM, Tyler, Mark wrote: >> I suggest rethinking your dislike of NOTIFY. > > I have thought very hard about using NOTIFY for this but it has two > large problems (from my point of view). The first is that it forces me Wait a while and you will learn to detest Spread, too.
Vivek Khera wrote: > Wait a while and you will learn to detest Spread, too. I know this is probably off-topic for this group but why do you say this? I guess I don't want to go too far down a particular route if there are big traps waiting so I am interested in the basis for your comment. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender anddelete the email.
Karsten Hilbert wrote: > On Tue, Mar 18, 2008 at 01:28:36PM +1030, Tyler, Mark wrote: >> This >> is because I cannot pass any information with the NOTIFY apart from >> the fact that "something happened". > > Oh, you can, you can calculate the name of the NOTIFY dynamically > in the trigger sending the notify, for example embedding a primary key value. I don't understand how this can work. Surely my subscriber applications have to listen to the same notification name that the publisher is going to notify on (unless I am missing something about how NOTIFY/LISTEN works). Yes I know you can construct that name dynamically prior to issuing the NOTIFY but if you are changing the notification name then how do the subscribers know what names to listen on? Do you have a quick example of your suggestion? Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender anddelete the email.
Alban Hertroys wrote: >On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: >> I suggest rethinking your dislike of NOTIFY. >> >> I have thought very hard about using NOTIFY for this but it has two >> large problems (from my point of view). The first is that it forces me >> to put far more smarts and state into the subscriber applications. >> This >> is because I cannot pass any information with the NOTIFY apart from >> the fact that "something happened". Due to this restriction my >> subscriber apps would have to go and look up some secondary table to >> get sufficient information to construct the real query. That is just >> plain ugly in my view. > > You will have the same problem if you want to send a message about a > record change in combination with transactions. You can either send > a message about an /uncommitted/ transaction and include what record > changed, /or/ you send a message about a /committed/ transaction which > possibly changed multiple of those records - in which case there's no > possibility to send a single id along with your message. I think you are suggesting that instead of having the trigger function FOR EACH ROW it should be FOR EACH STATEMENT. In the case of an AFTER trigger using a FOR EACH STATEMENT clause; does it get fired /after/ the commit? (the documentation for CREATE TRIGGER does not really specify) That may work for me if I can guarantee that the publisher only ever changes single rows for notifiable tables in a transaction. > You could try sending a set after commit, equivalent to how INSERT RETURNING > works, but you'll have to marshall those id's into your message yourself. And > that's pretty similar to putting those id's in a table and fetch them from your > application - it's just moving the work around. I prefer to put as much of the knowledge about the technicalities of the process into the publishing side of the system keeping the interface the subscriber apps have to deal with relatively simple. Anything I can do, within reason, to help this is good. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender anddelete the email.
On Mar 19, 2008, at 2:35 AM, Tyler, Mark wrote: > Alban Hertroys wrote: >> On Mar 18, 2008, at 3:58 AM, Tyler, Mark wrote: >>> I suggest rethinking your dislike of NOTIFY. >>> >>> I have thought very hard about using NOTIFY for this but it has two >>> large problems (from my point of view). The first is that it forces > me >>> to put far more smarts and state into the subscriber applications. >>> This >>> is because I cannot pass any information with the NOTIFY apart from >>> the fact that "something happened". Due to this restriction my >>> subscriber apps would have to go and look up some secondary table to >>> get sufficient information to construct the real query. That is just >>> plain ugly in my view. >> >> You will have the same problem if you want to send a message about a >> record change in combination with transactions. You can either send >> a message about an /uncommitted/ transaction and include what record >> changed, /or/ you send a message about a /committed/ transaction >> which > >> possibly changed multiple of those records - in which case there's no >> possibility to send a single id along with your message. > > I think you are suggesting that instead of having the trigger function > FOR EACH ROW it should be FOR EACH STATEMENT. In the case of an AFTER > trigger using a FOR EACH STATEMENT clause; does it get fired / > after/ the > commit? (the documentation for CREATE TRIGGER does not really specify) I think FOR EACH ROW or FOR EACH STATEMENT will make little or no difference here, unless you can guarantee that you'll never change more than one row in a transaction. Rows changed in a transaction are usually in different tables, that's what transactions are for after all - "Transaction" is a good name for what it does. As an example in an employee database, if you want to send a NOTIFY when an employee's salary gets INSERTed, the transaction will usually also contain the INSERT of the employee record. You want those two statements together in a transaction, or you risk creating an employee without a salary (because the application crashed, the connection was lost, the office got flooded, etc) - and be sure that employee will be grumpy about that! For the difference between ON EACH ROW and ON EACH STATEMENT in triggers, that's best illustrated by queries such as INSERT INTO table1 (column1, column2, fkey1) SELECT 'value1', 'value2', id FROM table2 WHERE column1 = 'value3'; This single statement could result in multiple rows being inserted, and so you can either choose to fire a trigger for each inserted row or for the entire statement at once. That said, if you'll need to batch your statements for a client to be notified at the end of the transaction, sending a notify with an ON EACH STATEMENT trigger will yield NOTIFY's at the same moment in time (at COMMIT). > That may work for me if I can guarantee that the publisher only ever > changes single rows for notifiable tables in a transaction. That depends on the importance of your data's integrity in relation to transactions, as per the employee example above. I can't say anything about that. >> You could try sending a set after commit, equivalent to how INSERT > RETURNING >> works, but you'll have to marshall those id's into your message > yourself. And >> that's pretty similar to putting those id's in a table and fetch them > from your >> application - it's just moving the work around. > > I prefer to put as much of the knowledge about the technicalities > of the > process into the publishing side of the system keeping the > interface the > subscriber apps have to deal with relatively simple. Anything I can > do, > within reason, to help this is good. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47e0a7459781583513226!
Tom Lane wrote: > "Tyler, Mark" <Mark.Tyler@dsto.defence.gov.au> writes: > >> What I want to do is to guarantee that the row is available for >> selection prior to sending the message. >> > > You cannot do that with an AFTER trigger, because whatever it does > necessarily happens before your transaction commits. I suggest > rethinking your dislike of NOTIFY. > What if the trigger is a constraint trigger that is deferred? http://www.postgresql.org/docs/8.3/interactive/sql-createconstraint.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
On Wed, Mar 19, 2008 at 11:37:16AM +1030, Tyler, Mark wrote:
> > Oh, you can, you can calculate the name of the NOTIFY dynamically
> > in the trigger sending the notify, for example embedding a primary key
> value.
>
> I don't understand how this can work. Surely my subscriber applications
> have to listen to the same notification name that the publisher is going
> to notify on (unless I am missing something about how NOTIFY/LISTEN
> works). Yes I know you can construct that name dynamically prior to
> issuing the NOTIFY but if you are changing the notification name then
> how do the subscribers know what names to listen on? Do you have a quick
> example of your suggestion?
Sure. GNUmed is a medical record application. There is
always one particular patient "active" in any given
frontend. The frontend knows the primary key of this
patient.
During database setup we use a script to create notification
triggers for a bunch of tables which directly or indirectly
(via multi-step foreign key paths) link to a patient primary
key. The NOTIFY names generated dynamically are of the
format like, say,
allergy_mod::<pk of patient>
where <pk of patient> is replaced by the respective primary
key (allergy_mod stands for "modification to allergy
table").
Now assume the frontend activates patient 12. It will then
issue LISTEN commands for, among others, "allergy_mod::12".
Assume a process INSERTing, UPDATEing, or DELETEing data
in the allergy table. The notification triggers calculate
which patient a modified row belongs to. Let's say it was
about patient 12. So the triggers sends a NOTIFY
"allergy_mod::12" which the frontend receives.
It's great fun to watch people from all across the globe
change data on test patients in our public test database.
The frontend is written to display such updates in realtime
(well, whatever it takes to get the notification delivered).
:-)
We do, of course, know which PKs we are interested in at any
given time. Without that knowledge this scheme wouldn't work.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Mar 20, 2008 at 12:40:46PM +0100, Karsten Hilbert wrote: > It's great fun to watch people from all across the globe > change data on test patients in our public test database. > The frontend is written to display such updates in realtime > (well, whatever it takes to get the notification delivered). All I can say is: awesome! Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
On Thu, Mar 20, 2008 at 02:08:13PM +0100, Martijn van Oosterhout wrote:
> > It's great fun to watch people from all across the globe
> > change data on test patients in our public test database.
> > The frontend is written to display such updates in realtime
> > (well, whatever it takes to get the notification delivered).
>
> All I can say is: awesome!
Well, it's a great database engine.
BTW, we've also reimplemented gettext in SQL such
that you can run
select _(description) from diagnosis
where code = 'F43.0' and coding_system = 'icd10';
and get a description translated into your language.
;-)
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346