我有一个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)
server_name
只有一个值,那么对于主键约束{server_name, line_number, log_generation}来说,{line_number, log_generation}是否必须唯一呢? - Mike Sherrill 'Cat Recall'