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.