Thread: [HACKERS] jsonb_to_tsvector should be immutable
Wanted to pull this out of my general report, because nobody seems to
have seen it:
P3: apparently jsonb_to_tsvector with lang parameter isn't immutable?
This means that it can't be used for indexing:
libdata=# create index bookdata_fts on bookdata using gin ((
to_tsvector('english',bookdata)));
ERROR: functions in index expression must be marked IMMUTABLE
... and indeed it's not:
select proname, prosrc, proargtypes, provolatile from pg_proc where
proname = 'to_tsvector'; proname | prosrc | proargtypes | provolatile
-------------+------------------------+-------------+-------------to_tsvector | jsonb_to_tsvector | 3802 |
sto_tsvector| to_tsvector_byid | 3734 25 | ito_tsvector | to_tsvector | 25 | sto_tsvector
|json_to_tsvector | 114 | sto_tsvector | jsonb_to_tsvector_byid | 3734 3802 | sto_tsvector |
json_to_tsvector_byid | 3734 114 | s
Both of the _byid functions should be marked immutable, no? Otherwise
how can users use the new functions for indexing?
--
Josh Berkus
Containers & Databases Oh My!
Josh Berkus <josh@berkus.org> writes:
> select proname, prosrc, proargtypes, provolatile from pg_proc where
> proname = 'to_tsvector';
Slightly more readable version:
regression=# select oid::regprocedure, provolatile, proparallel from pg_proc where proname = 'to_tsvector';
oid | provolatile | proparallel
------------------------------+-------------+-------------to_tsvector(jsonb) | s |
sto_tsvector(regconfig,text) | i | sto_tsvector(text) | s | sto_tsvector(json)
| s | sto_tsvector(regconfig,jsonb) | s | sto_tsvector(regconfig,json) | s | s
(6 rows)
> Both of the _byid functions should be marked immutable, no? Otherwise
> how can users use the new functions for indexing?
Yeah, if the (regconfig,text) one is considered immutable, I don't see
why the other two aren't. The justification for the other three being
only stable is that they depend on default_text_search_config.
(You could argue that none of these should be immutable because text
search configurations are changeable, but we already decided to ignore
that for the (regconfig,text) case.)
regards, tom lane
On 06/08/2017 02:26 PM, Tom Lane wrote: > Josh Berkus <josh@berkus.org> writes: >> select proname, prosrc, proargtypes, provolatile from pg_proc where >> proname = 'to_tsvector'; > Slightly more readable version: > > regression=# select oid::regprocedure, provolatile, proparallel from pg_proc where proname = 'to_tsvector'; > oid | provolatile | proparallel > ------------------------------+-------------+------------- > to_tsvector(jsonb) | s | s > to_tsvector(regconfig,text) | i | s > to_tsvector(text) | s | s > to_tsvector(json) | s | s > to_tsvector(regconfig,jsonb) | s | s > to_tsvector(regconfig,json) | s | s > (6 rows) > >> Both of the _byid functions should be marked immutable, no? Otherwise >> how can users use the new functions for indexing? > Yeah, if the (regconfig,text) one is considered immutable, I don't see > why the other two aren't. The justification for the other three being > only stable is that they depend on default_text_search_config. > > (You could argue that none of these should be immutable because text > search configurations are changeable, but we already decided to ignore > that for the (regconfig,text) case.) > > Yes, agreed it should be done consistently with text. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 06/08/2017 02:26 PM, Tom Lane wrote:
>> Yeah, if the (regconfig,text) one is considered immutable, I don't see
>> why the other two aren't. The justification for the other three being
>> only stable is that they depend on default_text_search_config.
> Yes, agreed it should be done consistently with text.
You going to fix it, or shall I?
regards, tom lane
On 06/08/2017 03:06 PM, Tom Lane wrote: > Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes: >> On 06/08/2017 02:26 PM, Tom Lane wrote: >>> Yeah, if the (regconfig,text) one is considered immutable, I don't see >>> why the other two aren't. The justification for the other three being >>> only stable is that they depend on default_text_search_config. >> Yes, agreed it should be done consistently with text. > You going to fix it, or shall I? > > I'll do it. -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services