Replace the function used in the index

Question

What happens to an index built on a function when I change the underlying function?

Let's say I have a clean_name() function defined as:

 CREATE OR REPLACE FUNCTION clean_name(n text) RETURNS TEXT AS $BODY$ DECLARE rec TEXT; BEGIN EXECUTE 'SELECT Regexp_replace(' || quote_literal(n) || ', ''[az]'', '''', ''g'');' INTO rec; RETURN rec; END; $BODY$ LANGUAGE plpgsql IMMUTABLE ; 

Then create an index:

 CREATE INDEX my_table_upper_name_btree ON schema.my_table USING GIST (my_text_field); 

But then I decided to redefine the function to remove the uppercase letters instead. What happens to the index I created? Does it change on its own? Will I repeat DROP and CREATE ? VACUUM [ANALYZE] [FULL] ?

(The corresponding function is similar, instead a rather long series of replacements is used, which are still fixed but expected to be stable.)

Answer 1

IMMUTABLE means "unchanged" or "unchanged". What you must do to strictly avoid breaking this rule is to drop the function and everything that depends on it, and then recreate it and the indices that use it.

If you replace the function in place, you take responsibility for the consequences. Personally, I think PostgreSQL should disable the OR REPLACE functions for IMMUTABLE for this reason, forcing you to jump over the configuration setting with an additional hoop, like setting ignore_immutable_checks_even_though_it_might_cause_incorrect_queries .

If you change an immutable function, then function-based indexes are not valid. The server cannot determine whether the behavior of the function has changed or not; you could just replace it with an optimized version that has the same behavior in every way. Thus, this will not invalidate the indexes for you, although this may be the case, because if the behavior of your function is different, you may get incorrect query results for function-based queries.

Answer 2

If you change the function, you will have to rebuild the index.

 create table t (i integer); insert into t (i) select generate_series(1, 100000); analyze t; 

A simple function to return the opposite integer:

 create or replace function f(i integer) returns integer as $$ select i * -1; $$ immutable language sql; 

And the index on it:

 create index t_i_index on t(f(i)); 

Used index:

 explain select * from t order by f(i); QUERY PLAN --------------------------------------------------------------------------- Index Scan using t_i_index on t (cost=0.00..3300.26 rows=100000 width=4) 

Now the function is changed to return the whole:

 create or replace function f(i integer) returns integer as $$ select i; $$ immutable language sql; 

And the index is no longer used:

 explain select * from t order by f(i); QUERY PLAN --------------------------------------------------------------- Sort (cost=11116.32..11366.32 rows=100000 width=4) Sort Key: i -> Seq Scan on t (cost=0.00..1443.00 rows=100000 width=4) 

If the index is rebuilt

 reindex index t_i_index; 

It is used again:

 explain select * from t order by f(i); QUERY PLAN --------------------------------------------------------------------------- Index Scan using t_i_index on t (cost=0.00..4376.26 rows=100000 width=4)