Trigger raising: "ERROR: stack depth limit exceeded"

Question

I am trying to make a trigger where after I insert a painting, then I want to insert it to either the In_Gallery table or the On_Loan table but not both. When I tried to make a trigger function, I keep getting the error:

ERROR: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

I am not sure what's wrong with this:

    CREATE OR REPLACE FUNCTION checkOnLoan()    
    RETURNS trigger AS
$$
    DECLARE    
       countGal numeric;
    BEGIN
            SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
            IF countGal = 0 THEN    
                INSERT INTO ON_LOAN VALUES (new.Certid, new.P_id, new.Insurer);
            ELSE
                RAISE EXCEPTION 'ALREADY IN GALLERY';
            END IF;
    RETURN new;
    END;
$$

LANGUAGE 'plpgsql';

CREATE TRIGGER OnLoan
    AFTER INSERT ON ON_LOAN
    FOR EACH ROW
    EXECUTE PROCEDURE checkOnLoan();

You have a recursive call. One of your inserts is inserting into a table, which is triggering the trigger, which is inserting into the table, which triggers the trigger... and so on. Each of these layers requires stack space. Eventually, you run out. — 3Dave, Nov 30 '19 at 00:40
@3Dave Hi thanks for the help, what could I do to get rid of it, my brain is fried been sitting looking at this for the past 4 hours. — Drakus, Nov 30 '19 at 00:42

sticky bit · Accepted Answer · 2019-11-30T00:49:43.717

You INSERT again in an AFTER INSERT trigger, causing the trigger to be fired again for this second INSERT which again INSERTs and fires the trigger anew and so on and so on. At some point the stack is exhausted from all that function calls and you get the error.

Remove the INSERT from the trigger functions and just RETURN new. Returning new will cause the original INSERT to be completed. There's no need for a manual INSERT in the trigger function for AFTER INSERT triggers.

Like:

CREATE OR REPLACE FUNCTION checkOnLoan()    
RETURNS trigger AS
$$
DECLARE    
    countGal numeric;
BEGIN
    SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
    IF countGal = 0 THEN    
        RETURN new;
    ELSE
        RAISE EXCEPTION 'ALREADY IN GALLERY';
    END IF;
END;
$$
LANGUAGE plpgsql;

And analog for the other trigger function.

It should be noted that `RETURN new;` does not do anything in a row-level `AFTER` trigger. — Erwin Brandstetter, Dec 02 '19 at 02:08

Erwin Brandstetter · Answer 2 · 2019-12-02T02:04:50.267

The immediate cause of you error is an endless loop like the currently accepted answer explained. But you should probably fix more than just that. A BEFORE trigger would improve the situation ...

Trigger function:

CREATE OR REPLACE FUNCTION check_onloan()
  RETURNS trigger AS
$$
BEGIN
   IF EXISTS (SELECT FROM in_gallery WHERE p_id = NEW.p_id) THEN
      RAISE EXCEPTION 'p_id % already in gallery!', NEW.p_id;
   END IF;
   RETURN NEW;  -- for BEFORE trigger
END
$$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER insert_after_on_loan
BEFORE INSERT ON on_loan             -- !!!
FOR EACH ROW EXECUTE PROCEDURE check_onloan();

RETURN NEW would not make any sense at all for an AFTER trigger. The manual:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

My educated guess: you want a BEFORE trigger instead. All that remains to do is raising an exception. It's cheaper to check before doing the work than rolling it back later. For the purpose, it's generally more efficient to check for existence with IF EXISTS ... instead of counting. Then you don't need to define any variables and no DECLARE section.

However you do it, there will be remaining race condition. Under concurrent write load, multiple transactions could try to enter the same p_id in both tables at virtually the same time, not see the p_id in the other table, yet, and enter it in both tables after all. It helps to keep transactions short to minimize the time frame, but the problem remains in principle.

One clean solution would be a single table painting with a boolean flag indicating its status. That can only ever have one state at a time. Details depend on your complete situation ...

Aside: reconsider CaMeL case spelling of identifiers in Postgres.

Are PostgreSQL column names case-sensitive?

Trigger raising: "ERROR: stack depth limit exceeded"

2 Answers2