postgresql 邮政。超过 plpgsql 堆栈深度限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16696757/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres. plpgsql stack depth limit exceeded
提问by user2410258
im working on a simple function where it automatically updates something from a table.
我正在研究一个简单的功能，它会自动更新表格中的某些内容。
create or replace function total()
returns void as $$
declare
sum int;
begin
sum = (SELECT count(copy_id) FROM copies);
update totalbooks
set all_books = sum
where num = 1;
end;
$$ language plpgsql;
if i execute "select total();" it works perfectly fine so i made a function trigger so that it automatically updates:
如果我执行“select total();” 它工作得很好，所以我做了一个函数触发器，以便它自动更新：
create or replace function total1() returns trigger as $$
begin
perform (select total());
return null;
end;
$$ language plpgsql;
but after i execute this:
但是在我执行这个之后：
create trigger total2
after update
on totalbooks
for each row
execute procedure total1();
it gives me an error message:
它给了我一条错误消息：
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 3072kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL statement "SELECT (SELECT count(copy_id) FROM copies)"
PL/pgSQL function total() line 5 at assignment
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
Obviously theres something wrong with my trigger. Please help.
显然我的触发器有问题。请帮忙。
I am using Postgres 9.2.4, compiled by Visual C++ build 1600, 64-Bit
我使用的是 Postgres 9.2.4，由 Visual C++ build 1600, 64-Bit 编译
EDIT:
编辑：
i tried the pg_trigger_depth(), but now trigger doesn't automatically update?? i still have to execute 'select total()'
我尝试了 pg_trigger_depth()，但现在触发器不会自动更新？？我仍然必须执行“select total()”
here's my new code:
这是我的新代码：
create or replace function total()
returns void as $$
declare
sum int;
begin
sum = (SELECT count(copy_id) FROM copies);
update totalbooks
set all_books = sum;
end;
$$ language plpgsql;
create or replace function total1() returns trigger as $$
begin
perform (select total());
return null;
end;
$$ language plpgsql;
create trigger total2
after update
on totalbooks
for each row
WHEN (pg_trigger_depth()=0)
execute procedure total1();
回答by Scott S
Ok, if you really want the trigger on update, what you could do it set this trigger as column specific, so that it is not fired on an update to all_books, which is causing your recursion. Something like this -
好的，如果您真的想要更新时的触发器，您可以将这个触发器设置为特定于列的触发器，这样它就不会在更新到 时触发all_books，这会导致您的递归。像这样的东西——
create trigger total2
after update of copy_id
on totalbooks
for each row
execute procedure total1();
Of course, you can change which columns trigger the function, I just chose copy_idbecause that is what you are counting.
当然，您可以更改哪些列会触发该功能，我只是选择了，copy_id因为那是您正在计算的。
HOWEVER
然而
If you are updating with a count()result, you can just put the trigger on INSERTand DELETEactions. This way the trigger will fire when the count changes, but will not itself be triggered by the update. // EDIT: Since your sumis only a count of all records in copies, it will only change when a record is inserted or updated, so running this trigger on update would not make sense anyway.
如果您正在更新count()结果，您只需打开触发器INSERT并DELETE执行操作即可。这样触发器会在计数改变时触发，但不会被更新触发。// 编辑：由于您sum只是 中所有记录的计数copies，因此它只会在插入或更新记录时更改，因此在更新时运行此触发器无论如何都没有意义。
EDIT: I figured it would be useful to add a link to the CREATE TRIGGER Documentation. See the section labeled "event", because this details how to specify columns in the event.
编辑：我认为添加指向CREATE TRIGGER Documentation的链接会很有用。请参阅标有“事件”的部分，因为这详细说明了如何在事件中指定列。
EDIT FOR NEW INFORMATION:
编辑新信息：
Given what it sounds like you need to accomplish, I think you need to rethink your data design, I suggest you use a parent-child relationship (Anytime you are caching shared data on many rows in a table because they share something in common, that is a sign that you might need a parent table instead).
鉴于听起来您需要完成什么，我认为您需要重新考虑您的数据设计，我建议您使用父子关系（任何时候您在表中的许多行上缓存共享数据，因为它们共享一些共同点，即是一个迹象，表明您可能需要一个父表）。
Have a bookstable where each row is information about one book (title, author, etc), and then have a copiestable where each row holds information about one copy of a book (serial number, last checked out, etc).
有一个books表格，其中每一行是关于一本书的信息（书名、作者等），然后有一个copies表格，其中每一行都包含关于一本书的一份副本的信息（序列号、最后一次签出等）。
That way, getting the count of copies is as simple as SELECT COUNT(*) FROM copies WHERE book_id=[some book id].
这样，获取副本的数量就像SELECT COUNT(*) FROM copies WHERE book_id=[some book id].
If you really want to cache the count somewhere, do it on the bookstable.
如果您真的想在某处缓存计数，请在books表上进行。
Create an INSERT OR UPDATEtrigger on copiesthat does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id.
创建INSERT OR UPDATE触发器上copies，做UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id。
Then create a DELETEtrigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id
然后DELETE在副本上创建一个触发器UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id
The reason for two triggers is that the NEWvariable is only available in INSERTor UPDATEtriggers, and OLDis only available in DELETEtriggers. You could do it all as one trigger, but that requires more code than I wanted to put here.
两个触发器的原因是NEW变量只在INSERT或UPDATE触发器中可用，并且OLD只在DELETE触发器中可用。您可以将这一切作为一个触发器来完成，但这需要的代码比我想放在这里的要多。
Make sure all your triggers are AFTERtriggers, or else a newly inserted/deleted row won't be considered in the count.
确保您的所有触发器都是AFTER触发器，否则新插入/删除的行将不会被考虑在计数中。
回答by Denis de Bernardy
Look into pg_trigger_depth:
查看 pg_trigger_depth：
http://www.postgresql.org/docs/9.2/static/functions-info.html
http://www.postgresql.org/docs/9.2/static/functions-info.html
(9.2 or later)
（9.2 或更高版本）
回答by Fernando Meneses Gomes
Inside your trigger:
在触发器内部：
IF pg_trigger_depth() < 2 THEN
PERFORM total();
END IF;
