Stack Depth Limit exceeded in PostgresQL (After Delete Trigger)

Question

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

Error:

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

Background:

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

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.

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.

• wildplasser: A cascading delete via the child->parent FK will result in all children getting deleted if a parent is deleted. Your trigger does the opposite. So once a kid is deleted, its parent and its siblings are deleted. Is this your intention?
• samol: That is right. If a child is deleted, delete the parent as well as all its silbings
• wildplasser: BTW: why do you use varchar(255) fields as PK and FK ?
• samol: It is not actually VARCHAR(255). The server is on fire right now so I just quickly wrote a contrieved example instead of copying the real schema. It is a UUID.

Answer 1

So far you that's what happens:

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.

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

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

• ALTER TABLE to add ON DELETE CASCADE statement

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

Should be faster, too.

• samol: Hi @erwin thanks for the solution. It seems like the only thing the changed was the addition of DELETE FROM child WHERE parent_id = OLD.parent_id; Why does that reduce the number of invocations?
• samol: Also, when a parent is deleted it cascades to the child, and the child deletion triggers to try to delete the parent again (which at that point no longer exists)
• Erwin Brandstetter: @samol: Sorry, I forgot to add the essential detail: Drop ON DELETE CASCADE from the FK constraint.
• samol: But I will need the on delete cascade trigger, because sometimes I delete the parent and I need that delete to cascade
• Erwin Brandstetter: @samol: You could drop any child instead for the same effect. Also, I am not entirely sure your problem wasn't caused by additional triggers / constraints you mentioned.