On Fri, 2025-06-20 at 14:37 -0400, Tom Lane wrote:
> I think the fundamental problem you're having is that you marked
> this function IMMUTABLE, which gives the planner license to
> pre-evaluate it. It had better be VOLATILE to discourage advance
> evaluation.
>
> https://www.postgresql.org/docs/current/xfunc-volatility.html
What you say is true in the narrow sense that VOLATILE prevents
planner-time evaluation. But it doesn't generalize into a mathematical
rule about how to mark a function: one might conclude (falsely)
that IMMUTABLE functions must not be able to throw exceptions, but
clearly they can. For instance, int4div():
EXPLAIN SELECT * FROM a WHERE (1/1 = 0);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
EXPLAIN SELECT * FROM a WHERE (1/0 = 0);
ERROR: division by zero
If you try to fix that by marking int4div as VOLATILE, you'd have to do
the same for int4pl (which can overflow), and any function that can run
out of memory.
The way I see it, the problem is not the function marking, nor is it
the evaluation order. It's that there's an expression in the query
that's impossible to evaluate.
Todd, you mentioned that you started from a more complex scenario, can
you give some more details about how such an expression ended up in the
original query, and what you'd like to happen?
Regards,
Jeff Davis
