Re: help with rule and notification

Theodore Petrosky <tedpet5(at)yahoo(dot)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