CREATE RULE rewrites PostgreSQL queries and attaches actions to database-object events. A tempting audit rule can appear to log an inserted row while silently changing the statement’s behavior. section 5.2
The audit rule that records the wrong ID
A rule intended to log manual insertions uses DO ALSO to insert NEW.id into an audit table. Inserting order_groups id 500001 produces an audit record for 500002. PostgreSQL rewrites the original INSERT and audit INSERT into separate effective queries; each nextval() call advances the sequence. section 5.2
Rules are PostgreSQL-specific and function as internal query-rewrite machinery rather than predictable row-level callbacks. Rewriting can duplicate effects, making VOLATILE functions especially risky because they may execute multiple times. TRIGGER is the safer choice for this audit requirement. section 5.2 section 5.7
A protective update rule
A DO INSTEAD NOTHING rule can block updates to orders older than one year. Test its predicate with a sampled SELECT, run the UPDATE inside a transaction, inspect the plan, and ROLLBACK before adopting it.
CREATE OR REPLACE RULE log_order_insertions AS
ON INSERT TO erp.order_groups
DO ALSO
INSERT INTO audit.audit_log (what, who, id, tstamp)
VALUES ('Manual order insertion',
CURRENT_ROLE::text, NEW.id, clock_timestamp());
Inspect the complete audit-rule definition and its rewritten behavior alongside the resulting audit rows. section 5.2
