To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with rule and notification 
In-reply-to: <20031002021523.96380.qmail@web41015.mail.yahoo.com> 
References: <20031002021523.96380.qmail@web41015.mail.yahoo.com>
Comments: In-reply-to Theodore Petrosky <tedpet5@yahoo.com>
	message dated "Wed, 01 Oct 2003 19:15:23 -0700"
Date: Wed, 01 Oct 2003 23:10:59 -0400
Message-ID: <11905.1065064259@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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