This error occurs when a trigger updates the same table that triggered it, causing infinite recursion until PostgreSQL hits the stack depth limit. Fix it by adding conditional checks or using pg_trigger_depth() to prevent the trigger from re-executing on its own updates.
In PostgreSQL, a "Too many trigger recursions" error (often appearing as "ERROR: stack depth limit exceeded") happens when a trigger performs an UPDATE or INSERT on the same table, which fires the trigger again, which makes another update, and so on infinitely. The database protects itself by enforcing a maximum_stack_depth limit (typically 2MB by default). Since the recursion is infinite in nature, the stack fills up and PostgreSQL terminates the operation. This is not actually a separate error code but rather a symptom of uncontrolled trigger recursion hitting PostgreSQL's resource limits.
The safest solution is to use PostgreSQL's pg_trigger_depth() function to check the recursion level and abort if recursion is detected:
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
-- Only execute on the first trigger level, skip recursive calls
IF pg_trigger_depth() > 1 THEN
RETURN NEW;
END IF;
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER timestamp_trigger
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();In this approach, the trigger only executes on the initial call (depth = 1). Any recursive invocations immediately return NEW without executing the update logic.
Modify your trigger to check if the update is actually necessary before executing. This stops recursion by ensuring the trigger doesn't update the row if it's already in the correct state:
CREATE OR REPLACE FUNCTION sync_total()
RETURNS TRIGGER AS $$
BEGIN
-- Only update if the total has actually changed
IF NEW.total <> OLD.total THEN
UPDATE orders SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.order_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_trigger
AFTER UPDATE ON items
FOR EACH ROW
EXECUTE FUNCTION sync_total();The condition NEW.total <> OLD.total ensures the parent table is only updated when necessary, preventing infinite loops.
PostgreSQL's WHEN clause allows you to make triggers conditional at the SQL level, avoiding unnecessary trigger function calls:
CREATE TRIGGER update_on_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (NEW.price IS DISTINCT FROM OLD.price)
EXECUTE FUNCTION update_last_modified();The WHEN clause evaluates before the trigger function is called. If the condition is false, the function is never invoked, eliminating potential recursion and improving performance.
If a trigger needs to update a parent or related table, consider whether you can restructure the logic:
-- BAD: Trigger updates the same table
CREATE OR REPLACE FUNCTION process_item()
RETURNS TRIGGER AS $$
BEGIN
NEW.status := 'processed';
-- This RETURN NEW modifies data without triggering recursion
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER process_trigger
BEFORE UPDATE ON items
FOR EACH ROW
EXECUTE FUNCTION process_item();Key insight: Modifying NEW/OLD in a BEFORE trigger does NOT cause recursion because it's setting values, not executing UPDATE statements. Use this approach instead of UPDATE statements in AFTER triggers when modifying the same row.
For migrations or bulk updates that would trigger infinite loops, temporarily disable the triggers:
-- Disable all triggers on the table
ALTER TABLE items DISABLE TRIGGER sync_total_trigger;
-- Perform the bulk update safely
UPDATE items SET total = 0 WHERE status = 'reset';
-- Re-enable triggers
ALTER TABLE items ENABLE TRIGGER sync_total_trigger;Alternatively, use the CONSTRAINT trigger mode for more granular control:
ALTER TABLE items DISABLE TRIGGER USER; -- Disable all user-created triggers, keep system onesFor complex scenarios with multiple related triggers, use session variables to control trigger execution:
CREATE OR REPLACE FUNCTION sync_parent()
RETURNS TRIGGER AS $$
BEGIN
-- Check if recursion control flag is set
IF current_setting('app.skip_triggers', true) = 'on' THEN
RETURN NEW;
END IF;
-- Set flag to prevent other triggers from executing
SET app.skip_triggers = 'on';
UPDATE parent_table SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.parent_id;
-- Reset flag
RESET app.skip_triggers;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;This approach allows fine-grained control when multiple interdependent triggers exist.
Important: "Too many trigger recursions" is not a separate PostgreSQL error code—it's the result of hitting the max_stack_depth limit due to infinite trigger recursion. The default max_stack_depth is 2MB on most platforms, which PostgreSQL sets conservatively to avoid crashes. Increasing max_stack_depth (via ALTER SYSTEM SET max_stack_depth) is NOT a fix because infinite recursion will still occur, just with a longer stack trace. The real solution is always to prevent the recursive logic. When using pg_trigger_depth(), remember it returns the current nesting level (1 on initial call, 2 on first recursion, etc.), so check for > 1 to skip recursive calls. PostgreSQL also provides NEW and OLD variables in trigger functions—modifying these values in BEFORE triggers executes the modification without firing the trigger again, making this safer than UPDATE statements. For performance, also note that every UPDATE in a trigger generates additional WAL logging and dead tuples for VACUUM to clean up, so even controlled recursion can hurt performance if not carefully bounded.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL