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
