Skip site navigation (1) Skip section navigation (2)

How do i make use of listen/notify properly

From: "Magnus Naeslund(f)" <mag(at)fbab(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How do i make use of listen/notify properly
Date: 2002-09-27 15:13:23
Message-ID: 114001c26638$6bfb2ca0$f80c0a0a@mnd (view raw or whole thread)
Lists: pgsql-general
I'm developing a notification mechanism that will be pretty heavily used for a cache. So i wan't it nearly lockless mechanism run 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
  doit integer;

   doit := 1;
      IF NEW.* <> OLD.* THEN // you get the point
        doit := 1;
      END IF;

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

 return NULL;
' LANGUAGE 'plpgsql';

So the listener does this (in strange pseudocode):

    arr = new array[];
    arr = select orderid from order_updates for update;
    delete from order_updates where id in arr;
    commit; //Now all entries are released

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 table at maximum.

The locking (FOR UPDATE) whould (i hope?) make it impossible that i miss an entry. I could get "empty" notifies but that doesn'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 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.

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 UPDATE selected 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 around the 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?


 Programmer/Networker [|] Magnus Naeslund


pgsql-general by date

Next:From: scott.marloweDate: 2002-09-27 15:16:03
Subject: Re: Performance while loading data and indexing
Previous:From: Andriy TkachukDate: 2002-09-27 14:58:05
Subject: Re: query speed depends on lifetime of frozen db?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group