Stack depth limit exceeded in PostgresQL (after trigger removal)
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();
Mistake:
stack depth limit exceeded
hint: "Increase the 'max_stack_depth' config parameter (currently 6144kB) after the platform / stack depth limit is sufficient.
Background:
- A
parent
can have manychildren
- The schema is designed in such a way that if a
parent
is deleted, all records of its children are also deleted. - If a
child
is removed, the trigger removesparent
and then the cascade removes all otherschildren
associated with itparent
This worked for months and today we suddenly started getting this error.
I can't seem to find, though there may be infinite recursion, and I'm considering doubling the stack depth limit to see what's going on.
Note. The actual schema is more complex than this and has a few more related tables with CASCADE drop constraints. But this is the only trigger.
UPDATE . So I doubled the max_stack_depth limit and everything is fine now. I don't think this is a good solution and I still don't know how I can, for example, prevent this from happening in the future.
source to share
So far, you what is happening:
- Remove child1.
- Starts removing the parent.
- Removes siblings of child1 by .
n
DELETE CASCADE
- Calls the same trigger .
n
- There are no more brothers and sisters left.
No endless loop, but still trigger calls. This might explain why your stack depth limit was exceeded, but you can fix it by increasing the limit. The same can happen again with more . n
n
Alternatively, replace the trigger with the following:
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
. Sample code:
Now, for the DELETE
whole family, you cannot delete the parent like you did before (FK is now forbidden). Instead of DELETE
any child.
It should be faster.
source to share