在PostgresQL中超出堆栈深度限制（删除触发器之后） [英] Stack Depth Limit exceeded in PostgresQL (After Delete Trigger)

问题描述

CREATE TABLE parent (
   parent_id VARCHAR(255) PRIMARY KEY
);

CREATE TABLE child (
   parent_id VARCHAR(255) REFERENCES parent ON DELETE CASCADE,
   child_id VARCHAR(255) PRIMARY KEY
);

CREATE OR REPLACE FUNCTION delete_parent()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM parent WHERE parent_id = OLD.parent_id;
    RETURN NULL;
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER delete_parent AFTER DELETE
ON child 
FOR EACH ROW
EXECUTE PROCEDURE delete_parent();

错误：

已超过堆栈深度限制
提示：在确保平台的堆栈深度限制足够后，增加配置参数 max_stack_depth（当前为6144kB）。

stack depth limit exceeded hint: 'Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform\'s stack depth limit is adequate.'

背景：

• A 父母可以有许多孩子


• 该方案旨在使父母被删除，其所有子记录也被删除。


• 如果删除了孩子，触发器将删除父级，并且然后级联删除与该父母

• A parent can have many children
• The schema is designed to so that if a parent is deleted, all of its children records are also removed.
• If a child is deleted, the trigger deletes the parent, and then cascade deletes all the other children related to that parent

这已经工作了好几个月了，今天突然我们开始出现此错误。

This has worked for months and today suddenly we started getting this error.

我可能找不到无限的时间递归，我正在考虑将堆栈深度限制加倍以查看会发生什么。

I can't find while there might be an infinite recursion and I am considering doubling the stack depth limit just to see what happens.

注意：实际的架构比这更复杂，并且有一些相关的表CASCADE删除约束。但这是唯一的触发器。

Note: The actual schema is more complex than this and has a few more related tables that have CASCADE delete constraints. But this is the only trigger.

更新：所以我将max_stack_depth限制加倍了，现在可以了。我认为这不是一个好的解决方案，我仍然不确定如何防止这种情况将来发生。

UPDATE: So I doubled the max_stack_depth limit and now it is fine. I don't think this is a good solution and I am still unsure how I can for example prevent this from happening in the future.

推荐答案

到目前为止，这就是发生的事情：

So far you that's what happens:

1. 删除child1。


2. 触发删除父对象。


3. 将child1的 n 个兄弟姐妹删除删除级联。


4. 调用相同的触发器 n 次。


5. 没有兄弟姐妹了。

1. Delete child1.
2. Triggers deletion of parent.
3. Deletes n siblings of child1 by DELETE CASCADE.
4. Calls same trigger n times.
5. No more siblings left.

没有无限循环，但仍然触发的 n 调用。那可以解释为什么超出了堆栈深度限制，但是您可以通过增加限制来解决。 n 可能再次发生相同的情况。

No endless loop, but still n invocations of the trigger. That could explain why your stack depth limit was exceeded, but you could fix it by increasing the limit. The same could happen again with a greater n.

另外，将触发器替换为：

As an alternative, replace your trigger with:

CREATE OR REPLACE FUNCTION delete_family()
  RETURNS TRIGGER AS
$func$
BEGIN
    DELETE FROM child  WHERE parent_id = OLD.parent_id;
    DELETE FROM parent WHERE parent_id = OLD.parent_id;  -- done after 1st call
    RETURN NULL;
END
$func$ LANGUAGE plpgsql;  -- don't quote the language name!

CREATE TRIGGER delete_family
AFTER DELETE ON child 
FOR EACH ROW EXECUTE PROCEDURE delete_family();

并用 无版本替换FK约束strong> 删除级联。代码示例：

And replace the FK constraint with a version without ON DELETE CASCADE. Code example:

• 在ALTER TABLE上添加DELETE CASCADE语句

• ALTER TABLE to add ON DELETE CASCADE statement

现在，要删除整个家庭的$code> Delete ，您不能像以前一样删除父级（现在FK禁止）。而是删除任何一个孩子。

Now, to DELETE a whole family, you can't delete the parent like before (now forbidden by FK). Instead DELETE any child.

也应该更快。

这篇关于在PostgresQL中超出堆栈深度限制（删除触发器之后）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！