From: Tom Lane <tgl@sss.pgh.pa.us>
To: Theodore Petrosky <tedpet5@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with rule and notification
Date: Wed, 01 Oct 2003 23:10:59 -0400
Message-ID: <11905.1065064259@sss.pgh.pa.us> (raw)
In-Reply-To: <20031002021523.96380.qmail@web41015.mail.yahoo.com>
References: <20031002021523.96380.qmail@web41015.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, tedpet5@yahoo.com
Subject: Re: help with rule and notification
In-Reply-To: <11905.1065064259@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox