Postgres.超出了plpgsql堆栈深度限制

问题描述

我正在研究一个简单的功能，该功能可以自动更新表中的内容.

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;

如果我执行"select total();"它工作得很好，所以我做了一个函数触发器，以便它自动更新:

if i execute "select total();" it works perfectly fine so i made a function trigger so that it automatically updates:

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();

它给我一条错误消息:

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.

我使用的是Postgres 9.2.4，由Visual C ++ build 1600 64位编译

I am using Postgres 9.2.4, compiled by Visual C++ build 1600, 64-Bit

我尝试了pg_trigger_depth()，但是现在触发器不会自动更新?我仍然必须执行'select total()'

i tried the pg_trigger_depth(), but now trigger doesn't automatically update?? i still have to execute 'select total()'

这是我的新代码:

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();

推荐答案

好吧，如果您确实希望在更新时使用触发器，可以执行以下操作将该触发器设置为特定于列的触发器，以便在更新到该触发器时不会触发该触发器all_books，这导致您的递归.像这样-

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 -

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

当然，您可以更改触发该功能的列，我只是选择copy_id，因为这就是您要计数的内容.

Of course, you can change which columns trigger the function, I just chose copy_id because that is what you are counting.

但是

如果要使用count()结果进行更新，则可以仅将触发器置于INSERT和DELETE操作上.这样，当计数更改时将触发触发器，但更新本身不会触发该触发器. //由于您的sum仅是copies中所有记录的计数，因此仅在插入或更新记录时才会更改，因此无论如何在更新上运行此触发器都毫无意义.

If you are updating with a count() result, you can just put the trigger on INSERT and DELETE actions. This way the trigger will fire when the count changes, but will not itself be triggered by the update. // Since your sum is 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.

我认为将链接添加到创建触发器文档.请参阅标为事件"的部分，因为这详细说明了如何在事件中指定列.

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.

考虑到您需要完成的工作，我认为您需要重新考虑数据设计，建议您使用父子关系(任何时候在表中的许多行上缓存共享数据，因为它们共享某些内容.常见，这表示您可能需要一个父表).

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).

有一个books表，其中每一行是关于一本书的信息(书名，作者等)，然后有一个copies表，其中每一行是有关一本书的副本的信息(序列号，最后检查一次)等等).

Have a books table where each row is information about one book (title, author, etc), and then have a copies table where each row holds information about one copy of a book (serial number, last checked out, etc).

这样，获取份数就和SELECT COUNT(*) FROM copies WHERE book_id=[some book id]一样简单.

That way, getting the count of copies is as simple as SELECT COUNT(*) FROM copies WHERE book_id=[some book id].

如果您真的想将计数缓存在某个地方，请在books表上进行.

If you really want to cache the count somewhere, do it on the books table.

在执行UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id的copies上创建一个INSERT OR UPDATE触发器.

Create an INSERT OR UPDATE trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id.

然后在执行UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id

有两个触发器的原因是NEW变量仅在INSERT或UPDATE触发器中可用，而OLD仅在DELETE触发器中可用.您可以将所有操作作为一个触发器来完成，但这需要比我想在此处输入的代码更多的代码.

The reason for two triggers is that the NEW variable is only available in INSERT or UPDATE triggers, and OLD is only available in DELETE triggers. You could do it all as one trigger, but that requires more code than I wanted to put here.

确保所有触发器都是AFTER触发器，否则计数中将不考虑新插入/删除的行.

Make sure all your triggers are AFTER triggers, or else a newly inserted/deleted row won't be considered in the count.

这篇关于Postgres.超出了plpgsql堆栈深度限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！