Re: Functions used by index don't need to be immutable?

On 11/11/2025 14:35, hubert depesz lubaczewski wrote:
> Hi,
> while researching some blogpost I found that we can make index on
> volatile functions.
>
> Tested this code:
>
> CREATE TABLE wiki_docs (
> id INT8 generated always as identity PRIMARY KEY,
> title TEXT,
> body TEXT
> );
> create function get_tsvector( IN the_row wiki_docs ) returns
> tsvector as $$
> select
> setweight( to_tsvector('english', the_row.title), 'A')
> ||
> setweight( to_tsvector('english', the_row.body),
> 'B');
> $$ language sql;
> create index the_magic_gist on wiki_docs using gist( get_tsvector(wiki_docs) );
> create index the_magic_gin on wiki_docs using gin( get_tsvector(wiki_docs) );
>
> Ran it in in every Pg from 10 to 19, and it worked.
>
> postgres=# select provolatile from pg_proc where proname = 'get_tsvector';
> provolatile
> -------------
> v
> (1 row)
>
> Which seems to contradict part of
> https://www.postgresql.org/docs/18/sql-createindex.html
> which says:
>
>> All functions and operators used in an index definition must be
>> “immutable”,
>
> What am I misunderstanding and/or doing wrong?

In case of SQL functions, the immutability check can inline or "see
through" the definition and determine that the expression used in the
function doesn't contain any immutable expressions. If you modify
get_tsvector, adding a random() to it for example, then you will get the
error.

- Heikki