Thread

1. Functions used by index don't need to be immutable?

   hubert depesz lubaczewski <depesz@depesz.com> — 2025-11-11T12:35:22Z

   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
   
   
   
   
   

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

   Heikki Linnakangas <hlinnaka@iki.fi> — 2025-11-11T12:43:18Z

   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
   
   
   
   
   

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

   hubert depesz lubaczewski <depesz@depesz.com> — 2025-11-11T12:43:53Z

   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