主页 / dba / 问题 / 292185
Accepted
Nate
Asked: 2021-05-25 13:02:47 +0800 CST

文本列比较等于 where 子句但不选择匹配行

  • 772

查询生产数据库中的表时遇到问题。一个文本列将与我们在 where 子句中过滤的字符串进行比较，但 postgres 不会选择该行。（我们在 postgres 11.11 上）我们的表设置如下：

(PROD)=> \d names;
                           Table "public.names"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 name                 | text                        |           | not null |
 processed_name       | text                        |           | not null |
 name_index           | integer                     |           | not null |
 when_created         | timestamp without time zone |           | not null |
Indexes:
    "names_pkey" PRIMARY KEY, btree (name, processed_name)
    "names_name_index_key" UNIQUE CONSTRAINT, btree (name_index)
    "ix_names_name" btree (name)
    "ix_names_processed_name" btree (processed_name)

当我们处理名称列表时，我们检查它们是否已经在表中，以防止重复添加和违反主键约束。

但是，在一个名称“Сергей Иванович МЕНЯЙЛО”上，查看该名称是否已存在的查询返回一个空集
，我希望返回具有相同名称的行。但是，当我们尝试在表中插入行时，我们会遇到主键冲突

以下是一些可以更好地解释问题的查询

(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names where name_index = 75128;
      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

但是，在名称列上进行过滤不会选择任何行。

2021-05-24 20:37:41 UTC
(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' 
    names_compare_equal 
FROM names 
WHERE name = 'Сергей Иванович МЕНЯЙЛО';

       name_index     | name | names_compare_equal
----------------------+------+---------------------
(0 rows)

因此，如果我们尝试插入行，我们会遇到主键冲突：

(PROD)>=> INSERT INTO names (name_index, name, processed_name, when_created) 
  VALUES (89266, 'Сергей Иванович МЕНЯЙЛО', lower('Сергей Иванович МЕНЯЙЛО'), now());

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name, processed_name)=(Сергей Иванович МЕНЯЙЛО, сергей иванович меняйло) already exists.

更重要的是，如果我根据行的哈希进行查询，我会得到正确的结果：

(PROD)=> SELECT name_index, 
    name, 
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal 
FROM names 
WHERE md5(name) = md5('Сергей Иванович МЕНЯЙЛО');

      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

这只发生在我们的生产数据库上 - 它具有以下编码设置

      Name      |     Owner      | Encoding |   Collate   |    Ctype    |
----------------+----------------+----------+-------------+-------------+
 PROD DB        | PROD DB OWNER  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

这对我来说非常莫名其妙，所以关于下一步要检查什么的想法会很有帮助

postgresql string

1 个回答

  1. Best Answer

    损坏的索引将是这里的主要嫌疑人。测试：

    SELECT * FROM names WHERE name || '' = 'Сергей Иванович МЕНЯЙЛО';

    该表达式name || ''不能使用任何索引，因此您会获得顺序扫描。如果该查询找到您的条目，则您的诊断结果是：索引损坏。可能是 just (name)，但由于多个索引符合条件，请重新检查EXPLAIN. （这可能不是 PK，因为它仍然会在您的测试中引发独特的违规行为，但它也可能已损坏......）

    Postgres 11.11的发行说明中有一条注释：

    ...请参阅下面的第二个更改日志项目，其中描述了升级后重新索引索引可能是可取的情况。

    或者，您的底层操作系统中的语言环境可能已更新？相同的修复：重新索引。

    索引损坏还有其他原因，但唯一的其他常见原因是硬件问题。这应该立即触发更严厉的措施，从备份开始。

    重新创建受影响的索引。您可以使用REINDEX：

    REINDEX INDEX ix_names_name;

    如果您需要允许对表的并发访问，请使用非阻塞（但速度较慢）CONCURRENTLY：

    REINDEX INDEX ix_names_name CONCURRENTLY;

    如果有理由相信问题可能是系统性的，请重新创建表上的所有索引：

    REINDEX TABLE names;

    或整个数据库：

    REINDEX DATABASE name_of_current_database;

    如果进行更大的清理，我会建议一个没有并发访问的维护窗口。还有很多maintenance_work_mem。

    除此之外，您有两列的这些索引，name并且processed_name：

    "names_pkey" PRIMARY KEY, btree (name, processed_name)
"ix_names_name" btree (name)
"ix_names_processed_name" btree (processed_name)

    上的 PK 索引(name, processed_name)可以用于所有可以使用附加索引的内容(name)。仅当列相当大时，该附加索引才可能有用processed_name-在这种情况下，我会考虑从更有效的 PK 开始。看：

    • 5

相关问题