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>
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
