Error functions in index expression must be marked immutable

ОШИБКА: функции в индексном выражении должны быть помечены как НЕЗАВИСИМЫЕ в Postgres

Я хочу создать индекс выражения с несколькими столбцами, но когда я создаю индекс, выводится следующее сообщение:

задан 12 мая ’11, 00:05

Если вы столкнулись с этим с полями char, просто поместите имя поля char в кавычки, например «field» — Luv33preet

5 ответы

Согласно этой теме в списке рассылки хакеров:

это предполагаемое поведение как to_char зависит от настройки LC_MESSAGES

В вашем случае это, по-видимому, не имеет смысла, поскольку используемый вами формат никогда не будет зависеть от локали, поэтому, если вам действительно нужно использовать текстовое представление в индексе, вы можете создать свою собственную функцию to_char () и пометить ее как неизменный:

Если вам нужно использовать его как текст в индексе (и вы не можете использовать приведение к дате, как предложил Сэм), вам нужно будет создать свою собственную функцию форматирования, которую вы можете пометить как неизменяемую. Затем это можно использовать в индексе.

Но чтобы сделать Postgres использование индекс, который вам нужно будет вызвать my_to_char() в ваших операторах SQL. Он не узнает его, когда вы воспользуетесь встроенным to_char()

Но я думаю, что предложение Сэма использовать прямую дату в указателе, вероятно, лучше.

ответ дан 12 мая ’11, 07:05

Спасибо за совет. После изменения функции «to_char» на «IMMUTABLE» она работает, но есть ли риск после этого? — франки

skytf = # изменить функцию to_char (метка времени с часовым поясом, текст) IMMUTABLE; ИЗМЕНИТЬ ФУНКЦИЮ — франки

Это объясняет более подробно:

в основном часовой пояс зависит от сервера, и поэтому результат может измениться, если кто-то его изменит. Но если вы заблокируете часовой пояс:

Источник

cobusc / postgresql_date_function_index_howto.md

. you realize that you have to perform complex queries with the following type of condition:

The table is big and other indexes are not helping, so you decide to create an index on DATE(created_at) :

It turns out that the function DATE(TIMESTAMP WITH TIME ZONE) is mutable, due to the time zone. On the other hand DATE(TIMESTAMP WITHOUT TIME ZONE) is immutable, as shown in the following example:

So how do we handle the case where we have a time zone? Well, since the DATE() function may give different results based on the time zone, we have to decide which time zone is applicable. In this example we use UTC:

That worked. Now how do get the query planner to use it? I was under the impression that I simply had to use the same function I used to create the index in the WHERE condition. Let’s see what the query planner says:

The index is not used. Let’s go look at the index definition:

Let’s try to use the definition as stored by the database. Note that this is (and should be) functionally equivalent to DATE(created_at AT TIME ZONE ‘UTC’) , since the documentation says:

This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the WHERE clause (in order to match the index function).

Footnote: PostgreSQL 9.1.9 was used.

wow excellent post

I use pg 9.5. I still got functions in index expression must be marked IMMUTABLE error, when I create function index with sql below.

It seem created_at AT TIME ZONE ‘UTC+8’ return value is immutable, but data(..) function is not

Any help will be greatly appreciated!

Really useful. I used this for an ORDER BY with a LIMIT . Thought the index wasn’t working, but then read the manual and saw the LIMIT I was using was too high (1000), so the planner decided to do a Seq Scan . A lower LIMIT (100) made it use the index.

A little more elaboration might suffice here.

Although this example uses UTC as the timezone value, if you have something specified differently as timezone in postgresql.conf, then you must use that value instead of UTC when creating and using the index in a query. Assuming timezone = ‘localtime’, then this needs to happen:

CREATE INDEX ON foo (DATE(created_at AT TIME ZONE ‘localtime’));
EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE(‘localtime’::text, created_at)) = DATE(‘2013-01-01’);

If you don’t do that, you may be deceived by erroneous query results, even though the explain would still show that it’s using UTC.

I stumbled on this while implementing this solution in our own environment. Before the index, I got 188 rows back. Afterwards, I got none back. But when I changed index and query to use «localtime», then I got the expected 188 rows back using the index.

A little more elaboration might suffice here.

Although this example uses UTC as the timezone value, if you have something specified differently as timezone in postgresql.conf, then you must use that value instead of UTC when creating and using the index in a query. Assuming timezone = ‘localtime’, then this needs to happen:

CREATE INDEX ON foo (DATE(created_at AT TIME ZONE ‘localtime’));
EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE(‘localtime’::text, created_at)) = DATE(‘2013-01-01’);
If you don’t do that, you may be deceived by erroneous query results, even though the explain would still show that it’s using UTC.

I stumbled on this while implementing this solution in our own environment. Before the index, I got 188 rows back. Afterwards, I got none back. But when I changed index and query to use «localtime», then I got the expected 188 rows back using the index.

Источник

Thread: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

[GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Hello everybody,

I’d like to implement a btree date index from json input data.

>>># d docs
В В В В В В В В В В В В В В В В В В В В В В В В Table «public.docs»
В Column |В TypeВ В |В В В В В В В В В В В В В В В В В В В В ModifiersВ В В В В В В В В В В В В В В В В В В В
———+———+—————————————————
В idВ В В В | integer | not null default nextval(‘docs_id_seq’::regclass)
В metaВ В | jsonbВ В |
Indexes:
В В В «docs_pkey» PRIMARY KEY, btree (id)

So, I did:

>>># create index docs_birthdate_idx ON docs using btree (((meta->>’birthdate’)::date));
ERROR:В functions in index expression must be marked IMMUTABLE

Searching the Internet for a solution, I tried several variants of this:

>>># create index docs_birthdate_idx ON docs using btree ((to_timestamp(meta->>’birthdate’, ‘YYYY-MM-DD’) at time zone ‘UTC’));
ERROR:В functions in index expression must be marked IMMUTABLE

Years ago, I circumvented it by creating an immutable function. This, though, just hides the errors since I would use the mutable expression anyway and mark it as immutable.

So, what is the problem here?

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> On 26 February 2017 at 10:09, Sven R. Kunze > > wrote:
>
>В В В >>># create index docs_birthdate_idx ON docs using btree
>В В В (((meta->>’birthdate’)::date)) ;
>В В В ERROR:В functions in index expression must be marked IMMUTABLE
>
>В В В So, what is the problem here?
>
>
> ​Date functions are inherently not immutable because of timezones. Your
> solution of using to_timestamp doesn’t help because it automatically
> returns a value in WITH TIMESTAMP. Do you get anywhere by using
> «::timestamp without time zone» instead, as suggested here?

My attempts at working the OP’s problem passed through that:

test=> create index docs_birthdate_idx ON docs using btree (((meta->>’birthdate’):: timestamp));
ERROR:В functions in index expression must be marked IMMUTABLE

test=> create index docs_birthdate_idx ON docs using btree ((meta->>’birthdate’));
CREATE INDEX

It is the act of casting that fails. Other then the OP’s own suggestion of creating
a function that wraps the operation and marks it immutable I don’t have a solution at
this time

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Thanks, Geoff. It’s not the most convenient way to define an index to define a function first and that all future queries need to use that very function in order to utilize the index. Though, it’s the most safest and best documenting way.

So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are timezone-influenced.

I still feel that a function is overkill for a simple text to date conversion. Couldn’t there be an IMMUTABLE modifier for an expression to mark it as immutable?

«SELECT ‘2007-02-02’::date;» just works. It would be great if one could define an index with the same ease. I already can see how our application developers need constant reminders that «in case of dates, use ‘magic_function’ first». If they don’t, the application will suffer from bad performance.

Thanks in advance for your replies.

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

Yes, but is not about timezone dependency, it is about the other dependencies listed in the second and third points. Namely the datestyle setting and magic strings e.g. ‘now’

=# — required datestyle
=# select to_date(‘2000-01-01’);
ERROR:В function to_date(unknown) does not exist
LINE 1: select to_date(‘2000-01-01’);
В В В В В В В В В В В В В В ^
HINT:В No function matches the given name and argument types. You might need to add explicit type casts.

=# — magic strings don’t work
=# select to_date(»);
ERROR:В invalid value «epoc» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘epoch’, ‘YYYY-MM-DD’);
ERROR:В invalid value «epoc» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘infinity’, ‘YYYY-MM-DD’);
ERROR:В invalid value «infi» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘-infinity’, ‘YYYY-MM-DD’);
ERROR:В invalid value «-inf» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘now’, ‘YYYY-MM-DD’);
ERROR:В invalid value «now» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘today’, ‘YYYY-MM-DD’);
ERROR:В invalid value «toda» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘tomorrow’, ‘YYYY-MM-DD’);
ERROR:В invalid value «tomo» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘yesterday’, ‘YYYY-MM-DD’);
ERROR:В invalid value «yest» for «YYYY»
DETAIL:В Value must be an integer.
=# select to_date(‘allballs’, ‘YYYY-MM-DD’);
ERROR:В invalid value «allb» for «YYYY»
DETAIL:В Value must be an integer.

Regards,
Sven

Источник

Sql error 42p17 error functions in index expression must be marked immutable

In this post, I will share issue faced while working with function based index in PostgreSQL . I was performing database migration from Oracle to PostgreSQL and used AWS Schema Conversion Tool(SCT) for converting the database schema.AWS Schema conversion tool takes care of automatically converting schema from one database engine to other. You can get more information about it on AWS documentation or AWS Database Blogs articles

AWS SCT converted a function based index but while executing ddl in PostgreSQL, it failed to execute with error

“ERROR: functions in index expression must be marked IMMUTABLE”

As per PostgreSQL docs, function could be of 3 types

• Volatile – It can return different results on successive calls with the same arguments
• Stable – It is guaranteed to return the same results given the same arguments for all rows within a single statement
• Immutable – It is guaranteed to return the same results given the same arguments forever

Let’s understand this with example. In below example, we are using current_timestamp function and output would change based on client timezone setting

Current_timestamp is tagged as STABLE, since their values do not change within a transaction but it will change in next transaction even though input is same.

But Immutable function won’t change result. e.g Below two sql will give same result

In PostgreSQL, function/expression used to create function based index needs to be immutable i.e function is guaranteed to return same results on giving same arguments to avoid data corruption.

We can identify whether function is immutable/stable by quering pg_proc view . Value of provalite field will indicate the type

Definition from docs

provolatile tells whether the function’s result depends only on its input arguments, or is affected by outside factors. It is i for “immutable” functions, which always deliver the same result for the same inputs. It is s for “stable” functions, whose results (for fixed inputs) do not change within a scan. It is v for “volatile” functions, whose results might change at any time. (Use v also for functions with side-effects, so that calls to them cannot get optimized away.)

We can see that concat and concat_ws are listed as stable function and their value can depend on client setting. E.g setting parameters like extra_float_digits will impact the output of concat function

We were able to workaround by creating new function with immutable type which accepts input as text

Источник

Согласно этой теме в списке рассылки хакеров:

http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg86725.html

это предполагаемое поведение как to_char зависит от настройки LC_MESSAGES

В вашем случае это, по-видимому, не имеет смысла, поскольку используемый вами формат никогда не будет зависеть от локали, поэтому, если вам действительно нужно использовать текстовое представление в индексе, вы можете создать свою собственную функцию to_char () и пометить ее как неизменный:

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp) 
  RETURNS text
AS
$BODY$
    select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;

Если вам нужно использовать его как текст в индексе (и вы не можете использовать приведение к дате, как предложил Сэм), вам нужно будет создать свою собственную функцию форматирования, которую вы можете пометить как неизменяемую. Затем это можно использовать в индексе.

Но чтобы сделать Postgres использование индекс, который вам нужно будет вызвать my_to_char() в ваших операторах SQL. Он не узнает его, когда вы воспользуетесь встроенным to_char()

Но я думаю, что предложение Сэма использовать прямую дату в указателе, вероятно, лучше.

In this post, I will share  issue faced while working with function based index in PostgreSQL . I was performing database migration from Oracle to PostgreSQL and used AWS Schema Conversion Tool(SCT) for converting the database schema.AWS Schema conversion tool takes care of automatically converting schema from one database engine to other. You can get more information about it on AWS documentation or  AWS Database Blogs articles

AWS SCT converted a function based index but while executing ddl in PostgreSQL, it failed to execute  with error

 “ERROR: functions in index expression must be marked IMMUTABLE”

-- Oracle Index DDL
 
CREATE UNIQUE INDEX "APP"."STUDENT_IDX1" ON "APP"."STUDENT" (CASE "IS_VALID" WHEN 1 THEN TO_CHAR("STUDENT_ID")||'-'||TO_CHAR("COURSE_ID") ELSE NULL END ) TABLESPACE "USERS" ; 
 
-- PostgreSQL DDL (Recommended by SCT)
 
CREATE UNIQUE INDEX student_idx1 ON student
USING BTREE ((CASE is_valid
    WHEN 1 THEN CONCAT_WS('', student_id::TEXT, '-', course_id::TEXT)
    ELSE NULL
END) ASC);

As per PostgreSQL docs, function could be of 3 types

• Volatile – It can return different results on successive calls with the same arguments
• Stable – It is guaranteed to return the same results given the same arguments for all rows within a single statement
• Immutable – It is guaranteed to return the same results given the same arguments forever

Let’s understand this with example. In below example, we are using current_timestamp function and output would change based on client timezone setting

postgres=> set timezone to 'US/Pacific';
SET
postgres=> select current_timestamp;
              now
-------------------------------
 2018-01-17 20:22:32.023775-08
(1 row)
postgres=> set timezone to 'UTC';
SET
postgres=> select current_timestamp;
              now
-------------------------------
 2018-01-18 04:22:46.227855+00
(1 row)

Current_timestamp is tagged as STABLE, since their values do not change within a transaction but it will change in next transaction even though input is same.

But Immutable function won’t change result. e.g Below two sql will give same result

select * from emp where empid=4 

OR

select * from emp where empid = 2+2

In PostgreSQL, function/expression used to create function based index needs to be immutable i.e function is guaranteed to return same results on giving same arguments to avoid data corruption.

We can identify whether function is immutable/stable by quering pg_proc view . Value of provalite field will indicate the type

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname like 'concat%';
proname  | provolatile | proargtypes | argtype |     prosrc
-----------+-------------+-------------+---------+----------------
concat    | s           | 2276        | "any"   | text_concat
concat_ws | s           | 25 2276     | text    | text_concat_ws
(2 rows)

Definition from docs

provolatile tells whether the function’s result depends only on its input arguments, or is affected by outside factors. It is i for “immutable” functions, which always deliver the same result for the same inputs. It is s for “stable” functions, whose results (for fixed inputs) do not change within a scan. It is v for “volatile” functions, whose results might change at any time. (Use v also for functions with side-effects, so that calls to them cannot get optimized away.)

We can see that concat and concat_ws are listed as stable function and their value can depend on client setting. E.g setting parameters like extra_float_digits will impact the output of concat function

postgres=> select concat(1.0000000000003::float8,56);
      concat
-----------------
1.000000000000356
(1 row)
postgres=> set extra_float_digits TO 3;
SET
postgres=> select concat(1.0000000000003::float8,56);
     concat
-----------------------
1.0000000000002999856
(1 row)

We were able to workaround by creating new function with immutable type which accepts input as text

CREATE OR REPLACE FUNCTION immutable_concat ( text, text, text, text ) RETURNS text AS 'SELECT $1 || $2 || $3 || $4;' LANGUAGE SQL IMMUTABLE;

New index statement

CREATE UNIQUE INDEX student_idx1
ON student
USING BTREE ((CASE is_valid
    WHEN 1 THEN immutable_concat('', student_id::TEXT, '-', course_id::TEXT)
    ELSE NULL
END) ASC);

Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>>> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze(at)mail(dot)de
>>> <mailto:srkunze(at)mail(dot)de>>wrote:
>>>> # create index docs_birthdate_idx ON docs using btree
>>>> (((meta->>’birthdate’)::date));
>>>> ERROR: functions in index expression must be marked IMMUTABLE

>>> ​Date functions are inherently not immutable because of timezones.

> ​ Isn’t the point that casting to ::timestamp will still keep the
> timezone? Hence casting to «without timezone».

There are multiple reasons why the text-to-datetime conversion functions
are not immutable:

* some of them depend on the current timezone (but I don’t believe date_in
does);

* all of them depend on the current datestyle setting, eg to resolve
’02/03/2017′;

* all of them accept strings with time-varying values, such as ‘now’
or ‘today’.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it’s still not immutable.

I’m not entirely sure why the OP feels he needs an index on this
expression. If he’s willing to restrict the column to have the
exact format ‘YYYY-MM-DD’, then a regular textual index would sort
the same anyway. Perhaps what’s needed is just to add a CHECK
constraint verifying that the column has that format.

regards, tom lane