Trigger function in a multi-threaded environment behavior
in [PgSql]
|
Prev: Trigger function in a multi-threaded environmentbehavior
Next: [HACKERS] VACUUM messages without newlines
From: Robert Haas on 1 Jun 2010 09:45 On Tue, Jun 1, 2010 at 7:10 AM, Hardik Belani <hardikbelani(a)gmail.com> wrote: > If we introduce postgres triggers (trigger functions) on some of the tables > to track insert/update/delete operations, (This is done by keeping and > updating a counter field for every insert, update and delete operation > performed on a set of tables inside trigger function) at this point, one or > more threads get stuck in lock while executing the query, to the extent that > sometimes even with the pgadmin, the database tables cannot be updated. Well, if you have a lot of concurrent backends trying to update the same counters, it is pretty reasonable to think that you're going to have some lock contention. There are a variety of ways to work around this - insert rows (instead of updating) and summarize them later, write the data to a flatfile somewhere and summarize it later, use the built-in statistics mechanisms, etc. Fundamentally the problem is that if transaction A is adding 1 to a counter and transaction B is adding 1 to a counter, one must wait for the other to commit. If A gets there first and updates the counter from, say, 37 to 38, B has to wait to see whether A commits or aborts. If A aborts, B must add 1 to 37; if A commits, B must add 1 to 38. Until A commits or aborts, B doesn't know what value it's adding 1 *to*. So every transaction updating that counter serializes on the counter itself. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |