Thread: help with rule and notification
I don't know if this is the correct forum for this question but I will start here... I have a job tracking system that I am developing with postgresql and mac os x. I have all the pieces in place (mostly) but i am having a problem with notify.. I am trying to set up things so that two (or more) people can view the same job data, and if one client updates the data the others will be notified and can update their displays. I got the notify to work (it wasn't too difficult) but now I am trying to figure out the logic. I mean the only examples I see have rules that say.. update table1, then the rule updates table2 and sends a notify to anyone listening. The information passed in the notify is a reference to the second table. Ok so far. I am having a problem with the second table update. When I update table1 (update table1 set info ='info' where jobno = '10023') how do I pick up the jobno variable in my rule? something like create rule r1 as on update to table1 do (update table2 set jobno = table1.jobno; notify table2;) so anyone listening for notifications on table2 can ask table2 for the jobno that was updated. then if they were viewing that jobno, update their display. if not just ignore the notify. maybe i'm going about this all wrong and someone can point me in a better direction. I am open to any solution... Thanks.. Ted __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
> so anyone listening for notifications on table2 can > ask table2 for the jobno that was updated. then if > they were viewing that jobno, update their display. if > not just ignore the notify. Pardon me if I make your focus blur! I believe the implementation for such requirement in an application server in 3 tier environment is not too difficult. Regards, CN -- http://www.fastmail.fm - Or how I learned to stop worrying and love email again
Theodore Petrosky <tedpet5@yahoo.com> writes:
> create rule r1 as on update to table1 do (update
> table2 set jobno = table1.jobno; notify table2;)
> so anyone listening for notifications on table2 can
> ask table2 for the jobno that was updated. then if
> they were viewing that jobno, update their display. if
> not just ignore the notify.
At the moment, a NOTIFY cannot convey very much information beyond
"something happened, better look to see what". (There have been
discussions about making the notification carry more info, see the
pgsql-hackers archives.) In a previous lifetime I had a moderately
complex application that used NOTIFY to trigger display updates for
multiple client apps viewing a shared database. If memory serves,
I did it by having a "sequence number" column that was assigned from
a nextval() operation on every insert or update. In addition the
inserts and updates triggered NOTIFY events. When the clients
got NOTIFY they'd do "select from tab where seqno > last-seqno-seen"
and then update their local state from the rows they got back.
This solution doesn't directly handle deletes. I think I finessed the
problem by treating "delete" as "update to a 'dead' state" and only
cleaning out the dead rows later.
regards, tom lane
ok if the only info I can pass is that the notify happened and then I need to go look at the notifying table is it possible to create a rule that on update of jobstable insert into notifytable the jobnumber has there been any real discussion about adding this to the todo list? I was really pretty excited about using notify,... Ted --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Theodore Petrosky <tedpet5@yahoo.com> writes: > > create rule r1 as on update to table1 do (update > > table2 set jobno = table1.jobno; notify table2;) > > > so anyone listening for notifications on table2 > can > > ask table2 for the jobno that was updated. then if > > they were viewing that jobno, update their > display. if > > not just ignore the notify. > > At the moment, a NOTIFY cannot convey very much > information beyond > "something happened, better look to see what". > (There have been > discussions about making the notification carry more > info, see the > pgsql-hackers archives.) In a previous lifetime I > had a moderately > complex application that used NOTIFY to trigger > display updates for > multiple client apps viewing a shared database. If > memory serves, > I did it by having a "sequence number" column that > was assigned from > a nextval() operation on every insert or update. In > addition the > inserts and updates triggered NOTIFY events. When > the clients > got NOTIFY they'd do "select from tab where seqno > > last-seqno-seen" > and then update their local state from the rows they > got back. > > This solution doesn't directly handle deletes. I > think I finessed the > problem by treating "delete" as "update to a 'dead' > state" and only > cleaning out the dead rows later. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com