pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions used by index don't need to be immutable?
3+ messages / 2 participants
[nested] [flat]
* Functions used by index don't need to be immutable?
@ 2025-11-11 12:35 hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2025-11-11 12:35 UTC (permalink / raw)
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Functions used by index don't need to be immutable?
@ 2025-11-11 12:43 Heikki Linnakangas <hlinnaka@iki.fi>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2025-11-11 12:43 UTC (permalink / raw)
To: depesz@depesz.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Functions used by index don't need to be immutable?
@ 2025-11-11 12:43 hubert depesz lubaczewski <depesz@depesz.com>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 0 replies; 3+ messages in thread
From: hubert depesz lubaczewski @ 2025-11-11 12:43 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-11 12:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-11 12:35 Functions used by index don't need to be immutable? hubert depesz lubaczewski <depesz@depesz.com>
2025-11-11 12:43 ` Heikki Linnakangas <hlinnaka@iki.fi>
2025-11-11 12:43 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox