PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres function based index update on function change

Will an index be automatically be updated if I change a function that is used in it. e.g.

CREATE OR REPLACE FUNCTION add_test_prefix(input_text VARCHAR)
RETURNS VARCHAR AS
$$
BEGIN
    RETURN 'test_' || input_text;
END;
$$
LANGUAGE plpgsql;

then create an index with it:

CREATE INDEX idx_test_prefix ON users ((add_test_prefix(username)));

Now use the index:

SELECT * FROM users
WHERE add_test_prefix(username) = 'test_jon%';

Will I immediately be able to query for ‘newprefix_jon%’ if I change the function to

BEGIN
    RETURN 'newprefix_' || input_text;
END;

or do I have to recreate or somehow trigger an index update for all rows?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As answered in the comments: It needs to be reindexed manually by calling

    REINDEX INDEX index_name


  2. 0

    Creating that index will fail with the error message

    ERROR:  functions in index expression must be marked IMMUTABLE

    An index must rely on function results being immutable, that is, the function has to return the same result for the same arguments regardless of the contents of the database, the current time or the configuration of the database. You would have to declare the function IMMUTABLE to promise to PostgreSQL that your function will obey that:

    ALTER FUNCTION add_test_prefix(varchar) IMMUTABLE;

    If you redefine the function, you are breaking that promise, and the index will be corrupted. The only way to recover from the corruption is to rebuild the index:

    REINDEX INDEX idx_test_prefix;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search