William Scott Jordan wrote:
> Hi Andrew,
>
> That's a very good guess. We are in fact updating this table multiple
> times within the same triggered function, which is being called on an
> INSERT. Essentially, we're using this to keep a running total of the
> number of rows being held in another table.
This is the worst way to go about keeping running totals; it would be
far better to have a table holding a "last aggregated value" and deltas
from that; to figure out the current value of the counter, add the last
value plus/minus the deltas (I figure you'd normally have one +1 for
each insert and one -1 for each delete; update is an exercise to the
reader). You have another process that runs periodically and groups the
deltas to generate an up-to-date "last aggregated value", deleting the
deltas.
This way you should have little deadlock problems if any, because no
transaction needs to wait for another one to update the counter.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
