How do i make use of listen/notify properly - Mailing list pgsql-general

Hello,
I'm developing a notification mechanism that will be pretty heavily used for a cache. So i wan't it nearly lockless
mechanismrun from within a trigger. 

Hang on, this is an lengthy message but there might be some points in the end :)

I wan't this:

We have a order table "order" like this:

create table order (id SERIAL, integer custid, int artnr);

And a trigger like this:

create trigger order_trig AFTER INSERT OR UPDATE ON order FOR EACH ROW EXECUTE PROCEDURE check_order_notify();

We also have a table called "order_updates" :
create table order_updates(integer orderid UNIQUE);

The trigger function is something like this:

create function xorder1_autonotify() RETURNS OPAQUE AS
'declare
  doit integer;
 begin

 doit:=0;
 IF TG_OP = ''INSERT'' THEN
   doit := 1;
 ELSE
      IF NEW.* <> OLD.* THEN // you get the point
        doit := 1;
      END IF;
 END IF;

 IF doit = 1 THEN
    select order_id from order_updates where id = NEW.id for update; //Lock it
    IF NOT EXISTS THEN
        INSERT INTO order_updates(order_id) values(NEW.id);
        NOTIFY order_updates;
    END IF;
 END IF;

 return NULL;
 end;
' LANGUAGE 'plpgsql';


So the listener does this (in strange pseudocode):

for(;;){
    blockForListen();
    arr = new array[];
    begin;
    arr = select orderid from order_updates for update;
    delete from order_updates where id in arr;
    commit; //Now all entries are released
    updateCache(arr);
}

I want it this way because it should give me the following:

The order_updates table will not be filled with a lot of rows if the listener is down, it'll be the same count as order
tableat maximum. 

The locking (FOR UPDATE) whould (i hope?) make it impossible that i miss an entry. I could get "empty" notifies but
thatdoesn't matter. 

The problems are:

The locks are held until transaction ends.
The listening side holds the lock for a very short time because it knows when it begin/commits, and that's good, but on
thetrigger side if the updates to order are in a complex transaction the lock will be held for a very long time,
blockingother transactions updating the same order and the listerner. 

This could be fixed in two ways:

1) If plpgsql supported exception it could do a INSERT and catch an eventual unique constraint exception.
That would make the listen side the only one doing FOR UPDATE. I'm counting on that a INSERT with the same ID as an FOR
UPDATEselected row would block until the FOR UPDATE is done (and the row is deleted). 

2) If the LOCK statement had an counterpart, so that i can lock stuff in the trigger for a small time only, wrapped
aroundthe select IF NOT EXISTS insert code. 

How are the you guys solving this problem?
Am i going about this in the wrong way?

How does you highperf caches/listen/notify stuff work in an secure and FAST manner?

Magnus

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 Programmer/Networker [|] Magnus Naeslund
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-