max_stack_depth on PostgreSQL trigger execute

Question

I have very simple trigger and trigger function. Trigger function should insert GUID in every row after insert operation.

SQL:

CREATE TABLE "USERS"
(
  id integer,
  guid uuid,
  name text
)

Trigger function:

CREATE OR REPLACE FUNCTION insertGUID() RETURNS trigger AS
$BODY$BEGIN
    INSERT INTO "USERS" ("guid") VALUES (md5(random()::text || clock_timestamp()::text)::uuid);
END$BODY$
LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER example_trigger BEFORE INSERT ON "USERS" EXECUTE PROCEDURE insertGUID();

But after after every insert. Like:

INSERT INTO "USERS"(name)  VALUES ('foo1');

I am getting error: max_stack_depth

CONTEXT: SQL: "INSERT INTO "USERS" ("Joe") VALUES (md5(random()::text || clock_timestamp()::text)::uuid)"

UPD: I am getting same problem even if I use BEFORE INSERT

user330315 · Accepted Answer · 2016-03-30 11:18:42Z

1

Your insert fires the trigger which inserts a new row, which then fires the trigger, which inserts a new row, which fires the trigger, which inserts a new row, which fires the trigger which inserts a new row.... (you get the picture).

You want to change the new row, not insert a new one. So your trigger function needs to be like this:

CREATE OR REPLACE FUNCTION insertGUID() 
  RETURNS trigger AS
$BODY$BEGIN
    new.guid := md5(random()::text || clock_timestamp()::text;
    return new;
END$BODY$
LANGUAGE plpgsql;

For this to work the trigger itself has to be defined as before insert.

CREATE TRIGGER example_trigger 
    BEFORE INSERT ON "USERS" EXECUTE PROCEDURE insertGUID();

^{Unrelated, but: you should really avoid quoted identifiers ("USERS"). They are much more trouble than they are worth it}

answered Mar 30, 2016 at 11:18

user330315

Sign up to request clarification or add additional context in comments.

8 Comments

Dmitry Bubnenkov Over a year ago

Strange, but it's look like it's work same even with BEFORE snag.gy/hyth5.jpg

Dmitry Bubnenkov Over a year ago

but on my screenshot is before. Or am wrong? I will try check all again

user330315 Over a year ago

@user1432751: I have no idea what that screen shot is supposed to show me. The problem with the endless recursion has nothing to do whether it's a before or after trigger. But changing the column value will only work in a before trigger.

Dmitry Bubnenkov Over a year ago

I checked all my code. I am getting same error even if I use BEFORE INSERT.

user330315 Over a year ago

@user1432751 Again: did you remove (replace) the insert statement from the trigger function?

|

Collectives™ on Stack Overflow

max_stack_depth on PostgreSQL trigger execute

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related