How to fix Stack depth limit exceeded in PostgreSQL

-- If it's a large IN clause
SELECT * FROM table WHERE id IN (val1, val2, ..., val5000);

-- If it's a trigger issue, check trigger definitions
SHOW create_trigger information;

-- If it's a function, identify recursive calls

-- Instead of this (causes stack depth error with 5000+ items):
DELETE FROM my_table WHERE id IN (1, 2, 3, ..., 5000);

-- Use this approach:
CREATE TEMP TABLE doomed AS VALUES (1), (2), (3), ..., (5000);
DELETE FROM my_table WHERE id IN (SELECT column1 FROM doomed);
DROP TABLE doomed;

-- Or with a CTE:
WITH doomed AS (
  VALUES (1), (2), (3), ..., (5000)
)
DELETE FROM my_table WHERE id IN (SELECT * FROM doomed);

-- Problem: AFTER trigger causing recursion
CREATE TRIGGER my_trigger AFTER UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION update_other_column();

FUNCTION update_other_column() THEN
  UPDATE my_table SET other_col = NEW.some_col WHERE id = NEW.id;
END;

-- Solution 1: Use BEFORE trigger to modify NEW directly
CREATE TRIGGER my_trigger BEFORE UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION set_other_column();

FUNCTION set_other_column() BEGIN
  NEW.other_col := NEW.some_col;
  RETURN NEW;
END;

-- Solution 2: Guard recursion with pg_trigger_depth()
CREATE TRIGGER my_trigger AFTER UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION update_other_column_guarded();

FUNCTION update_other_column_guarded() BEGIN
  IF pg_trigger_depth() = 1 THEN  -- Only run at first level
    UPDATE my_table SET other_col = NEW.some_col WHERE id = NEW.id;
  END IF;
  RETURN NEW;
END;

-- Instead of a recursive function (memory-intensive)
CREATE FUNCTION traverse_tree(node_id INT) RETURNS TABLE(...) AS $$
BEGIN
  RETURN QUERY SELECT * FROM nodes WHERE id = node_id;
  RETURN QUERY SELECT * FROM traverse_tree(parent_id) FROM nodes WHERE id = node_id;
END;
$$ LANGUAGE plpgsql;

-- Use recursive CTE (more efficient)
WITH RECURSIVE tree_traversal AS (
  SELECT id, parent_id, name FROM nodes WHERE id = $1
  UNION ALL
  SELECT n.id, n.parent_id, n.name
  FROM nodes n
  INNER JOIN tree_traversal t ON n.id = t.parent_id
)
SELECT * FROM tree_traversal;

SHOW max_stack_depth;  -- Default: 2MB

SET max_stack_depth = '4MB';  -- Session only

-- Or permanently in postgresql.conf:
-- max_stack_depth = 4MB

ulimit -s  # Check current stack limit
ulimit -s unlimited  # Increase if needed