Re: Proposal: pg_is_volatile function

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> čt 20. 2. 2025 v 13:48 odesílatel Andrew Farries <andrew(dot)farries(at)xata(dot)io>
> napsal:
>> I believe the implementation of this function would be straightforward
>> with a
>> new function in `src/backend/utils/adt/misc.c` delegating to the existing
>> `contain_volatile_functions_after_planning` function in
>> `src/backend/optimizer/util/clauses.c`.

> If this feature can be implemented, then it needs to be implemented like a
> pseudo function, it cannot not be a classic function.

Yeah, this doesn't seem anywhere near as straightforward to implement
as all that. For one thing, you'd probably rather that the argument
expression not be evaluated at all, which an ordinary function could
not prevent. But there's also semantic questions about where exactly
in the planning process we ought to try to capture volatility.
Here are a couple of examples:

pg_is_volatile(CASE WHEN 0 > 1 THEN random() ELSE 0 END)

We'd get different answers if we allow const-folding to happen
before examining the expression than if we don't.

create function myf() returns int as 'select 1' language sql;

pg_is_volatile(myf())

myf() is marked volatile (by default), but after function inlining
the expression would just be a constant. Which answer do you want?

For the specific use-case of determining what ALTER TABLE will do,
we'd want to determine the answer the same way ALTER TABLE will.
But I can foresee people trying to use the function for other
purposes and then complaining because they want some other answer.

On the whole, I disapprove of this whole approach to figuring out
what ALTER TABLE will do: there are too many moving parts in that
question, and this can answer only one part. It leaves far too
much to the user to know about what other things will affect their
results.

What we have speculated about in the past is extending EXPLAIN
so that it can be applied to ALTER TABLE and other complicated
utility commands, and then for ALTER TABLE one bit of info it would
give you is whether a table rewrite (or even a table scan) is
required. Obviously, that's a major project, and so nobody's
tackled it yet AFAIK.

regards, tom lane