Re: How do i make use of listen/notify properly
От | Magnus Naeslund(f) |
---|---|
Тема | Re: How do i make use of listen/notify properly |
Дата | |
Msg-id | 118a01c26655$e19e0c80$f80c0a0a@mnd обсуждение исходный текст |
Ответ на | How do i make use of listen/notify properly ("Magnus Naeslund(f)" <mag@fbab.net>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: