Почему я получаю ошибку max_stack_depth в PostgreSQL при создании триггера?

Question

[beduin01]

Провозился очень долго, но так и не смог разобраться почему у меня получается ошибка при генерации триггера.
Собственно при вставке данных в таблицу мне нужно генерировать GUID. Я создал триггер. Как мне подсказали, он должен срабатывать *до* вставки данных, чтобы не порождать циклическую вставку, когда вставка одних данных пораждает вставку новой строки. Однако мне не строка нужна. Мне для строки которую я вставляю GUID нужен.

Вот мой код:

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

Вот сама функция:

CREATE OR REPLACE FUNCTION public.insertguid()
  RETURNS trigger AS
$BODY$BEGIN
    INSERT INTO "USERS" ("guid") VALUES (md5(random()::text || clock_timestamp()::text)::uuid);
    return new;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insertguid()
  OWNER TO postgres;

Сам триггер:

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

Однако при запросе:
INSERT INTO "USERS"(name) VALUES ('foo1');

Получаю:

ОШИБКА:  превышен предел глубины стека
HINT:  Увеличьте параметр конфигурации "max_stack_depth" (текущее значение 2048 КБ), предварительно убедившись, что ОС предоставляет достаточный размер стека.
CONTEXT:  SQL-оператор: "INSERT INTO "USERS" ("guid") VALUES (md5(random()::text || clock_timestamp()::text)::uuid)"
функция PL/pgSQL insertguid(), строка 2, оператор SQL-оператор
SQL-оператор: "INSERT INTO "USERS" ("guid") VALUES (md5(random()::text || clock_timestamp()::text)::uuid)"

Answer 1

[terrier]

Ваш триггер на инсерт вызывает инсерт ( который вызывает инсерт ).
Вам нужно во-первых объявить его FOR EACH ROW,
во-вторых можифицировать new
new.guid = < ваше выражение>

Comments

[beduin01]

FOR EACH ROW что даст?

Слово `new` зачем? Что оно делает?

[terrier]

Триггер, объявленный FOR EACH ROW выполняется для каждой измененной строки, а дефолтная настройка FOR EACH STATEMENT выполняет триггер один раз для одного запроса.
То есть
insert into users ( name ) values ( 'Vasya'), ('Petya'), ('Masha')
Триггер FOR EACH ROW выполнится 3 раза, FOR EACH STATEMENT - один раз.
new - собственно значение, которое будет вставлено вместо того, которое было бы вставлено без триггера.
Вы читали www.postgresql.org/docs/current/static/sql-createt... ?

[beduin01]

terrier: спасибо, а RETURNS trigger обязательно делать? Или можно просто void? Функция же по идее ничего не возвращает, а просто вставляет данные.

[terrier]

function_name
A user-supplied function that is declared as taking no arguments and returning type trigger, which is executed when the trigger fires.

Обязательно. Таковы правила

Answer 2

[Александр Шелеметьев]

Давайте поясню. Триггер в вашем случае вызывается перед вставкой данных. Ему автоматически передаётся переменная NEW, в которой содержатся значения, которые должны вставиться: NEW.id, NEW.guid, NEW.name.
Так вот этот триггер может просто поменять вставляемые значения. Например, в триггерной функции вы подменяете значение uuid на то, что вам нужно:

NEW.uuid = md5(random()::text || clock_timestamp()::text)::uuid;

после этого триггер должен вернуть изменённые значения NEW:
return NEW;
Для вставки в таблицу будет испрользовано то, что вернул триггер, т.е. NEW, в том числе и NEW.guid, который вы указали в триггерной функции.

Возможно, устроит более простое решение: объявить в таблице значение поля uuid по умолчанию:

CREATE TABLE public."USERS"
(
  id integer,
  guid uuid default md5(random()::text || clock_timestamp()::text)::uuid,
  name text
)

в таком случае можно обойтись без триггеров.