I would like to start a dialogue and hear general feedback about the use
of constraint triggers in postgres (8.4.5).
Our overall issue is that using general triggers is causing slow inserts
(from locking issues) in our database. Here are some details:
A little background (jboss/j2ee/hibernate/linux).....
We have 3 basic triggers on a particular database table - one for
inserts, one for updates & another for deletes and they keep track of a
"granule count" that is used in reporting. This field (gracount) is
stored in another table called dataset. An example of the insert
trigger/function is as follows:
CREATE TRIGGER increment_dataset_granule_count
FOR EACH ROW
EXECUTE PROCEDURE increment_dataset_granule_count();
CREATE OR REPLACE FUNCTION increment_dataset_granule_count()
RETURNS trigger AS
IF NEW.visible_id != 5 THEN
SET gracount = gracount + 1
WHERE dataset.inv_id = NEW.inv_id;
LANGUAGE plpgsql VOLATILE
ALTER FUNCTION increment_dataset_granule_count() OWNER TO jboss;
What we found was that when these triggers were fired we saw locking
issues that slowed down performance dramatically to inserts into the
inventory table (the table where the triggers are located). You could
see the inserts sit and wait by looking at the pg_stat_activity table.
Within our app, the trigger was invoked within the same hibernate
transaction that a stateless session bean was using to persist/merge the
granule (inventory). Subsequently, in the same transaction, a EJB MBean
was merging the dataset changes creating kind of a "dead lock" on the
Our first try to solve this problem has been to convert these triggers
into a constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED
flags. This, we are finding, is forcing the trigger function to run
after the triggering transaction is completed. We believe this will fix
our locking problem and hopefully speed up our inserts again.
Any comments or past experiences would certainly be helpful!
NASA/Langley Research Center
Hampton, Virginia 23681
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2011-09-26 17:07:51|
|Subject: Re: Performance Anomaly with "col in (A,B)" vs. "col = A OR col = B" ver. 9.0.3 |
|Previous:||From: Biswa||Date: 2011-09-26 14:19:16|
|Subject: Re: How to find record having % as part of data.|