Re: [PATCH] Prevent replacement of a function if it's used in an index expression and is not IMMUTABLE - Mailing list pgsql-hackers

On Mon, Jun 30, 2025 at 5:34 PM sundayjiang(蒋浩天)
<sundayjiang@tencent.com> wrote:
>
> The purpose of this patch is to prevent replacing a function via `CREATE OR REPLACE FUNCTION` with a new definition
thatis not marked as `IMMUTABLE`, if the existing function is referenced by an index expression. 
>
> Replacing such functions may lead to index corruption or runtime semantic inconsistencies, especially when the
function’soutput is not stable for the same input. 
>
> If a function is used in an index, it can only be replaced if it is declared as `IMMUTABLE`.
>

looking at the right above code ``if (oldproc->prokind != prokind)``

+ if(oldproc->prokind == PROKIND_FUNCTION && volatility !=
PROVOLATILE_IMMUTABLE){

we can change it to

+        if(prokind == PROKIND_FUNCTION && oldproc->provolatile ==
PROVOLATILE_IMMUTABLE &&
+           volatility != PROVOLATILE_IMMUTABLE)
 +     {
curly brace generally begins with a new line.

if (index_found)
+ ereport(ERROR,
+ (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
+ errmsg("cannot replace function \"%s\" with a non-IMMUTABLE function
because it is used by an index",
+ procedureName)));
Here, errcode ERRCODE_FEATURE_NOT_SUPPORTED would be more appropriate.

you can add a simple test in src/test/regress/sql/create_function_sql.sql
for example:
CREATE OR REPLACE FUNCTION get_a_int(int default 2) RETURNS int
IMMUTABLE AS 'select $1' LANGUAGE sql;
create table t1(a int);
create index on t1((get_a_int(a)));
CREATE OR REPLACE FUNCTION get_a_int(int default 2) RETURNS int AS
'select $1' LANGUAGE sql;