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

"Magnus Naeslund(f)" <mag(at)fbab(dot)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