From: Gilberto Castillo Martínez <gilberto.castillo@etecsa.cu>
To: Maria.L.Wilson-1@nasa.gov
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: constraint triggers
Date: Wed, 28 Sep 2011 10:26:29 -0400
Message-ID: <1317219989.24926.10.camel@gilbertoc.mtcorp.etecsa.cu> (raw)
In-Reply-To: <4E83190B.3070604@nasa.gov>
References: <4E83190B.3070604@nasa.gov>
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.
---
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gilberto.castillo@etecsa.cu, Maria.L.Wilson-1@nasa.gov
Subject: Re: constraint triggers
In-Reply-To: <1317219989.24926.10.camel@gilbertoc.mtcorp.etecsa.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox