依赖OLD、NEW、TG_OP的PL/pgSQL触发器函数能否标记为IMMUTABLE？

Short answer: No, you should never mark a trigger function that relies on OLD, NEW, TG_OP, or any other trigger-specific special variables as IMMUTABLE.

Let’s break down why this is the case, using PostgreSQL’s own definitions and behavior:

1. What PostgreSQL Means by IMMUTABLE #

PostgreSQL’s documentation clearly defines an IMMUTABLE function as one that:

Given the same input parameters, always returns the same result, regardless of when it’s called or the state of the database.

The key here is "input parameters" — these refer to the explicit parameters defined in the function’s signature, not implicit context variables injected by the trigger system.

2. Trigger Special Variables Are Not Function Parameters #

Variables like OLD, NEW, and TG_OP are not part of your trigger function’s declared parameters. Instead, they’re dynamic context values that PostgreSQL passes to the function at runtime based on the specific trigger event (e.g., an INSERT vs. UPDATE operation, or the row being modified).

For example:

• A trigger function using TG_OP will return different results if it’s fired by an INSERT versus an UPDATE.
• A function using NEW will behave differently depending on the data in the row being inserted or updated.

These variations have nothing to do with the function’s explicit input parameters (most trigger functions have no declared parameters at all!) — they depend on external state that changes with each trigger invocation. This directly violates the IMMUTABLE contract.

3. The Risks of Mislabeling as IMMUTABLE #

If you incorrectly mark a trigger function as IMMUTABLE, PostgreSQL will optimize it under the assumption that its result is fixed for a given set of parameters. This can lead to:

• Cached results being reused across different trigger events (e.g., returning an old row value when it should return a new one).
• Data inconsistencies or silent failures, as the database’s optimizations clash with the function’s actual dynamic behavior.

4. What Stability Marker Should You Use? #

Most trigger functions should be marked as VOLATILE (the default if you don’t specify anything), since they often modify data (via NEW/OLD) or depend on highly dynamic context.

If your trigger function only reads data (and doesn’t modify it or depend on transaction-specific state), you might be able to use STABLE — but VOLATILE is the safest choice for most trigger use cases.

内容的提问来源于stack exchange，提问作者Kotsius