From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Hardik Belani <hardikbelani(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Trigger function in a multi-threaded environment behavior |
Date: | 2010-06-01 13:45:29 |
Message-ID: | AANLkTimQsgv0aiO2ivVQ6r1Pte6v7gXgGDMRP7i3C2MQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Jun 1, 2010 at 7:10 AM, Hardik Belani <hardikbelani(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2010-06-01 13:53:16 | Re: [RFC] A tackle to the leaky VIEWs for RLS |
Previous Message | Bruce Momjian | 2010-06-01 13:37:30 | Re: is_absolute_path incorrect on Windows |