Subject: Re: constraint triggers
From: Gilberto Castillo =?ISO-8859-1?Q?Mart=EDnez?=
	<gilberto.castillo@etecsa.cu>
Reply-To: gilberto.castillo@etecsa.cu
To: Maria.L.Wilson-1@nasa.gov
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <4E83190B.3070604@nasa.gov>
References: <4E83190B.3070604@nasa.gov>
Organization: ETECSA
Date: Wed, 28 Sep 2011 10:26:29 -0400
Message-Id: <1317219989.24926.10.camel@gilbertoc.mtcorp.etecsa.cu>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="=pATcHuKt52770=imx3.etecsa.cu="

This is a multi-part message in MIME format.

--=pATcHuKt52770=imx3.etecsa.cu=
Content-Disposition: inline
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit


El mié, 28-09-2011 a las 08:54 -0400, Maria L. Wilson escribió:
> 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;
> -----------------------

Replace RETURN NULL for RETURN NEW
-- 
Saludos,
Gilberto Castillo
Edificio Beijing. Miramar Trade Center. Etecsa.
Miramar, La Habana.Cuba.

--=pATcHuKt52770=imx3.etecsa.cu=
Content-Type: text/plain
Content-Disposition: inline

--- 
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

--=pATcHuKt52770=imx3.etecsa.cu=--