ОШИБКА: функции в выражении индекса должны быть отмечены IMMUTABLE в Postgres

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

--detail message 
wapgrowth=> create index CONCURRENTLY idx_test on  tmp_table using btree (skyid, to_char(create_time, 'YYYY-MM-DD'), actiontype );
ERROR:  functions in index expression must be marked IMMUTABLE


--table ddl
wapgrowth=> \d tmp_table
               Table "wapgrowth.tmp_table"
   Column    |            Type             |   Modifiers   
-------------+-----------------------------+---------------
 id          | integer                     | not null
 actiontype  | character varying(20)       | 
 apptype     | character varying(20)       | 
 score       | integer                     | 
 create_time | timestamp without time zone | default now()
 skyid       | integer                     | 
Indexes:

Ответы

Ответ 1

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

http://www.mail-archive.com/[email protected]/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()

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

Ответ 2

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

create index CONCURRENTLY idx_test on  tmp_table using btree (skyid, cast(create_time as date), actiontype );

Ответ 3

to_char временной метки без времени не является неизменной функцией, потому что преобразование зависит от локальной настройки часового пояса. Это означает, что индекс не будет переноситься на другой компьютер в другом часовом поясе, и Postgres не позволит этого. Я думаю, проблема исчезнет, если вы объявите create_time как время с часовым поясом.

Ответ 4

В конце дня похоже, что вы пытаетесь индексировать представление "YYYY-MM-DD" для create_time. Почему не просто INDEX create_time? Проблема заключается в том, что to_char() является MUTABLE, потому что переменная среды локали может меняться, что изменяет вывод to_char().

http://www.postgresql.org/docs/9.0/static/charset.html

Если у вас есть контроль над схемой, вы можете добавить новый столбец (например, create_date TEXT) и INDEX, а затем настроить триггер, который обрабатывает вставки. Фактически, если вы создали способ конвертировать ваш TIMESTAMP WITHOUT TIME ZONE в TEXT, вы можете индексировать его постоянным образом. Предложение a_horse_with_no_name было хорошим, так как я не думаю, что вы заботитесь о локали.

Проблема, с которой вы работаете, - это все коды обработки DATE и TIME, которые соответствуют языку, который не является IMMUTABLE, поэтому вы не можете легко использовать функции INDEX, которые полагаются на эти типы данных.