CODEWITHSUNDEEP
postgresqldatabase-trigger
Matelutex
Matelutex

Reputation: 2220

Postgres on Docker - stack depth limit exeeded

I installed Postgres (latest version) on my local Docker:

docker-compose:

version: '3.1'
services:
  postgres:
    image: postgres
    restart: always
    ports:
      - 5435:5432
    environment:
      POSTGRES_PASSWORD: xxxxxxx
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command:
      - "postgres"
      - "-c"
      - "max_stack_depth=7680"

volumes:
  pgdata:
networks:
  network1:

My tables:

create TABLE dictionary.language (
  id bigserial NOT NULL PRIMARY KEY,
  lang_name text NOT NULL
);

create TABLE dictionary.level (
  id bigserial NOT NULL PRIMARY KEY,
  level smallint NOT NULL
);

create TABLE dictionary.word (
  id bigserial NOT NULL PRIMARY KEY,
  name text,
  translation text,
  language_abbr_id bigint references language(id),
  level_id smallint references level(id),
  last_reviewed TIMESTAMP not null DEFAULT clock_timestamp()
);


create TABLE dictionary.examples (
   id bigserial NOT NULL PRIMARY KEY,
   sentence text NOT NULL,
   translation text NOT NULL,
   word_id bigint references word(id)
   );

create TABLE dictionary.user (
  id bigserial NOT NULL PRIMARY KEY,
  username text NOT NULL,
  password text NOT NULL
);

  CREATE OR REPLACE FUNCTION dictionary.set_last_reviewed()
  RETURNS trigger as
  $$
BEGIN
         INSERT INTO dictionary.word(last_reviewed)
         VALUES(now());

    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';



create trigger updateLastReviewedAfterUpdate
before update on dictionary.word
for each row
execute procedure dictionary.set_last_reviewed();

create trigger updateLastReviewedAfterInsert
before insert on dictionary.word
for each row
execute procedure dictionary.set_last_reviewed();

I want to insert something to my table:

insert into word(id,name,translation,language_abbr_id,level,last_reviewed) values(1,'fabulous', 'bajeczny, fantastyczny'    ,   1   ,   6   ,   '11/09/2018 23:58'  );

Then I get the error:

Query execution failed

Reason: SQL Error [54001]: ERROR: stack depth limit exceeded Hint: Increase the configuration parameter "max_stack_depth" (currently 7680kB), after ensuring the platform's stack depth limit is adequate. Where: SQL statement "INSERT INTO dictionary.word(last_reviewed)

     VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO

dictionary.word(last_reviewed)

     VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO

dictionary.word(last_reviewed)

     VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO

dictionary.word(last_reviewed)

     VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement SQL statement "INSERT INTO

dictionary.word(last_reviewed)

     VALUES(now())" PL/pgSQL function set_last_reviewed() line 3 at SQL statement ...

Can it be related to my triggers? I don't have any idea how to solve my problem

Upvotes: 0

Views: 505

Answers (1)

user330315
user330315

Reputation:

Your insert trigger INSERTs a new row into the table word which in turn fires the insert trigger, which INSERTs a new row which fires the trigger.... and so on.

As far as I can tell, you are just trying to set a default value for the column.

You do this by changing the record that is processed by the trigger, not by inserting a completely new row into the table:

Just change your trigger to:

CREATE OR REPLACE FUNCTION dictionary.set_last_reviewed()
  RETURNS trigger as
$$
BEGIN
  new.last_reviewed := now();
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Upvotes: 1

Related Questions