CODEWITHSUNDEEP
postgresqltriggerspsql
Mae_ 27
Mae_ 27

Reputation: 15

Stack depth limit exceeded PostgreSQL insert trigger

I'm trying to create a simple trigger that inserts a row into the friend table with inverted values, so when (1,2) is inserted (2,1) is also inserted, but I keep getting this error and I'm not sure what is going wrong.

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. CONTEXT: SQL statement "insert into friend(ID1,ID2) values (NEW.ID2, NEW.ID1)" PL/pgSQL function friend_add() line 2 at SQL statement

CREATE OR REPLACE FUNCTION public.friend_add()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
COST 100
AS $BODY$begin
insert into friend(ID1,ID2) values (NEW.ID2, NEW.ID1);
return new;
end
end;$BODY$;

CREATE TRIGGER friend_add
    AFTER INSERT
    ON public.friend
    FOR EACH ROW
    EXECUTE PROCEDURE public.friend_add();

Here are my function and trigger definions.

Upvotes: 0

Views: 286

Answers (1)

JGH
JGH

Reputation: 17876

It is an infinite loop: you insert ID1-ID2, then the trigger inserts ID2-ID1, which is caught by the trigger that again inserts ID1-ID2 etc.

You need to do the insert once only. The simplest (and propably the safest) would be to have a unique constraint on ID1-ID2, and to do an upsert:

insert into friend(ID1,ID2) values (NEW.ID2, NEW.ID1) ON CONFLICT (ID1,ID2) DO NOTHING;

Upvotes: 1

Related Questions