Message-ID: <20031002123409.10401.qmail@web41008.mail.yahoo.com>
Date: Thu, 2 Oct 2003 05:34:08 -0700 (PDT)
From: Theodore Petrosky <tedpet5@yahoo.com>
Subject: Re: help with rule and notification 
To: pgsql-sql@postgresql.org
In-Reply-To: <11905.1065064259@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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