CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
vftw
vftw

Reputation: 1687

Stack depth Limit INSERT trigger

I have this table on PostgreSQL and I'd like to make a trigger that every time I edit one row of the table publications the last_edit_date is updated to now():

CREATE FUNCTION trigger_update_question_timestamp()
  RETURNS "trigger" AS $func$
BEGIN
    UPDATE publications SET last_edit_date = now() WHERE publicationid = NEW.publicationid;
    RETURN NULL;
END;
$func$  LANGUAGE plpgsql;

CREATE TRIGGER answer_update_question_timestamp AFTER INSERT OR UPDATE ON publications
   FOR EACH ROW EXECUTE PROCEDURE trigger_update_question_timestamp();

CREATE TABLE publications
(
    publicationid SERIAL PRIMARY KEY,
    body VARCHAR(1000) NOT NULL ,
    creation_date TIMESTAMP DEFAULT now() NOT NULL,
    userid INTEGER NOT NULL,
    last_edit_date TIMESTAMP,
    CONSTRAINT body_length CHECK (CHAR_LENGTH(body) >= 10 AND CHAR_LENGTH(body) <= 1000),
    CONSTRAINT "FK_publications_users"
        FOREIGN KEY ("userid") REFERENCES users ("userid") ON DELETE SET NULL ON UPDATE CASCADE
);

I'm using PhpStorm and when I manually edit a line I get the following error:

[54001] 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. Where: SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "userid" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" SQL statement "UPDATE publications SET last_edit_date = now() WHERE publicationid = NEW.publicationid" PL/pgSQL function trigger_update_question_timestamp()

What does this exactly means? Does it have to do with my trigger or any other definition?

Upvotes: 0

Views: 1625

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247043

Your trigger is recursive, that is, the trigger function executes an UPDATE that calls the trigger function again.

The solution here is to use a BEFORE INSERT OR UPDATE trigger, that modifies NEW as follows:

BEGIN
   NEW.last_edit_date := current_timestamp;
   RETURN NEW;
END;

Upvotes: 3

Related Questions