在拥有四千万条记录的表中添加多列主键

Question

在拥有四千万条记录的表中添加多列主键

3

我正在维护一个数据库，用于存储不同网络之间的数据传输信息。基本上，每次数据传输都会被记录下来，每个月末我会运行一个perl脚本，将日志文件加载到数据库表中。这个perl脚本和数据库模式是在我开始这个项目之前就完成的，而我并没有参与设计。

我使用这个链接来检索表（usage_detail是表名）的主键，但没有找到任何内容。由于表中有很多记录，很难跟踪重复项。我们曾经遇到过许多重复项被加载的问题（因为日志文件中存在错误，但这是另一个话题），最终不得不放弃最新的加载并重新加载所有修复后的新记录。你可以想象这是多么愚蠢和繁琐。

为了解决这个问题，我想在表中添加一个主键。由于几个原因，我们不想为主键添加一个全新的列。通过查看字段，我已经确定了一个多列主键。基本上它包括：传输开始时间戳、传输结束时间戳、传输的文件名（也包括整个路径）。看起来很不可能会有两条记录具有相同的这些字段。

以下是我的问题： 1）如果我在表中添加了这个主键，那么可能已经存在于表中的任何重复项会发生什么情况？

2）我应该如何将这个主键实际添加到表中（我们使用的是PostgreSQL 8.1.22）。

3）在添加了主键之后，假设加载脚本正在运行，并尝试加载一个重复项。PostgreSQL会抛出什么样的错误？我能在脚本中捕获它吗？

4）我知道你对加载脚本没有太多信息，但根据我提供的信息，你是否预见到可能需要更改的内容？

非常感谢您的帮助。谢谢。

- shaun

2个回答

2

如果有重复，您将无法添加它。您需要先删除重复项。
ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY(fld1, fld2);
PostgreSQL会给出一个错误代码为SQL state: 23505的错误。我不知道perl的任何内容，但我认为您可以捕获此错误。
同样，我不知道perl的任何内容，但我认为您可以捕获错误，然后由您决定如何处理它。

注意：您使用的是不受支持的PostgreSQL版本（建议升级）。我甚至无法在SqlFiddle上测试这个版本。因此，所有答案都基于PosgreSQL 9.1。

- David S

当你说我无法添加PK时，你到底意味着什么？我的意思是，如果我运行ALTER TABLE并且表内有重复项，它只会给我一个错误？至于PosgreSQL版本，这真的不在我的控制范围内，但我一定会让正确的人知道需要升级。 - shaun

是的，你会收到一个错误。主键字段中的值必须是唯一的。注意：这是一个很容易测试的事情。你可以创建一个临时表并插入一些记录，看看会发生什么。 - David S

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

使用序列列

您计划为 4000 万行添加一个不必要的巨大索引。而且您甚至不确定它是否唯一。我强烈建议不要采取这种行动。相反，添加一个 serial 列，并完成它：

ALTER TABLE tbl ADD COLUMN tbl_id serial PRIMARY KEY;

这就是你需要做的全部，其余部分将自动完成。手册中或这些密切相关的答案中有更多信息：
PostgreSQL主键自动递增在C++中崩溃
自动递增SQL函数

添加一个serial列是一次性操作，但代价高昂。整个表必须被重写，操作期间会阻塞更新。最好在非高峰期无并发负载时完成。我在这里引用手册：

添加具有非空默认值的列或更改现有列的类型将要求重新编写整个表和索引。[...] 对于大型表，表格和/或索引重建可能需要大量时间；并且暂时需要多达两倍的磁盘空间。

由于这实际上重新编写了整个表，因此您可以创建一个带有序列pk列的新表，从旧表中插入所有行，让序列使用其序列中的默认值填充，删除旧表并重命名新表。更多相关答案：
在PostgreSQL 9.2中更新数据库行而不锁定表
 添加新列而不锁定表？

确保所有INSERT语句都有目标列表，然后额外的列就不会使它们混淆：

INSERT INTO tbl (col1, col2, ...) VALUES ...

注意：

INSERT INTO tbl VALUES ...

serial 是使用一个 integer 列（4个字节）实现的。
主键约束是通过在相关列上实施唯一索引和 NOT NULL 约束来实现的。
索引的内容存储方式很像表格。需要另外的物理存储空间。有关物理存储的更多信息，请参阅此相关答案：
在 PostgreSQL 中计算和保存空间

您的索引将包含 2 个时间戳（2 x 8 字节）以及一个包括路径在内的较长的文件名（大约 50 字节？）。这将使索引增加约 2.5 GB（40M x 60..某些字节），并使所有操作变慢。

处理重复项

如何处理“导入重复项”取决于您导入数据的方式以及如何定义“重复项”。

如果我们讨论的是COPY语句，则一种方法是使用临时暂存表，并在INSERT命令中使用简单的SELECT DISTINCT或DISTINCT ON合并重复项：

CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM tbl LIMIT 0;     -- copy structure without data and constraints

COPY tbl_tmp FROM '/path/to/file.csv';

INSERT INTO tbl (col1, col2, col3)
SELECT DISTINCT ON (col1, col2)
       col1, col2, col3 FROM tbl_tmp;

或者，还可以禁止与已存在的行重复：

INSERT INTO tbl (col1, col2, col3)
SELECT i.*
FROM  (
   SELECT DISTINCT ON (col1, col2)
          col1, col2, col3
   FROM   tbl_tmp
   ) i
LEFT   JOIN tbl t USING (col1, col2)
WHERE  t.col1 IS NULL;

会话结束时，临时表会自动删除。

但是更好的解决方法是处理产生重复值的错误根源。

原始问题

1) 如果所有列中存在重复项，则根本不能添加主键。

2) 我不会接触一个完全过时、效率低下、不再受支持并可能存在许多未修补安全漏洞的PostgreSQL数据库 8.1版本。官方Postgres版本网站
 @David已经提供了SQL语句。

3和4) 重复键违规。 PostgreSQL抛出错误也意味着整个事务会回滚。在perl脚本中捕获它无法使其余事务继续进行。您必须创建一个具有plpgsql的服务器端脚本，例如可以在其中捕获异常。