一张表如何违反自己的主键索引？

Question

我有一个PostgreSQL数据库，其中有一张表，主键应用于三列。根据数据库，该键上有一个索引：

Indexes:
    "full_log_pkey" PRIMARY KEY, btree (server_name, line_number, log_generation)

然而一些简单的测试表明我有重复的键：

select count(*) from full_log;
  count
----------
 60644405

select count(*) from 
    (select distinct server_name, 
                     line_number, 
                     log_generation 
            from     full_log) as foo;
  count
----------
 60636564

很明显，基于主键，唯一的行数比总行数要少。我的问题是，这是怎么可能的呢？

编辑：完整的表定义如下：

                 Table "public.full_log"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 activity       | character(1)                |
 archivaldate   | timestamp without time zone |
 media_type     | character varying(5)        |
 vsn            | text                        |
 archive_set    | character varying(20)       |
 copy           | smallint                    |
 file_start     | integer                     |
 file_offset    | integer                     |
 fs_name        | character varying(20)       |
 inode          | double precision            |
 file_length    | bigint                      |
 file_type      | character(1)                |
 overflow       | integer                     |
 device_number  | integer                     |
 server_name    | text                        | not null
 path           | text                        |
 line_number    | integer                     | not null
 log_generation | integer                     | not null
Indexes:
    "full_log_pkey" PRIMARY KEY, btree (server_name, line_number, log_generation)
Foreign-key constraints:
    "full_log_server_name_fkey" FOREIGN KEY (server_name) REFERENCES servers(server_name)
Rules:
    insert_update_full_log AS
    ON INSERT TO full_log
   WHERE (EXISTS ( SELECT full_log.activity, full_log.archivaldate, full_log.media_type, full_log.vsn, full_log.archive_set, full_log.copy, full_log.file_start, full_log.file_offset, full_log.fs_name, full_log.inode, full_log.file_length, full_log.file_type, full_log.overflow, full_log.device_number, full_log.server_name, full_log.path, full_log.line_number, full_log.log_generation
           FROM full_log
          WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation)) DO INSTEAD  UPDATE full_log SET activity = new.activity, archivaldate = new.archivaldate, media_type = new.media_type, vsn = new.vsn, archive_set = new.archive_set, copy = new.copy, file_start = new.file_start, file_offset = new.file_offset, fs_name = new.fs_name, inode = new.inode, file_length = new.file_length, file_type = new.file_type, overflow = new.overflow, device_number = new.device_number, path = new.path
  WHERE full_log.server_name = new.server_name AND full_log.line_number = new.line_number AND full_log.log_generation = new.log_generation

一个重复行的示例：

 select * from full_log where line_number = 6332986;
 activity |    archivaldate     | media_type |  vsn   | archive_set | copy | file_start | file_offset | fs_name |   inode    | file_length | file_type | overflow | device_number | server_name |                                           path                                            | line_number | log_generation
----------+---------------------+------------+--------+-------------+------+------------+-------------+---------+------------+-------------+-----------+----------+---------------+-------------+-------------------------------------------------------------------------------------------+-------------+----------------
 A        | 2010-10-13 10:49:49 | ti         | Z00711 | lcbp_rel    |    1 |     226237 |      779099 | lcbp    | 21798068.3 |    31198108 | f         |        0 |          8511 | redact      | wdl/delivery/irishparis_2010_09/MSE2_Histoire des rois d'Angleterre/MSE2_239.TIF          |     6332986 |              1
 A        | 2010-10-13 10:49:49 | ti         | Z00711 | lcbp_rel    |    1 |     226237 |      779099 | lcbp    | 21798068.3 |    31198108 | f         |        0 |          8511 | redact      | wdl/delivery/irishparis_2010_09/MSE2_Histoire des rois d'Angleterre/MSE2_239.TIF          |     6332986 |              1
(2 rows)

- astine

---

值得注意的是，我在尝试更改表列的类型时发现了这个问题。该列不是主键的一部分。当发生这种情况时，Postgres会删除并重新创建索引，并且在我这样做时会触发错误。 - astine

有没有可能挖掘出其中一些重复的行？如果使用GROUP BY而不是DISTINCT会发生什么？完整的表模式是什么样子的？ - mu is too short

@mu 我已经添加了完整的表模式。插入规则将用几乎相同的更新替换已存在主键的插入。 - astine

一些奇怪的Unicode字符/组合被认为是相等的，但在字节级别上却不同？ - Jens Schauder

如果server_name只有一个值，那么对于主键约束{server_name, line_number, log_generation}来说，{line_number, log_generation}是否必须唯一呢？ - Mike Sherrill 'Cat Recall'

Answer 1

这个查询返回什么？

select server_name, line_number, log_generation 
from full_log
group by server_name, line_number, log_generation
having count(*) > 1

可能比较有帮助的是将其与之进行比较。

select line_number, log_generation 
from full_log
group by line_number, log_generation
having count(*) > 1

但是有可能不会。我认为这个从句

WHERE (EXISTS ( SELECT full_log.activity, 
                       full_log.archivaldate, 
                       full_log.media_type, 
                       full_log.vsn, 
                       full_log.archive_set, 
                       full_log.copy, 
                       full_log.file_start, 
                       full_log.file_offset, 
                       full_log.fs_name, 
                       full_log.inode, 
                       full_log.file_length, 
                       full_log.file_type, 
                       full_log.overflow, 
                       full_log.device_number, 
                       full_log.server_name, 
                       full_log.path, 
                       full_log.line_number, 
                       full_log.log_generation
               FROM full_log
               WHERE full_log.server_name = new.server_name 
                 AND full_log.line_number = new.line_number 
                 AND full_log.log_generation = new.log_generation)) 

可以将其简化为这个子句。（尽管我认为这对问题没有贡献。）

WHERE (EXISTS ( SELECT full_log.server_name, 
                       full_log.line_number, 
                       full_log.log_generation
                FROM full_log
                WHERE full_log.server_name = new.server_name 
                  AND full_log.line_number = new.line_number 
                  AND full_log.log_generation = new.log_generation)) 

您说当您更改非关键列的数据类型时，PostgreSQL会删除并重新创建索引。我没有看到这种情况发生，而且我也不确定我是否曾经见过这种情况。如果更改成功，我可能没有注意到，并且我不会经常更改列的数据类型。（既然我这么说了，我无法告诉您我上次更改列的数据类型是什么时候。）我现在使用的是PostgreSQL 9.0.2。

- Mike Sherrill 'Cat Recall'

---

我已经运行了命令，但需要一点时间；数据库相当大。 - astine

我已经运行了该命令，它返回了7841行。乍一看，我没有发现任何特别之处。 - astine

@astine - 你能挑选一个并找到相应的匹配行，仔细检查它们（也许通过转换为二进制来发现任何微小的差异）吗？ - Martin Smith

60636564 + 7841 = 60644405。至少你的数据库还能正确地进行算术运算。（小胜利。） - Mike Sherrill 'Cat Recall'

@mu 我可以，但是要等到星期一。 @Catcall 是的，至少那样。 - astine

Answer 2

你是否正在使用表继承？如果是这样，在子级中不会强制执行主键（至少在8.2版本中是这样的）。

- Andrew Lazarus

Answer 3

我会怀疑是 NULL 值。也许查询一下是否有任何这样的值。

- Randy

---

它们不允许在主键中使用。 - Martin Smith

Answer 4

我曾经看到过这种情况发生，当目标行的ctid由于before触发器而发生更改时。我记不清确切的步骤，但基本上就是这个问题。

如果你在那里有触发器，我的答案可能就是你遇到的相关主题：

PostgreSQL规则有什么用处？

要点是：如果当你发出insert/update/delete语句时，postgres没有返回正确数量的受影响行数，你可能会遇到奇怪的问题。一个规则（或使用after触发器）可以帮助你摆脱它们。

- Denis de Bernardy