PostgreSQL多列唯一性约束无效

Question

我是一个有用的助手，可以翻译文本。

我在 PostgreSQL 9.4 中遇到了一个奇怪的情况。

我有一张表：

id      integer                 NOT NULL DEFAULT nextval('users_userpropmeta_id_seq'::regclass)
name    character varying(255)  NOT NULL
cls     character varying(4)    NOT NULL
app_id  integer                 NOT NULL

而且还有一个唯一的约束：

UNIQUE (app_id, name)

现在我要查询表：

 SELECT COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

并获得：

 count | app_id |          name
-------+--------+-------------------------
     2 |   6019 | Создание серии писем
     2 |   6019 | Увеличение объемов базы
(2 rows)

那么 "unique" 不起作用吗？我已经进一步尝试了：

SELECT * FROM users_userpropmeta WHERE app_id=6019 AND name in ('Создание серии писем', 'Увеличение объемов базы');
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 7308 | Создание серии писем    | str |   6019
 7309 | Увеличение объемов базы | str |   6019
(2 rows)

只有两行，这里发生了一些神奇的事情。让我们用黑客方式找到这些行：

SELECT MAX(id), MIN(id), COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

 max  | min  | count | app_id |          name
------+------+-------+--------+-------------------------
 7308 | 4633 |     2 |   6019 | Создание серии писем
 7309 | 4636 |     2 |   6019 | Увеличение объемов базы
(2 rows)

这里是找到的行：

SELECT * FROM users_userpropmeta WHERE id IN (7308, 7309, 4633, 4636);
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 4633 | Создание серии писем    | str |   6019
 4636 | Увеличение объемов базы | str |   6019
 7308 | Создание серии писем    | str |   6019
 7309 | Увеличение объемов базы | str |   6019
(4 rows)

逐行比较是正确的，它们是相等的：

SELECT a.id, b.id, a.name, b.name, a.name = b.name FROM users_userpropmeta AS a CROSS JOIN users_userpropmeta AS b WHERE a.id IN (7308, 7309, 4633, 4636) AND b.id IN (7308, 7309, 4633, 4636);
  id  |  id  |          name           |          name           | ?column?
------+------+-------------------------+-------------------------+----------
 4633 | 4633 | Создание серии писем    | Создание серии писем    | t
 4633 | 4636 | Создание серии писем    | Увеличение объемов базы | f
 4633 | 7308 | Создание серии писем    | Создание серии писем    | t
 4633 | 7309 | Создание серии писем    | Увеличение объемов базы | f
 4636 | 4633 | Увеличение объемов базы | Создание серии писем    | f
 4636 | 4636 | Увеличение объемов базы | Увеличение объемов базы | t
 4636 | 7308 | Увеличение объемов базы | Создание серии писем    | f
 4636 | 7309 | Увеличение объемов базы | Увеличение объемов базы | t
 7308 | 4633 | Создание серии писем    | Создание серии писем    | t
 7308 | 4636 | Создание серии писем    | Увеличение объемов базы | f
 7308 | 7308 | Создание серии писем    | Создание серии писем    | t
 7308 | 7309 | Создание серии писем    | Увеличение объемов базы | f
 7309 | 4633 | Увеличение объемов базы | Создание серии писем    | f
 7309 | 4636 | Увеличение объемов базы | Увеличение объемов базы | t
 7309 | 7308 | Увеличение объемов базы | Создание серии писем    | f
 7309 | 7309 | Увеличение объемов базы | Увеличение объемов базы | t
(16 rows)

有人可以解释一下为什么唯一约束没有抛出异常就插入了两行吗？为什么IN运算符找不到它们（我想到了一些无法打印的符号？），但相等性测试表明它们是相等的？

附：最初，我创建了一个订阅PostgreSQL 9.6与pglogical，以从postgres创建逻辑副本到另一台服务器。但是出现了错误，说不能插入重复的行=）。

- M1ha Shvn

---

我认为质量不应该忽略字符串末尾的空格。也就是说，即使一个字符串有空格，另一个没有，它们仍然相等。 - Gordon Linoff

@GordonLinoff 可能是一些索引检查问题，SELECT 'abc' = 'abc ' 正如预期的那样返回 false。 - M1ha Shvn

看起来索引已经损坏了。尝试使用 reindex 命令重新建立索引。 - klin

@jarlh 这个表和索引在我之前就存在了 =) 所以我不确定。 - M1ha Shvn

我在另一张表中也遇到了同样的情况。我已经清理了数据，然后进行了REINDEX操作。但是过了一会儿，我又发现了一个问题... - M1ha Shvn

Answer 1

这一定是一个损坏的索引。

尝试使用enable_seqscan = off进行实验，看是否仍然会发现重复项。

很可能你需要通过ctid删除重复条目并重新索引表。

- Laurenz Albe