Re: Proposal: pg_is_volatile function

Hi

čt 20. 2. 2025 v 13:48 odesílatel Andrew Farries <andrew(dot)farries(at)xata(dot)io>
napsal:

> 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`.
>
>
If this feature can be implemented, then it needs to be implemented like a
pseudo function, it cannot not be a classic function.

But for your use case you should probably check if the function is stable
too? So maybe you should check if the expression is immutable.

Maybe this function can be designed like pg_get_expr_volatility(expr) and
returns v, s or i

Probably this functionality should not be used for any other cases, so I
can imagine different and maybe more generic solutions. We can introduce
the function
`pg_does_relation_rewrite(sqlstr)`. Then the external tool can have less
knowledge about pg internals, and doesn't need to parse the command to
separate the expression.

Regards

Pavel