Ваш триггер срабатывает на событие 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;
Больше о триггерах в официальной документации.
I Create trigger to store the Salary Amount but When I Fire The Query For Insert
INSERT INTO employees(
employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES (2002,'poiuy','patel','bhargavgor@dfghj',9898562123,'2012-07-31 00:00:00','IT_PROG',4500.00,0.00,100,60);
Then It Will Show Me The Following Error To set the limit of the max_stack_depth
So Can Any One Give Me The Idea TO Solve This Error..
I Try Also To Change The Value Of max_stack_depth
in Configuration File But It IS Not Working
Error Like Following
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.
Давайте поясню. Триггер в вашем случае вызывается перед вставкой данных. Ему автоматически передаётся переменная 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
)
в таком случае можно обойтись без триггеров.
Ваш триггер срабатывает на событие 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;
Больше о триггерах в официальной документации.
Я пытаюсь создать триггер, когда после вставки рисунка я хочу вставить его в таблицу In_Gallery
или On_Loan
, но не в обе. Когда я пытался создать функцию триггера, я продолжал получать сообщение об ошибке:
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.
Я не уверен, что с этим не так:
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 ответа
Непосредственной причиной вашей ошибки является бесконечный цикл, подобный объясненному в настоящее время принятому ответу. Но вы должны, вероятно, исправить больше, чем просто это. Триггер BEFORE
улучшит ситуацию …
Функция запуска:
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();
RETURN NEW
не имеет никакого смысла вообще для триггера AFTER
. Руководство:
Возвращаемое значение игнорируется для триггеров уровня строки, запускаемых после операции, и поэтому они могут возвращать
NULL
.
Мое обоснованное предположение: вам нужен триггер BEFORE
. Осталось только сделать исключение. Дешевле проверить перед выполнением работы, чем откатить ее позже. Для этой цели обычно эффективнее проверять существование с помощью IF EXISTS ...
, а не подсчитывать. Тогда вам не нужно определять какие-либо переменные и нет DECLARE
раздела.
Связанный:
-
PL / pgSQL проверяет, существует ли строка
-
Откат транзакции при ошибке запуска
Очевидно, вам нужен еще один зеркальный триггер для таблицы in_gallery
в этом дизайне — который, вероятно, не идеален для начала.
Как бы вы это ни делали, будет оставшееся состояние гонки . При одновременной загрузке записи несколько транзакций могут попытаться ввести один и тот же p_id
в обе таблицы практически в одно и то же время, но пока не увидеть p_id
в таблице other , и введите его в обе таблицы. Это помогает держать транзакции короткими, чтобы минимизировать временные рамки, но проблема остается в принципе.
Одним чистым решением будет одна таблица painting
с флагом boolean
, указывающей ее статус. Это может иметь только одно состояние за раз. Детали зависят от вашей полной ситуации …
В стороне: пересмотреть регистр написания идентификаторов в CaMeL в Postgres.
- Имена столбцов PostgreSQL чувствительны к регистру?
1
Erwin Brandstetter
2 Дек 2019 в 02:04
Вы снова INSERT
в триггере AFTER INSERT
, вызывая повторный запуск триггера в течение этой секунды INSERT
, который снова INSERT
запускает и запускает триггер заново, и так далее, и так далее , В какой-то момент стек исчерпан всеми вызовами этой функции, и вы получите ошибку.
Удалите INSERT
из функций триггера и просто RETURN new
. Возвращение new
приведет к завершению оригинала INSERT
. Для триггеров AFTER INSERT
нет необходимости вручную INSERT
в функции триггера.
Как:
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;
И аналог для другой триггерной функции.
3
sticky bit
30 Ноя 2019 в 00:49
Хорошо, если вы действительно хотите, чтобы триггер обновлялся, что вы могли бы сделать, чтобы установить этот триггер как специфичный для столбца, чтобы он не запускался при обновлении до 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 МБ (проверял его, вставляя его в текстовый файл). Может ли кто-нибудь пролить свет на это?
#postgresql
Вопрос:
Я новичок в базе данных, но хочу создать триггер для остановки обновления при значении ниже 0.
CREATE OR REPLACE FUNCTION buyArticle()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$
DECLARE
print record;
calcquantity integer;
begin
SELECT w.iddrug, w.quantity, w.iddrugstore FROM warehouse w
INNER JOIN drugstore d on w.iddrugstore = d.iddrugstore WHERE w.iddrug= new.iddrug and
w.iddrugstore =new.iddrugstore
into print;
calcquantity :=print.quantity-new.quantity;
if calcquantity > 0 THEN
update warehouse set quantity=quantity-new.quantity where iddrug=new.iddrug AND
iddrugstore=new.iddrugstore;
end if;
RETURN NEW;
end;
$
CREATE TRIGGER triggerBuyArticle
BEFORE UPDATE
ON warehouse
FOR EACH ROW
EXECUTE PROCEDURE buyArticle()
запрос является update warehouseset quantity=2 where iddrug=1 AND iddrugstore=2;
Когда я запускаю этот запрос, я получаю бесконечный цикл и эту ОШИБКУ: превышен предел глубины стека, проблема в том, если, но я не знаю, почему?
Комментарии:
1. Я не думаю, что тебе нужна петля. (или функция)
2. Есть ли у меня петля? Когда я запускаю обновление, я получаю СООБЩЕНИЕ об ОШИБКЕ: превышен предел глубины стека ПОДСКАЗКА: Увеличьте параметр конфигурации «max_stack_depth» (в настоящее время 2048 КБ), убедившись, что предел глубины стека платформы достаточен. КОНТЕКСТ: Инструкция SQL «ВЫБЕРИТЕ w.idпрепарат, количество, аптеку СО склада, чтобы ПРИСОЕДИНИТЬСЯ к аптеке d на w.idпрепарат = d.idпрепарат, ГДЕ w.idпрепарат= новый.idпрепарат и w.idпрепарат =новый.idпрепарат» Функция PL/pgSQL buyArticle() строка 6 в инструкции SQL
3. Ваш триггер должен выполняться до обновления, но он сам выполняет ОБНОВЛЕНИЕ, поэтому это рекурсивный вызов, но стек все равно переполняется.
4. Что ты хочешь, чтобы я сделал?
5. Я Неправильно Понял. Тебе это не нужно
IF
. Смотрите ответ.
Ответ №1:
Я считаю, что это можно свести к:
CREATE OR REPLACE FUNCTION buyArticle()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$
DECLARE
calcquantity integer;
begin
calcquantity := old.quantity - new.quantity;
if calcquantity > 0 THEN
new.quantity = old.quantity - new.quantity;
end if;
RETURN NEW;
end;
$
CREATE TRIGGER triggerBuyArticle
BEFORE UPDATE
ON warehouse
FOR EACH ROW
EXECUTE PROCEDURE buyArticle()
Было бы полезно, если бы вы указали, чего вы пытаетесь достичь?
Комментарии:
1. Я хочу что-то сделать, когда клиент покупает товар, а на складе осталось только 10, но они хотят купить 11. Триггер не должен разрешать обновление.
2. Это выполнимо, просто
RETURN NULL
чтобы остановить действие триггера, когда новое количество становится отрицательным. Вы действительно хотите это сделать или действительно хотите продать 10 и вернуть 1 заказ? Также вы хотите сделать это наwarehouse
столе или на столе заказов?
Ответ №2:
Это связано с тем, что вы выполняете обновление в триггере ПЕРЕД ОБНОВЛЕНИЕМ. Поэтому каждый раз, когда вызывается ваш триггер, он вызывает другой вызов триггера, пока стек не переполнится.
Если вы хотите изменить значения, помещаемые в строку в результате обновления, вместо выполнения обновления в триггере, верните строку, содержащую фактические значения, которые вы хотите отобразить в обновлении, вместо возврата НОВЫХ неизмененных. Например, вместо вашего заявления об обновлении сделайте: NEW.quantity := quantity - NEW.quantity; RETURN NEW;
Я пытаюсь создать триггер, когда после вставки рисунка я хочу вставить его в таблицу In_Gallery
или On_Loan
, но не в обе. Когда я пытался создать функцию триггера, я продолжал получать сообщение об ошибке:
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.
Я не уверен, что с этим не так:
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 ответа
Непосредственной причиной вашей ошибки является бесконечный цикл, подобный объясненному в настоящее время принятому ответу. Но вы должны, вероятно, исправить больше, чем просто это. Триггер BEFORE
улучшит ситуацию …
Функция запуска:
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();
RETURN NEW
не имеет никакого смысла вообще для триггера AFTER
. Руководство:
Возвращаемое значение игнорируется для триггеров уровня строки, запускаемых после операции, и поэтому они могут возвращать
NULL
.
Мое обоснованное предположение: вам нужен триггер BEFORE
. Осталось только сделать исключение. Дешевле проверить перед выполнением работы, чем откатить ее позже. Для этой цели обычно эффективнее проверять существование с помощью IF EXISTS ...
, а не подсчитывать. Тогда вам не нужно определять какие-либо переменные и нет DECLARE
раздела.
Связанный:
-
PL / pgSQL проверяет, существует ли строка
-
Откат транзакции при ошибке запуска
Очевидно, вам нужен еще один зеркальный триггер для таблицы in_gallery
в этом дизайне — который, вероятно, не идеален для начала.
Как бы вы это ни делали, будет оставшееся состояние гонки . При одновременной загрузке записи несколько транзакций могут попытаться ввести один и тот же p_id
в обе таблицы практически в одно и то же время, но пока не увидеть p_id
в таблице other , и введите его в обе таблицы. Это помогает держать транзакции короткими, чтобы минимизировать временные рамки, но проблема остается в принципе.
Одним чистым решением будет одна таблица painting
с флагом boolean
, указывающей ее статус. Это может иметь только одно состояние за раз. Детали зависят от вашей полной ситуации …
В стороне: пересмотреть регистр написания идентификаторов в CaMeL в Postgres.
- Имена столбцов PostgreSQL чувствительны к регистру?
1
Erwin Brandstetter
2 Дек 2019 в 02:04
Вы снова INSERT
в триггере AFTER INSERT
, вызывая повторный запуск триггера в течение этой секунды INSERT
, который снова INSERT
запускает и запускает триггер заново, и так далее, и так далее , В какой-то момент стек исчерпан всеми вызовами этой функции, и вы получите ошибку.
Удалите INSERT
из функций триггера и просто RETURN new
. Возвращение new
приведет к завершению оригинала INSERT
. Для триггеров AFTER INSERT
нет необходимости вручную INSERT
в функции триггера.
Как:
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;
И аналог для другой триггерной функции.
3
sticky bit
30 Ноя 2019 в 00:49