时间戳

首页 > 解决方案 > Postgres 索引函数似乎根据调用时间创建不同的值

sql - Postgres 索引函数似乎根据调用时间创建不同的值

问题描述

我正在观察我无法在 Postgres v9.6 中解释的行为。

存在一个表，我们将其称为“订单”，其架构为

create table orders(
  id uuid primary key,
  created_at timestamp with time zone not null,
  modified_at timestamp with time zone not null,
  entity jsonb not null);

我需要基于使用id和modified_at值来创建索引值的函数创建索引。因此，我有

-- We put the 'A' on the front because if the beginning of the string looks like a date, it triggers
-- a postgres bug that causes sorting to skip some rows. The 'A' makes the value not look like a date.
create function sortable_timestamp_and_uuid(timestamp with time zone, uuid) returns text as
$$ select 'A' || to_char($1, 'YYYY-MM-DD HH24:MI:SS.US') || '/' || $2::text; $$
language sql immutable;

create function paging_func(timestamp with time zone, uuid) returns text as
$$ select sortable_timestamp_and_uuid($1, $2); $$
language sql immutable;

create index my_paging_idx on orders( paging_func(modified_at, id) );

这按预期工作。我可以在 orders 表上创建一个索引，当我使用它的WHERE子句运行查询时，paging_func(modified_at, id) < pagine_func(some_specfic_timestamp, some_specific_uuid)会返回我期望的结果。

但是，这只适用于我创建索引时表中已经存在的数据。如果我将数据插入表中INSERT id, created_at, modified_at, entity VALUES(?,now(),now(),?)，我之前的 where 子句不适用于新插入的数据。数据显示在索引的顶部（值最小）。

例如，如果我有两行值：

id                                    | modified_at
--------------------------------------------------------------------
199967e2-0987-2987-11c7-bbca1348467e  | 2020-01-14 20:14:25.799287
298bc22a-6eaa-5ec3-d962-ad2d206a4dca  | 2020-01-14 20:14:25.799287

如果我使用数据库中已经存在的行创建索引并使用

WHERE paging_func(modified_at, id) < paging_func(to_timestamp('2020-01-14 20:14:25.799287',
          'YYYY/MM/DD HH24:MI:SS.US'),
          uuid_in('298bc22a-6eaa-5ec3-d962-ad2d206a4dca'))

结果集将仅包含第一行。但是，如果在创建索引时仅存在第一行，并且我将第二行插入表中，并运行相同的精确查询，则会返回两行。

如果我删除索引并重新创建索引，则索引的行为与表中数据的预期相同，但插入到表中的所有新值都没有正确索引。我将不胜感激任何帮助解释我做错了什么以及为什么我观察到这种行为。

标签： sqlpostgresqlindexing

解决方案

原因是当你说函数是不可变的时你在撒谎：

SET timezone = 'UTC';

SELECT sortable_timestamp_and_uuid('2020-01-01 00:00:00+00',
                                   '9a1b6ef4-370f-11ea-9c8d-d0c637b5521b');

                   sortable_timestamp_and_uuid                    
------------------------------------------------------------------
 A2020-01-01 00:00:00.000000/9a1b6ef4-370f-11ea-9c8d-d0c637b5521b
(1 row)

SET timezone = 'Europe/Vienna';

SELECT sortable_timestamp_and_uuid('2020-01-01 00:00:00+00',
                                   '9a1b6ef4-370f-11ea-9c8d-d0c637b5521b');

                   sortable_timestamp_and_uuid                    
------------------------------------------------------------------
 A2020-01-01 01:00:00.000000/9a1b6ef4-370f-11ea-9c8d-d0c637b5521b
(1 row)

因此，timezone当您写入行和尝试写入行时SELECT，查询可能找不到该行。简而言之，数据损坏。

使用这样的函数：

CREATE FUNCTION sortable_timestamp_and_uuid(timestamp with time zone, uuid) RETURNS text AS
$$SELECT 'A' || ($1 AT TIME ZONE 'UTC')::text || '/' || $2::text$$
LANGUAGE sql IMMUTABLE;

推荐阅读