CODEWITHSUNDEEP
postgresqltriggers
Dmitry Bubnenkov
Dmitry Bubnenkov

Reputation: 9869

max_stack_depth on PostgreSQL trigger execute

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

Upvotes: 0

Views: 1030

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions