回复:[Internet]Re: [PATCH] Prevent replacement of a function if it's used in an index expression and is not IMMUTABLE

From: sundayjiang(蒋浩天) <sundayjiang(at)tencent(dot)com>
To: jian he <jian(dot)universality(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: 回复:[Internet]Re: [PATCH] Prevent replacement of a function if it's used in an index expression and is not IMMUTABLE
Date: 2025-07-09 06:38:51
Message-ID: tencent_769D00FA084A26140321FC4D@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi&nbsp;hackers,

Thanks&nbsp;for&nbsp;the&nbsp;valuable&nbsp;feedback&nbsp;on&nbsp;my&nbsp;previous&nbsp;patch.

Based&nbsp;on&nbsp;the&nbsp;review&nbsp;comments,&nbsp;I&nbsp;have&nbsp;updated&nbsp;the&nbsp;patch&nbsp;as&nbsp;follows:

-&nbsp;Limited&nbsp;the&nbsp;error&nbsp;trigger&nbsp;only&nbsp;to&nbsp;the&nbsp;case&nbsp;where&nbsp;the&nbsp;original&nbsp;function&nbsp;is&nbsp;IMMUTABLE&nbsp;but&nbsp;the&nbsp;new&nbsp;definition&nbsp;is&nbsp;not.
-&nbsp;Changed&nbsp;the&nbsp;error&nbsp;code&nbsp;to&nbsp;ERRCODE_FEATURE_NOT_SUPPORTED&nbsp;for&nbsp;better&nbsp;semantics.
-&nbsp;Improved&nbsp;code&nbsp;indentation&nbsp;and&nbsp;style&nbsp;to&nbsp;align&nbsp;with&nbsp;project&nbsp;conventions.
-&nbsp;Added&nbsp;a&nbsp;regression&nbsp;test&nbsp;case&nbsp;in&nbsp;create_function_sql.sql&nbsp;to&nbsp;verify&nbsp;the&nbsp;new&nbsp;behavior.

The&nbsp;full&nbsp;updated&nbsp;patch&nbsp;is&nbsp;attached&nbsp;below.

Please&nbsp;take&nbsp;a&nbsp;look&nbsp;at&nbsp;this&nbsp;revised&nbsp;version.&nbsp;I&nbsp;look&nbsp;forward&nbsp;to&nbsp;your&nbsp;feedback&nbsp;and&nbsp;suggestions.

Best&nbsp;regards,&nbsp;&nbsp;
xiaojiluo (Tencent Yunding Lab)

jian he<jian(dot)universality(at)gmail(dot)com&gt;&nbsp;在 2025年7月9日 周三 10:34 写道:

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

looking at the right above code ``if (oldproc-&gt;prokind != prokind)``

+ if(oldproc-&gt;prokind == PROKIND_FUNCTION &amp;&amp; volatility !=
PROVOLATILE_IMMUTABLE){

we can change it to

+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if(prokind == PROKIND_FUNCTION &amp;&amp; oldproc-&gt;provolatile ==
PROVOLATILE_IMMUTABLE &amp;&amp;
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; volatility != PROVOLATILE_IMMUTABLE)
&nbsp;+&nbsp;&nbsp;&nbsp;&nbsp; {
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;

Attachment Content-Type Size
v2-0001-Prevent-replacement-of-a-function-if-it-s-used-in-an.patch application/octet-stream 6.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Laurenz Albe 2025-07-09 06:39:27 Re: Should TRUNCATE fire DDL triggers
Previous Message Richard Guo 2025-07-09 06:32:47 Re: Reduce "Var IS [NOT] NULL" quals during constant folding