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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 a select 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 that clears the table every day or so if it grows over a limit, but i don't like that the updates table can grow forever if nothing is done.

> regards, tom lane

Cheers

Magnus