时间戳索引：索引表达式中的函数必须标记为IMMUTABLE [英] Index on Timestamp: Functions in index expression must be marked as IMMUTABLE

问题描述

我想在我的表上为 start_time 创建索引，这是我的json列中名为 match 的timestamptz（带时区的时间戳）字段。

I want to create an index on my table for start_time, a timestamptz (timestamp with time zone) field in my json column called match.

跟随这个问题和这篇文章我知道，由于时区和本地化的不同，您无法在timestamptz字段上创建索引。这两个都表明您可以在时间戳记上创建索引（转换为文本），所以我尝试了以下功能：

Following this question and this article I understand that you can't create an index on a timestamptz field because of different timezones and localisation. Both of these indicate that you can create an index on a timestamp (converted to text), so I tried the following function:

CREATE OR REPLACE FUNCTION to_text(timestamptz) 
 RETURNS text AS $$
  SELECT to_char($1 at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
  $$
LANGUAGE sql
IMMUTABLE;

我认为在时区和本地化方面没有问题。

Which I believe has no issues with timezones and localisation.

CREATE INDEX i_match_start_time ON matches (to_text(((match->>'start_time')::timestamptz)));

这将返回以下内容：

ERROR: functions in index expression must be marked IMMUTABLE

我也尝试返回时间戳的函数：

I have also tried functions that return a timestamp:

SELECT ($1 at time zone 'UTC') 

和返回unix时间的函数（尝试两次并强制转换为十进制）：

And functions that return unix time (tried double and casted into decimal):

SELECT EXTRACT(EPOCH FROM $1)

每个返回相同的错误。

我需要在 start_time 上建立索引，因为实际上对该表的所有选择查询都将由 start_time 。

I need to index on start_time because virtually all select queries to this table will be ordered by start_time.

感谢您的帮助。

推荐答案

首先，我认为这可能是 CREATE INDEX 逻辑中的错误。但关键是从文本到 timestamptz 本身的转换不是 IMMUTABLE 之一。它取决于易失性设置，例如 datestyle 。

First I thought this might be bug in the CREATE INDEX logic. But the point is that the cast from text to timestamptz itself isn't IMMUTABLE either. It depends on volatile settings like datestyle.

在您的特定情况下，有一种解决方法比您尝试过的要好。将演员表移入函数：

In your particular case there is a workaround that's even better than what you tried. Move the cast into the function:

CREATE OR REPLACE FUNCTION to_text(text) 
  RETURNS text AS
$func$
SELECT to_char($1::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
$func$ LANGUAGE sql IMMUTABLE;

效率一样高，但现在创建索引不会倒空：

Just as efficient, but now CREATE INDEX won't barf:

CREATE INDEX bar ON foo(to_text(j->>'start_time'));

显然，您必须相应地调整函数调用：强制转换： ：表达式中的：timestamptz 。确保在所有地方使用相同的设置 ，否则索引可能会导致错误的结果。

Obviously, you have to adjust your function calls accordingly: drop the cast ::timestamptz from the expression. Make sure that you use the same settings everywhere, or the index might lead to false results.

对 to_timestamp（） 而不是强制类型转换（如果您的输入模式允许的话）：

Use an actually immutable expression with to_timestamp() instead of the cast (if your input pattern allows it):

CREATE OR REPLACE FUNCTION to_text(text) 
  RETURNS text AS
$func$
SELECT to_char(to_timestamp($1, 'YYYY-MM-DD"T"HH24:MI:SS.US')  -- adapt to your pattern
            AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') 
$func$ LANGUAGE sql IMMUTABLE;

但是请注意（引用我测试中的错误消息）：

Note however (quoting an error message from my test):

TZ / tz / OF格式模式to_date

"TZ"/"tz"/"OF" format patterns are not supported in to_date

这篇关于时间戳索引：索引表达式中的函数必须标记为IMMUTABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！