Re: Problematic enforcement of "ERROR: functions in index predicate must be marked IMMUTABLE"

On Mon, Jul 10, 2023 at 6:28 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> writes:
> > Even given this, premising users keeping the volatility marks in line
> > with the actual behavior of their corresponding functions, it might be
> > benetifical to prohibit changes to the volatility category while it's
> > being used for indices.
>
> In the end, adding such restrictions would just give a false sense
> of security, because there would always be gaps in whatever we did.
> As you quote from the documentation, volatility markings are a promise
> by the user to the system, not vice versa. If you break your promise,
> you get to keep both pieces of whatever trouble ensues.
>
>
I'd accept this more readily if we didn't have user unfriendly behavior for
CREATE OR REPLACE FUNCTION.

postgres=# \df+ immut

List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges | Language |
Internal name | Description
--------+-------+------------------+---------------------+------+------------+----------+---------+----------+-------------------+----------+---------------+-------------
public | immut | text | | func | immutable
| unsafe | vagrant | invoker | | plpgsql |
|
(1 row)

postgres=# create or replace function immut() returns text as $$begin
select 'one'; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \df+ immut

List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges | Language |
Internal name | Description
--------+-------+------------------+---------------------+------+------------+----------+---------+----------+-------------------+----------+---------------+-------------
public | immut | text | | func | volatile
| unsafe | vagrant | invoker | | plpgsql |
|
(1 row)

I find it quite reasonable to tell the user (warning) that their default
choice of volatile violates the immutable clause of the existing function,
and even would go so far as to require them to drop/recreate the function
if indeed their goal is to change it from immutable to volatile (error).
To the extreme I'd just add "changing the volatility marker" to be
prohibited just like we prohibit changing the return type.

David J.