Обсуждение: Functions used by index don't need to be immutable?

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?

Best regards,

depesz

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

On Tue, Nov 11, 2025 at 02:43:18PM +0200, Heikki Linnakangas wrote:
> 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.

Ah, makes sense. Thanks a lot.

Best regards,

depesz