On Wed, Mar 19, 2008 at 11:37:16AM +1030, Tyler, Mark wrote:
> > Oh, you can, you can calculate the name of the NOTIFY dynamically
> > in the trigger sending the notify, for example embedding a primary key
> value.
>
> I don't understand how this can work. Surely my subscriber applications
> have to listen to the same notification name that the publisher is going
> to notify on (unless I am missing something about how NOTIFY/LISTEN
> works). Yes I know you can construct that name dynamically prior to
> issuing the NOTIFY but if you are changing the notification name then
> how do the subscribers know what names to listen on? Do you have a quick
> example of your suggestion?
Sure. GNUmed is a medical record application. There is
always one particular patient "active" in any given
frontend. The frontend knows the primary key of this
patient.
During database setup we use a script to create notification
triggers for a bunch of tables which directly or indirectly
(via multi-step foreign key paths) link to a patient primary
key. The NOTIFY names generated dynamically are of the
format like, say,
allergy_mod::<pk of patient>
where <pk of patient> is replaced by the respective primary
key (allergy_mod stands for "modification to allergy
table").
Now assume the frontend activates patient 12. It will then
issue LISTEN commands for, among others, "allergy_mod::12".
Assume a process INSERTing, UPDATEing, or DELETEing data
in the allergy table. The notification triggers calculate
which patient a modified row belongs to. Let's say it was
about patient 12. So the triggers sends a NOTIFY
"allergy_mod::12" which the frontend receives.
It's great fun to watch people from all across the globe
change data on test patients in our public test database.
The frontend is written to display such updates in realtime
(well, whatever it takes to get the notification delivered).
:-)
We do, of course, know which PKs we are interested in at any
given time. Without that knowledge this scheme wouldn't work.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
