Как исправить ошибку превышения предела глубины стека postgresql?

Question

Есть две таблицы trips и routes. Создаю триггер на изменение таблицы trips на update и триггерную функцию для расчета общей суммы за текущий месяц.

create or replace function month_sum_update1() returns trigger as
$BODY$
DECLARE 
summ int;
BEGIN
select sum(quantity*price) into summ from trips, routes
WHERE trips.id_route = routes.id_route
AND (date_travel<=now() AND date_travel>=now()-interval '1 month');
UPDATE trips set all_sum_column = summ;
return new;
END;
$BODY$
language plpgsql;
create trigger on_date_insert_update1 before update on trips
FOR EACH ROW
EXECUTE PROCEDURE month_sum_update1();

Далее делаю update:

update trips set quantity = 5 where id_trip = 9;

Выдаёт ошибку:

ERROR:  ОШИБКА:  превышен предел глубины стека
HINT:  Увеличьте параметр конфигурации "max_stack_depth" (текущее значение 2048 КБ), предварительно убедившись, что ОС предоставляет достаточный размер стека.
CONTEXT:  SQL-оператор: "select sum(quantity*price)           from trips, routes
WHERE trips.id_route = routes.id_route
AND (date_travel<=now() AND date_travel>=now()-interval '1 month')"
функция PL/pgSQL month_sum_update1(), строка 5, оператор SQL-оператор
SQL-оператор: "UPDATE trips set all_sum_column = summ"
функция PL/pgSQL month_sum_update1(), строка 8, оператор SQL-оператор
SQL-оператор: "UPDATE trips set all_sum_column = summ"

Как правильно исправить функцию и триггер?

Answer 1

Ваш триггер срабатывает на событие BEFORE UPDATE, а внутри него происходит еще один update, который снова вызывает этот триггер, что приводит к бесконечной рекурсии.

Также немного странным выглядит само обновление:

UPDATE trips set all_sum_column = summ;

Вы действительно хотите обновить all_sum_column всех записей таблицы на одно и тоже значение при обновлении одной из них?

Я полагаю, что это просто ошибка в коде (потому что при подсчете суммы вы учитываеете только текущую запись trips.id_route = routes.id_route), и вы хотите обновить только текущую запись из tips. В таком случае необходимо обновлять не через update, а используя специальное ключевое слово, доступное внутри триггера - NEW (оно представляет собой новую версию обновляемой строки, также есть OLD - предыдущая версия соответственно):

NEW.all_sum_column = summ;

Это не вызовет последующего выполнения триггера еще раз.

Триггер будет выглядеть так:

create or replace function month_sum_update1() returns trigger as
$BODY$
DECLARE
    summ int;
BEGIN
    select sum(quantity*price) into summ from trips, routes
    WHERE routes.id_route = NEW.id_route
      AND (date_travel<=now() AND date_travel>=now()-interval '1 month');
    NEW.all_sum_column = summ;
    return NEW;
END;
$BODY$
    language plpgsql;

Больше о триггерах в официальной документации.

БЛОГ НА HUSL

Как исправить ошибку превышения предела глубины стека postgresql?

Ответы (1 шт):