CODEWITHSUNDEEP
sqlpostgresql
juanpscotto
juanpscotto

Reputation: 1050

PostgreSQL Trigger Function get stack and does not update the table

I have a trigger that calls a function. It basically must update the same table after update. But it get stacks and does not update anything.

This is my trigger:

    CREATE OR REPLACE FUNCTION invtransferences_products_after() 
    RETURNS TRIGGER AS 
$BODY$
DECLARE
    TR invtransferences_products%ROWTYPE;
    v_transfer_cost NUMERIC;
BEGIN
    IF(TG_OP='INSERT') THEN
        TR := NEW;
        RAISE NOTICE 'INVTRANSFERENCE PRODUCT ADDED %',TR.id;
        UPDATE invtransferences_products  
        SET product_cost = (get_product_composition_cost(product_id, 0)*quantity )
        WHERE invtransferences_products.id=TR.id;
    ELSE
        IF (TG_OP='UPDATE') THEN
            TR := NEW;
            RAISE NOTICE 'INVTRANSFERENCE PRODUCTS UPDATED %',TR.id;
            UPDATE invtransferences_products  
            SET product_cost = (get_product_composition_cost(product_id, 0)*quantity )
            WHERE invtransferences_products.id=TR.id;
        END IF;
    END IF;
    RETURN TR;
END
$BODY$
LANGUAGE plpgsql;

This is my table invtransferences_products:

CREATE TABLE invtransferences_products
(
   id                  serial      NOT NULL,
   invtransference_id  bigint       NOT NULL,
   product_id          bigint       NOT NULL,
   quantity            numeric      DEFAULT 1 NOT NULL,
   created             timestamp    DEFAULT now() NOT NULL,
   modified            timestamp,
   rcv_quantity        numeric      DEFAULT 0 NOT NULL,
   pnd_quantity        numeric      DEFAULT 0 NOT NULL,
   product_cost        numeric
);

ALTER TABLE invtransferences_products
   ADD CONSTRAINT invtransferences_products_pkey
   PRIMARY KEY (id);

ALTER TABLE invtransferences_products
  ADD CONSTRAINT invtransferences_products_invtransference_id_fkey FOREIGN KEY (invtransference_id)
  REFERENCES invtransferences (id)
  ON UPDATE CASCADE
  ON DELETE CASCADE;

COMMIT;

What's wrong?? Help please.

Upvotes: 0

Views: 131

Answers (2)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

The problem is that the UPDATE statement in the trigger function causes the trigger to fire again.

Instead of issuing a separate update, you should manipulate the data in NEW.

Something like:

CREATE OR REPLACE FUNCTION invtransferences_products_after() 
    RETURNS TRIGGER AS 
$BODY$
BEGIN
    IF(TG_OP='INSERT') THEN
        RAISE NOTICE 'INVTRANSFERENCE PRODUCT ADDED %',NEW.id;
    ELSE
        IF (TG_OP='UPDATE') THEN
            RAISE NOTICE 'INVTRANSFERENCE PRODUCTS UPDATED %',NEW.id;
        END IF;
    END IF;

    NEW.product_cost := get_product_composition_cost(NEW.product_id,0)*NEW.quantity ;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

Fiddle at: SQLFiddle

Upvotes: 2

Daniel Vérité
Daniel Vérité

Reputation: 61506

This trigger will cause infinite recursion ending up with a stack depth limit exceeded error, because it issues another UPDATE of the table each time an INSERT/UPDATE occurs on the same table.

The solution is, instead of this:

 UPDATE invtransferences_products  
        SET product_cost = (get_product_composition_cost(product_id, 0)*quantity )
        WHERE invtransferences_products.id=TR.id;

It should do that:

  NEW.product_cost := get_product_composition_cost(NEW.product_id, 0)*NEW.quantity;

and declare the trigger as running BEFORE UPDATE or INSERT (not AFTER).

That's the more logical approach.

As a workaround, recursion can also be blocked outside of the trigger. This is answered in Prevent recursive trigger in PostgreSQL.

Upvotes: 1

Related Questions