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 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
