如何在PostgreSQL中安全地截断和重新填充表格？

Question

如何在PostgreSQL中安全地截断和重新填充表格？

5

我的数据库中的一张表需要每天更新。一个Web服务器每5秒主动查询此表。现在我不能简单地使用UPDATE命令来更新表格行，因为存在一些约束条件，所以我需要清除所有行并重新填充表格。如何在不影响Web服务器的情况下安全地执行此操作？重新填充是由另一个与 Web 服务器隔离的 Web 服务完成的。我正在使用 Spring 框架。这个表大约有170k行和5列。

- Vineet

1

只是好奇想知道，为什么你想要清除所有行并重新填充表格？？你需要如何重新填充数据？？ - Vivek S.

假设您真正“必须”重新填充表格（仅询问确认因为这是一个相当奇怪的要求），则drop table; create table;比delete from table;快得多-因此，计划在Web服务器的两个请求之间执行该操作可能是一个好主意。 - fvu

为什么不能更新行？有什么限制阻止了这个操作？那么为什么它们又不会阻止删除所有内容呢？ - user330315

该表格包含IP地址范围（起始IP到结束IP）与国家之间的映射关系。我从多个来源下载并解析这个映射关系，将它们保存到数据库中。但是根据哪个字段作为主键来更新行没有统一的标准，这限制了这个过程。 - Vineet

我不明白... 你需要截断表格，然后再填充它。所以你可以只用 truncate table my_table; 然后填充它。或者我漏掉了什么？ - stas.yaranov

我需要安全地执行此操作。假设在清空表格后重新填充之前的那一刻，我的网络服务会查询此表格。此时，由于该表格为空，我的网络服务将会失败。 - Vineet

1个回答

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

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

- Craig Ringer · Accepted Answer

在单个事务中截断并重新填充表格。~~截断对于并发读取者来说是不可见的，他们继续看到旧数据。~~ 根据@AlexanderEmelianov和文档的更正：

TRUNCATE不是MVCC安全的。如果并发事务在截断发生之前使用了快照，则截断后，该表将对并发事务显示为空。有关详细信息，请参见第13.5节。

因此，在TRUNCATE提交事务之后，开始之前TRUNCATE的并发事务将看到该表为空。

在表被截断后尝试进行写入的任何事务都将等待执行截断操作的事务提交或回滚。

BEGIN;

TRUNCATE TABLE my_table;

INSERT INTO my_table (blah) VALUES (blah), (blah), (blah);

COMMIT;

你可以使用COPY命令代替INSERT命令，它适用于普通事务中的任何内容。

更好的是，在 PostgreSQL 中有一种优化方式，使在截断后填充表比其他方式更快。