Thread: Proposal: pg_is_volatile function

I'd like to propose a new function `pg_is_volatile` that would test and return
the volatility of its argument expression. Example uses of the function would
be:

pg_is_volatile(1) -> false
pg_is_volatile(random()) -> true

The motivation for the proposal is to allow testing of column default
expressions for new columns added with `ALTER TABLE ... ADD COLUMN` before
adding the column. This is to determine whether the column default will be able
to take advantage of the fast-path optimization for non-volatile column
defaults, or whether a full table rewrite will be required.

For a schema migration tool, it's desirable for the tool to assess the
volatility of a column default for a new column before adding it. The tool can
then decide on the most appropriate way to add the column, either doing so
directly for a non-volatile default, or issuing a warning or using some other
method in the case of a volatile default.

The documentation for this function would be as follows:

```
  <row>
   <entry role="func_table_entry"><para role="func_signature">
    <indexterm>
     <primary>pg_is_volatile</primary>
    </indexterm>
    <function>pg_is_volatile</function> ( <type>"any"</type> )
    <returnvalue>boolean</returnvalue>
   </para>
   <para>
    Tests whether the argument expression contains volatile functions (see
    <xref linkend="xfunc-volatility"/>). This can be useful to determine
    whether the expression can be used as a column default without causing
    a table rewrite.
   </para></entry>
  </row>
```

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`.

Pavel Stehule <pavel.stehule@gmail.com> writes:
> čt 20. 2. 2025 v 13:48 odesílatel Andrew Farries <andrew.farries@xata.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