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 | s
to_tsvector | to_tsvector_byid | 3734 25 | i
to_tsvector | to_tsvector | 25 | s
to_tsvector | json_to_tsvector | 114 | s
to_tsvector | jsonb_to_tsvector_byid | 3734 3802 | s
to_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!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.)
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers