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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
