Fixing a PostgreSQL Trigger That Fires Twice on UPDATE With a Row-Level Rule

You added an audit trigger to a table, and now every UPDATE writes two rows to your log instead of one. The data looks correct, but the duplication is real and consistent. Before you start questioning your trigger logic, check whether you have a RULE on that table — because that's almost certainly the culprit.

What You'll Learn

• Why PostgreSQL row-level rules cause triggers to fire more than once
• How to reproduce and confirm the problem in isolation
• Three concrete fixes, from simplest to most defensive
• When to use rules versus triggers and why the answer is almost always "triggers"

What's Actually Happening Under the Hood

PostgreSQL rules are a query-rewriting mechanism. When you define a DO ALSO rule on a table for UPDATE, PostgreSQL doesn't modify the original statement — it adds an additional statement. Your single UPDATE orders SET status = 'shipped' WHERE id = 42 gets rewritten internally into two separate statements that both touch the same row.

Row-level triggers fire once per row per statement. If the rule rewrites your update into two statements, your FOR EACH ROW trigger fires once for each of those statements — giving you two executions for what looks like one user-initiated change. The trigger itself is doing nothing wrong.

How PostgreSQL Rules Work (and Why They Surprise You)

Rules live in the system catalog and intercept statements at the parser/rewriter level, before execution. A DO ALSO rule appends an extra action; a DO INSTEAD rule replaces the original. Either variant can change how many times a trigger fires.

The tricky part is that rules are invisible to most query inspection tools. Running EXPLAIN on your UPDATE won't show you the rule rewrite. You have to query pg_rules or pg_rewrite directly to discover they exist. Many developers inherit a database with rules in place and have no idea they're there.

For a related example of unexpected row-modification behavior in PostgreSQL, see how UPDATE FROM can silently modify the wrong rows when joins are involved — the root cause is a similar mismatch between what you think the planner is doing and what it actually does.

Reproducing the Problem

Set up a minimal test case so you can confirm the behavior before touching your production schema.

-- Create a target table
CREATE TABLE orders (
  id     SERIAL PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending'
);

-- Create an audit log table
CREATE TABLE order_audit (
  audit_id   SERIAL PRIMARY KEY,
  order_id   INT,
  old_status TEXT,
  new_status TEXT,
  changed_at TIMESTAMPTZ DEFAULT now()
);

-- Create the audit trigger function
CREATE OR REPLACE FUNCTION log_order_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO order_audit (order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger
CREATE TRIGGER trg_order_audit
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_change();

-- Now add a DO ALSO rule (this is the problem)
CREATE RULE rule_order_update AS
  ON UPDATE TO orders
  DO ALSO NOTIFY order_changes;

-- Seed a row and run an update
INSERT INTO orders (status) VALUES ('pending');
UPDATE orders SET status = 'shipped' WHERE id = 1;

-- Check the audit log
SELECT * FROM order_audit;

You'll see two rows in order_audit for that single UPDATE. The NOTIFY action in the rule is harmless, but even that trivial DO ALSO causes the trigger to execute twice.

Diagnosing the Duplicate Fire

If you're debugging an existing system rather than building from scratch, start by checking for rules on the affected table.

-- List all rules on a specific table
SELECT rulename, ev_type, is_instead
FROM pg_rules
WHERE tablename = 'orders';

ev_type values map to 1 = SELECT, 2 = UPDATE, 3 = INSERT, 4 = DELETE. If you see any UPDATE rules (ev_type = '2') alongside your trigger, you've found the interaction.

You can also check pg_rewrite for the raw rewrite rules attached to a relation:

SELECT r.rulename, r.ev_type, r.is_instead
FROM pg_rewrite r
JOIN pg_class c ON c.oid = r.ev_class
WHERE c.relname = 'orders';

Once you confirm the rule exists, add a temporary RAISE NOTICE to your trigger function to count executions per transaction — this gives you hard evidence of how many times it fires per statement.

CREATE OR REPLACE FUNCTION log_order_change()
RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'Trigger fired for order_id=%', NEW.id;
  INSERT INTO order_audit (order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Run your UPDATE again and watch the server log or psql output. Seeing the notice twice confirms the trigger is executing twice per logical update.

Fix 1: Drop the Rule and Use a Trigger Instead

This is the right answer in the vast majority of cases. PostgreSQL rules are a legacy mechanism with sharp edges. If the rule was added to implement side-effect logic (logging, notification, cascading changes), convert it to a trigger.

-- Remove the rule entirely
DROP RULE rule_order_update ON orders;

-- Fold the rule's intent into a separate trigger if needed
CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS TRIGGER AS $$
BEGIN
  NOTIFY order_changes;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_notify
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Now both the audit and the notification run as triggers. Each fires exactly once per updated row. No rewriting, no surprises.

If you can't drop the rule because another team owns it or it's part of a view dependency, use one of the two defensive fixes below instead.

Fix 2: Make the Trigger Idempotent

If you can't change the rule, you can make the trigger smart enough to ignore duplicate executions for the same logical change. The key is tracking whether the trigger has already run for this row within the current transaction.

CREATE OR REPLACE FUNCTION log_order_change()
RETURNS TRIGGER AS $$
BEGIN
  -- Skip if old and new values are identical (second rule-driven firing)
  -- Also skip if audit already has a row for this txid + order_id
  IF EXISTS (
    SELECT 1 FROM order_audit
    WHERE order_id = NEW.id
      AND new_status = NEW.status
      AND changed_at >= (now() - interval '1 second')
  ) THEN
    RETURN NEW;
  END IF;

  INSERT INTO order_audit (order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This approach works but has a race condition risk under high concurrency. It's acceptable for low-volume audit tables. For precision, use the transaction ID approach in Fix 3.

Fix 3: Guard With a Session Variable

A more reliable idempotency guard uses a PostgreSQL session-level variable to mark that the trigger has already run for a given row in this transaction. Session variables in plpgsql are set with SET LOCAL and persist only for the duration of the transaction.

CREATE OR REPLACE FUNCTION log_order_change()
RETURNS TRIGGER AS $$
DECLARE
  guard_key TEXT;
BEGIN
  guard_key := 'audit.fired.' || NEW.id::TEXT;

  -- If already fired in this transaction, skip
  IF current_setting(guard_key, true) = 'yes' THEN
    RETURN NEW;
  END IF;

  -- Mark as fired for this transaction
  PERFORM set_config(guard_key, 'yes', true);

  INSERT INTO order_audit (order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

set_config(key, value, is_local) with is_local = true sets the variable for the current transaction only. It resets automatically on commit or rollback. current_setting(key, missing_ok) with missing_ok = true returns NULL instead of raising an error if the key doesn't exist yet — that's what the second argument true does.

This pattern is transaction-safe and doesn't require a table lookup, making it more efficient than Fix 2 at scale.

Common Pitfalls to Avoid

Assuming EXPLAIN shows rule rewrites. It doesn't. EXPLAIN shows the execution plan after rewriting, but it won't explicitly label which statements originated from a rule. Always check pg_rules when you see duplicate side effects.

Using DO INSTEAD rules without understanding the consequences. A DO INSTEAD rule suppresses the original statement. If your trigger depends on the original statement firing, it won't. This is a separate but related failure mode.

Confusing statement-level and row-level trigger counts. A FOR EACH STATEMENT trigger fires once per SQL statement, not once per row. If a rule adds a second statement, a statement-level trigger also fires twice. Don't assume statement-level is immune to this problem.

Forgetting that views use rules internally. In PostgreSQL, updatable views are implemented using DO INSTEAD rules under the hood. If your trigger is on a view's underlying table and you're updating through the view, you may see similar multi-fire behavior depending on how the view rule is structured. Check how PostgreSQL handles index use in JSONB queries for another example of the planner making decisions that don't match your mental model.

Applying Fix 2 or Fix 3 when Fix 1 is available. Defensive guards in trigger functions add complexity and maintenance burden. If you can remove the rule, do that first. Keep the trigger clean.

For another class of subtle PostgreSQL data issues, the article on COPY FROM silently skipping rows due to null delimiter mismatches shows how PostgreSQL's defaults can produce unexpected results without raising errors — the same philosophy applies here.

Wrapping Up

A trigger firing twice on UPDATE is almost always a rule-trigger interaction, not a bug in your trigger logic. The fix path is straightforward once you know what to look for.

Here are your next steps:

1. Run SELECT * FROM pg_rules WHERE tablename = 'your_table' and identify any UPDATE rules on tables with triggers.
2. Drop DO ALSO rules and replace their logic with additional trigger functions — this is the cleanest solution.
3. If the rule can't be removed, implement the session variable guard (Fix 3) in your trigger function.
4. Add a RAISE NOTICE count to your trigger during development so you catch duplicate firings early, before they reach production audit logs.
5. Document any rules in your schema the same way you document triggers — they affect behavior just as significantly but are much easier to miss during code review.