Proposal: pg_is_volatile function

From: Andrew Farries <andrew(dot)farries(at)xata(dot)io>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Proposal: pg_is_volatile function
Date: 2025-02-20 12:47:46
Message-ID: CAELCXsCuB_djUW-no5m5uEHPvQHjSEYdFfUfg4wb_m5gU77i4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hayato Kuroda (Fujitsu) 2025-02-20 12:51:17 RE: ReplicationSlotRelease() crashes when the instance is in the single user mode
Previous Message Hayato Kuroda (Fujitsu) 2025-02-20 12:29:43 RE: ReplicationSlotRelease() crashes when the instance is in the single user mode