Thread: How do i make use of listen/notify properly

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

"Magnus Naeslund(f)" <mag@fbab.net> writes:
> 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 the trigger side
> if the updates to order are in a complex transaction the lock will be
> held for a very long time, blocking other transactions updating the
> same order and the listerner.

I don't see a need for all this locking.  You are only using
order_updates as a mechanism to tell the listener which orders to work
on, no?  Why don't you just do this: get rid of the unique index (all
indexes, likely) on order_updates, and unconditionally do

    INSERT INTO order_updates(order_id) values(NEW.id);
    NOTIFY order_updates;

in the trigger.  The listener changes to use DISTINCT:

    arr = select distinct orderid from order_updates;

(FOR UPDATE is a waste of cycles here too)

I'm assuming that it's relatively rare that many different transactions
touch the same orderid before the listener catches up.  Therefore, the
overhead of trying to avoid making duplicate entries in order_updates
is really counterproductive.

BTW, in any case you'll need to vacuum order_updates pretty frequently
to keep it from bloating.

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

Releasing locks before xact commit is generally evil; the reason being
that the xacts who acquire the lock after you release it wouldn't be
able to see the changes you made.  It will be a very hard sell to get
us to put an UNLOCK command into Postgres.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't see a need for all this locking.  You are only using
> order_updates as a mechanism to tell the listener which orders to work
> on, no?  Why don't you just do this: get rid of the unique index (all
> indexes, likely) on order_updates, and unconditionally do
>
> INSERT INTO order_updates(order_id) values(NEW.id);
> NOTIFY order_updates;
>
> in the trigger.  The listener changes to use DISTINCT:
>
> arr = select distinct orderid from order_updates;
>
> (FOR UPDATE is a waste of cycles here too)
>
> I'm assuming that it's relatively rare that many different
> transactions touch the same orderid before the listener catches up.
> Therefore, the overhead of trying to avoid making duplicate entries
> in order_updates is really counterproductive.
>

If pgpl could handle exceptions it wouldn't be too expensive, since the updates table at all times should be small.
Or is the unique check even then too expensive?
That way i would save an select to check existance.

The problem i wanted to avoid here is the case where the listening application isn't running.
The order_updates will grow huge if the caching app is down for say like one day.
If it's unique the order_updates can never grow more than order.

> BTW, in any case you'll need to vacuum order_updates pretty frequently
> to keep it from bloating.
>

Check.

> Releasing locks before xact commit is generally evil; the reason being
> that the xacts who acquire the lock after you release it wouldn't be
> able to see the changes you made.  It will be a very hard sell to get
> us to put an UNLOCK command into Postgres.

Well that's not what i really want, i would like to nest transactions instead, so that i can keep down the locktime for
aselect for update for example. 
But that won't work in postgresql right?
I hope it'll soon (i see it in the todo).

I'm pretty sure i'll be "forced" (performance or featurewise) to do it the way you describe, and just add a cronjob
thatclears the table every day or so if it grows over a limit, but i don't like that the updates table can grow forever
ifnothing is done. 

> regards, tom lane

Cheers

Magnus