Ошибка превышен предел глубины стека postgres

Ваш триггер срабатывает на событие 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;

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

    CREATE OR REPLACE FUNCTION checkOnLoan()    
    RETURNS trigger AS
$$
    DECLARE    
       countGal numeric;
    BEGIN
            SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
            IF countGal = 0 THEN    
                INSERT INTO ON_LOAN VALUES (new.Certid, new.P_id, new.Insurer);
            ELSE
                RAISE EXCEPTION 'ALREADY IN GALLERY';
            END IF;
    RETURN new;
    END;
$$

LANGUAGE 'plpgsql';

CREATE TRIGGER OnLoan
    AFTER INSERT ON ON_LOAN
    FOR EACH ROW
    EXECUTE PROCEDURE checkOnLoan();

2 ответа

CREATE OR REPLACE FUNCTION check_onloan()
  RETURNS trigger AS
$$
BEGIN
   IF EXISTS (SELECT FROM in_gallery WHERE p_id = NEW.p_id) THEN
      RAISE EXCEPTION 'p_id % already in gallery!', NEW.p_id;
   END IF;
   RETURN NEW;  -- for BEFORE trigger
END
$$  LANGUAGE plpgsql;

CREATE TRIGGER insert_after_on_loan
BEFORE INSERT ON on_loan             -- !!!
FOR EACH ROW EXECUTE PROCEDURE check_onloan();

CREATE OR REPLACE FUNCTION checkOnLoan()    
RETURNS trigger AS
$$
DECLARE    
    countGal numeric;
BEGIN
    SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
    IF countGal = 0 THEN    
        RETURN new;
    ELSE
        RAISE EXCEPTION 'ALREADY IN GALLERY';
    END IF;
END;
$$
LANGUAGE plpgsql;

Хорошо, если вы действительно хотите, чтобы триггер обновлялся, что вы могли бы сделать, чтобы установить этот триггер как специфичный для столбца, чтобы он не запускался при обновлении до all_books, что вызывает вашу рекурсию. Что-то вроде этого —

create trigger total2
after update of copy_id
on totalbooks
for each row
execute procedure total1();

Конечно, вы можете изменить, какие столбцы запускают функцию, я просто выбрал copy_id потому что это то, что вы считаете.

ОДНАКО

Если вы обновляете с помощью count() результат, вы можете просто включить триггер INSERT и DELETE действия. Таким образом, триггер будет срабатывать при изменении счетчика, но сам не будет срабатывать при обновлении. // РЕДАКТИРОВАТЬ: так как ваш sum это всего лишь подсчет всех записей в copies, он изменится только при вставке или обновлении записи, поэтому запуск этого триггера при обновлении в любом случае не имеет смысла.

РЕДАКТИРОВАТЬ: я подумал, что было бы полезно добавить ссылку на СОЗДАТЬ ТРИГГЕР Документация. См. раздел с пометкой «событие», потому что в нем подробно описано, как указывать столбцы в событии.

РЕДАКТИРОВАТЬ ДЛЯ НОВОЙ ИНФОРМАЦИИ:

Учитывая то, что вам нужно выполнить, я думаю, вам нужно переосмыслить свой дизайн данных, я предлагаю вам использовать отношения родитель-потомок (каждый раз, когда вы кэшируете общие данные во многих строках в таблице, потому что они имеют что-то общее, что является признаком того, что вам может понадобиться родительская таблица).

Иметь books таблица, где каждая строка содержит информацию об одной книге (название, автор и т. д.), а затем copies таблица, в которой каждая строка содержит информацию об одной копии книги (серийный номер, дата последнего извлечения и т. д.).

Таким образом, получить количество копий так же просто, как SELECT COUNT(*) FROM copies WHERE book_id=[some book id].

Если вы действительно хотите где-то кэшировать счетчик, сделайте это на books таблице.

Создать INSERT OR UPDATE запускать copies это делает UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id.

Затем создайте DELETE триггер на копиях, которые делают UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id

Причина двух триггеров заключается в том, что NEW переменная доступна только в INSERT or UPDATE триггеры и OLD доступно только в DELETE триггеры. Вы могли бы сделать все это как один триггер, но для этого потребуется больше кода, чем я хотел бы разместить здесь.

Убедитесь, что все ваши триггеры AFTER триггеры, иначе новая вставленная/удаленная строка не будет учитываться при подсчете.

Я stack depth limit exceeded при попытке сохранить строку из R в PostgreSQL. Чтобы решить проблему с массовыми upserts, я использовал такой запрос:

sql_query_data <- sprintf("BEGIN;
                        CREATE TEMPORARY TABLE 
                          ts_updates(ts_key varchar, ts_data hstore, ts_frequency integer) ON COMMIT DROP;
                          INSERT INTO ts_updates(ts_key, ts_data) VALUES %s;
                          LOCK TABLE %s.timeseries_main IN EXCLUSIVE MODE;

                          UPDATE %s.timeseries_main
                          SET ts_data = ts_updates.ts_data,
                          ts_frequency = ts_updates.ts_frequency
                          FROM ts_updates
                          WHERE ts_updates.ts_key = %s.timeseries_main.ts_key;

                          INSERT INTO %s.timeseries_main
                          SELECT ts_updates.ts_key, ts_updates.ts_data, ts_updates.ts_frequency
                          FROM ts_updates
                          LEFT OUTER JOIN %s.timeseries_main ON (%s.timeseries_main.ts_key = ts_updates.ts_key)
                          WHERE %s.timeseries_main.ts_key IS NULL;
                          COMMIT;",
                          values, schema, schema, schema, schema, schema, schema, schema)

}

До сих пор этот запрос работал достаточно хорошо для обновления миллионов записей при минимальном количестве вложенных вставок. Всякий раз, когда я сталкивался с проблемами размера стека, я просто разбивал свои записи на несколько кусков и продолжал оттуда.

Однако сейчас эта стратегия сталкивается с некоторыми проблемами. У меня больше нет записей, но несколько, в которых hstore немного больше. Но это действительно не «большой». Я читаю предложения @Craig Ringer, который советует не приближаться к пределу 1 ГБ. Поэтому я предполагаю, что размер самого hstore не является проблемой, но я получаю это сообщение:

Error in postgresqlExecStatement(conn, statement,...): RS-DBI driver: (could not Retrieve the result: ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform stack depth limit is adequate. )

EDIT: я увеличил лимит до 7 МБ и столкнулся с той же ошибкой, что и 7 МБ. Это действительно странно для меня, потому что я сам запрос составляет всего 1,7 МБ (проверял его, вставляя его в текстовый файл). Может ли кто-нибудь пролить свет на это?