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

Wed, 09 Jul 2025 02:12:43 -0700 

Hi hackers,

Thanks for the valuable feedback on my previous patch.

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

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

The full updated patch is attached below.

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

Best regards,  
xiaojiluo (Tencent Yunding Lab)




                    jian he<[email protected]&gt;&nbsp;在 2025年7月9日 周三 
10:34 写道：

On Mon, Jun 30, 2025 at 5:34 PM sundayjiang(蒋浩天)
<[email protected]&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: v2-0001-Prevent-replacement-of-a-function-if-it-s-used-in-an.patch
Description: Binary data

Reply via email to