PostgreSQL修复“data type X has no default operator class for access method Y”报错

我最近需要在一个整数数组列上创建一个 GiST 索引以进行性能比较。

我希望这很容易：

gabrielle=# \d my_table
                             Table "public.my_table"
 Column |   Type    | Collation | Nullable |               Default
--------+-----------+-----------+----------+--------------------------------------
 id     | bigint    |           | not null | nextval('my_table_id_seq'::regclass)
 my_ids | integer[] |           |          |
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)
 
gabrielle=# CREATE INDEX CONCURRENTLY idx_try_gist ON my_table USING gist (my_ids);
ERROR:  data type integer[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
 
.

—

如果您在这里，您可能想知道“Sam Hill 中的‘操作员类’是什么”？

操作员以某种方式操作数据。您可能已经熟悉算术运算符，例如 ‘+’、’-’ 和>and <。如果你做过任何编程，你就会知道逻辑运算符 AND 和 OR。Postgres 中还有许多其他运算符，例如<@和@>数组比较运算符（又名“冰淇淋锥”）。

比较运算符被分组到包含在特定数据类型的列上构建索引所需的运算符的类中。就我而言，我需要找到合适的运算符类来在整数数组上构建要点索引。（更多的文档在这里：https ：//www.postgresql.org/docs/current/indexes-opclass.html ）

—

找到正确的运算符只需要一点时间；我是这样做的：

首先，转到 Postgres 文档并找到显示数据库中已经可用的运算符类的便捷查询：https ：//www.postgresql.org/docs/current/indexes-opclass.html

（我将示例限制为仅显示gist运算符，因为这是我的特殊问题）：

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 = 'gist'
    ORDER BY index_method, opclass_name;
 
 index_method |     opclass_name     |        indexed_type         | is_default
--------------+----------------------+-----------------------------+------------
 ...
 gist         | gist_int2_ops        | smallint                    | t
 gist         | gist_int4_ops        | integer                     | t
 gist         | gist_int8_ops        | bigint                      | t
 ...

gist_int4_ops看起来很有希望，但是：

gabrielle=# CREATE INDEX CONCURRENTLY idx_try_gist ON my_table USING gist (my_ids gist_int4_ops);
ERROR:  operator class "gist_int4_ops" does not accept data type integer[]

所以我没有安装正确的操作员。由于我在 Postgres 上，“有一个扩展”，所以让我们去寻找吧！

gabrielle=# SELECT * FROM pg_available_extensions WHERE comment ILIKE '%gist%';
    name    | default_version | installed_version |                    comment
------------+-----------------+-------------------+-----------------------------------------------
 btree_gist | 1.5             | 1.5               | support for indexing common datatypes in GiST

我已经知道btree_gist没有我想要的，所以让我们尝试另一种方法：

gabrielle=# SELECT * FROM pg_available_extensions WHERE comment ILIKE '%int%';
         name         | default_version | installed_version |                                                       comment
----------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 refint               | 1.0             | [null]            | functions for implementing referential integrity (obsolete)
 address_standardizer | 3.0.5           | [null]            | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 seg                  | 1.3             | [null]            | data type for representing line segments or floating-point intervals
 intagg               | 1.1             | [null]            | integer aggregator and enumerator (obsolete)
 isn                  | 1.2             | [null]            | data types for international product numbering standards
 lo                   | 1.1             | [null]            | Large Object maintenance
 intarray             | 1.2             | [null]            | functions, operators, and index support for 1-D arrays of integers
 dict_int             | 1.0             | [null]            | text search dictionary template for integers
 amcheck              | 1.2             | 1.2               | functions for verifying relation integrity

我敢打赌intarray有我需要的东西！创建该扩展：

gabrielle=# CREATE EXTENSION intarray;
CREATE EXTENSION

……看看我现在有哪些运营商：

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 = 'gist'
    ORDER BY index_method, opclass_name;
 
 index_method |     opclass_name     |        indexed_type         | is_default
--------------+----------------------+-----------------------------+------------
...
 gist         | gist__int_ops        | integer[]                   | t
 gist         | gist__intbig_ops     | integer[]                   | f
...

一定是gist__int_ops！

gabrielle=# CREATE INDEX CONCURRENTLY idx_try_gist ON my_table USING gist (my_ids gist__int_ops);
CREATE INDEX

• 在实践中，我收到了以下错误消息：

gabrielle=# CREATE INDEX CONCURRENTLY idx_try_gist ON mytable USING gist (user_ids gist__int_ops);
ERROR:  data is too sparse, recreate index using gist__intbig_ops opclass instead
Time: 2121.985 ms (00:02.122)

这里的“int”与“intbig”指的是数据集的大小和基数，而不是整数与 bigint 数据类型。

按照错误消息中的说明轻松解决此问题：

gabrielle=# DROP INDEX CONCURRENTLY idx_try_gist;
gabrielle=# CREATE INDEX CONCURRENTLY tmp_index_delayed_jobs_on_group_keys_gist ON delayed_jobs_tmp_copy_0618 USING gist (group_keys gist__intbig_ops);

原文标题：How to fix ‘data type [x] has no default operator class for access method [y]’ error messages
原文作者：GORTHX
原文地址：https://gorthx.wordpress.com/2022/08/13/how-to-fix-data-type-x-has-no-default-operator-class-for-access-method-y-error-messages/