postgresql 在时间戳列上声明索引的行为发生更改

我遇到了一个在不同版本的Postgres之间创建索引的问题。我们正在将Postgres数据库从10.21升级到14.4。在准备这一点，我一直在运行我们的测试，在较新的版本，并在做，所以我打这个问题.以下编辑的迁移脚本在Postgres 12或更低版本上运行良好。

CREATE TABLE fs_hourly (
    id VARCHAR (255) NOT NULL,
    hour TIMESTAMP WITH TIME ZONE NOT NULL,
    -- more stuff here
    PRIMARY KEY (id, hour)
);
CREATE INDEX hourly_by_id ON fs_hourly (
    id text_ops,
    hour timestamp_ops
);

但是在Postgres 13+上，我得到以下错误：
QueryFailedError：运算符类“timestamp_ops”不接受带时区的数据类型timestamp
我尝试了以下方法来确定实际使用的是哪个操作符，但这并没有给予足够的细节。以下是PG 12。

uc=# \d fs_hourly
                     Table "public.fs_hourly"
         Column         |            Type             | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------
 id                     | character varying(255)      |           | not null |
 hour                   | timestamp with time zone    |           | not null |
Indexes:
    "fs_hourly_pkey" PRIMARY KEY, btree (id, hour)
    "fs_hourly_by_id" btree (id, hour)

我试着确定timestamp_ops是否支持PG 12和更早版本的timestamp with time zone。这里没有证据。

uc=# select am.amname AS index_method
                , opc.opcname AS opclass_name
                , opc.opcintype::regtype AS indexed_type
                , opc.opcdefault AS is_default
             from pg_am am
                , pg_opclass opc
            where opc.opcmethod = am.oid
              and am.amname = 'btree'
         order by index_method, opclass_name;
 index_method |    opclass_name     |        indexed_type         | is_default
--------------+---------------------+-----------------------------+------------
 btree        | time_ops            | time without time zone      | t
 btree        | timestamp_ops       | timestamp without time zone | t
 btree        | timestamptz_ops     | timestamp with time zone    | t
 btree        | timetz_ops          | time with time zone         | t

在这一点上，我假设当索引创建时，Postgres说“愚蠢的人类。我会为你解决这个问题。”并使用timestamptz_ops而不是timestamp_ops，PG 13+在解释上更严格。但我想确认一下。我们将进行就地升级，我想提前知道是否会出现问题。如果我不能证明哪个操作符正在使用，我可能会在升级之前删除索引并替换它。
有没有一种方法可以让我更详细地了解索引中使用的是什么操作符类？

select version();
                                       version                                       
-------------------------------------------------------------------------------------
 PostgreSQL 12.15 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit

CREATE TABLE fs_hourly (
    id VARCHAR (255) NOT NULL,
    hour TIMESTAMP WITH TIME ZONE NOT NULL,
    -- more stuff here
    PRIMARY KEY (id, hour)
);
CREATE TABLE

CREATE INDEX hourly_by_id ON fs_hourly (
    id text_ops,
    hour timestamp_ops
);
CREATE INDEX

\d fs_hourly
                      Table "public.fs_hourly"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 id     | character varying(255)   |           | not null | 
 hour   | timestamp with time zone |           | not null | 
Indexes:
    "fs_hourly_pkey" PRIMARY KEY, btree (id, hour)
    "hourly_by_id" btree (id, hour)

SELECT
    c.relname,
    o.opcname
FROM
    pg_class AS c
    JOIN pg_index AS i ON c.oid = i.indexrelid
    JOIN pg_opclass AS o ON o.oid = ANY (i.indclass)
WHERE
    relname = 'hourly_by_id';

relname    |     opcname     
--------------+-----------------
 hourly_by_id | text_ops
 hourly_by_id | timestamptz_ops

postgresql 在时间戳列上声明索引的行为发生更改

1条答案

相关问题

热门标签

最新问答