Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 071A1B5DBEE for ; Mon, 26 Sep 2011 14:17:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 18671-05 for ; Mon, 26 Sep 2011 17:17:31 +0000 (UTC) X-Greylist: delayed 00:24:48.54945 by SQLgrey-1.8.0-rc2 Received: from ndjsnpf02.ndc.nasa.gov (ndjsnpf02.ndc.nasa.gov [198.117.1.122]) by mail.postgresql.org (Postfix) with ESMTP id 60414B5DBF2 for ; Mon, 26 Sep 2011 14:17:30 -0300 (ADT) Received: from ndmsppt05.ndc.nasa.gov (ndmsppt05.ndc.nasa.gov [198.117.0.104]) by ndjsnpf02.ndc.nasa.gov (Postfix) with ESMTP id 3CFC9A8CEE for ; Mon, 26 Sep 2011 11:52:40 -0500 (CDT) Received: from ndmshub01.ndc.nasa.gov (ndmshub01-pub.ndc.nasa.gov [198.117.0.160]) by ndmsppt05.ndc.nasa.gov (8.14.4/8.14.4) with ESMTP id p8QGqdE7024276 for ; Mon, 26 Sep 2011 11:52:39 -0500 Received: from lrc000959195.ndc.nasa.gov (198.119.134.170) by smtp02.ndc.nasa.gov (198.117.0.160) with Microsoft SMTP Server (TLS) id 8.3.192.1; Mon, 26 Sep 2011 11:52:39 -0500 Message-ID: <4E80ADD7.8090008@nasa.gov> Date: Mon, 26 Sep 2011 12:52:39 -0400 From: "Maria L. Wilson" Reply-To: Maria.L.Wilson-1@nasa.gov Organization: SSAI User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2 MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: postgres constraint triggers Content-Type: multipart/alternative; boundary="------------010604000601070006070006" X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.4.6813, 1.0.211, 0.0.0000 definitions=2011-09-26_06:2011-09-26, 2011-09-26, 1970-01-01 signatures=0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.403 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/378 X-Sequence-Number: 45154 --------------010604000601070006070006 Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit Hi all.... 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 AFTER INSERT ON inventory FOR EACH ROW EXECUTE PROCEDURE increment_dataset_granule_count(); CREATE OR REPLACE FUNCTION increment_dataset_granule_count() RETURNS trigger AS $BODY$ DECLARE BEGIN IF NEW.visible_id != 5 THEN UPDATE dataset SET gracount = gracount + 1 WHERE dataset.inv_id = NEW.inv_id; END IF; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; 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 dataset table. 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! thanks, Maria Wilson NASA/Langley Research Center Hampton, Virginia 23681 --------------010604000601070006070006 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Hi all....

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
  AFTER INSERT
  ON inventory
  FOR EACH ROW
  EXECUTE PROCEDURE increment_dataset_granule_count();

CREATE OR REPLACE FUNCTION increment_dataset_granule_count()
  RETURNS trigger AS
$BODY$
    DECLARE
    BEGIN
        IF NEW.visible_id != 5 THEN
            UPDATE dataset
            SET gracount = gracount + 1
            WHERE dataset.inv_id = NEW.inv_id;
           END IF;
        RETURN NULL;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
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 dataset table.

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!

thanks,

Maria Wilson
NASA/Langley Research Center
Hampton, Virginia 23681


--------------010604000601070006070006--