主页 / dba / 问题 / 158162
Accepted
amphibient
Asked: 2016-12-15 08:41:22 +0800 CST

在 Postgres 中创建 FK 时删除可能的孤儿

  • 772

我正在研究一个复制模式和数据的脚本。我首先创建所有结构，除了foreign keys从源中读取它之后system tables，然后复制数据，然后创建 FK，以便在我复制父级后将一个可能的孤儿插入到子表中（当我运行脚本时） )，即使 FK 创建由于孤立数据而失败，我仍然可以获得基本架构和数据。

我的下一步是做一些事情，比如将try/catchcreate FK 语句包裹起来，如果它失败，删除导致它的孤立数据。这样做会有些复杂，因为我必须提取所有引用键并组装我自己的删除语句（我目前不这样做，因为 PG 系统表提供了一个REFERENCES可以连接的就绪语句）。

但我想知道是否有一种方法可以为ALTER TABLE x ADD CONSTRAINT fk_y FOREIGN KEY ... DELETE ANY WOULD-BE ORPHANS我节省这一步。

所以如果我的父表是：

 parent_id     | parent_name
---------------------------------
 1             | Joe
 2             | Mary

孩子是：

child_id      | child_name     | parent_parent_id
--------------------------------------------------
 11           | Jimmy          | 1
 21           | Joey           | 2
 31           | Jeff           | 3

'Jeff' 将在强制引用完整性之前被删除，因为他的父级和他在源父级复制到目标之后被引入源。然后可以根据一致的数据创建 FK。

澄清（来自评论/聊天讨论）：

您是否在单个事务中读取数据？

  • 我不使用单笔交易，因为它太大了。我按表分解检索到的数据，并按表段（5000 或 10000 行的批次）对一些大表进行分解。

  • 这本质上是一个 ETL，它从 DB 中生成一个“邪恶的双胞胎”。希望这是有道理的。

  • 平均而言，我的环境中的数据库副本需要 20 分钟（对于小型数据库）到 1.5-2 小时（对于较大的数据库），有些表有数百万行。我在 Python 脚本的内存中逐块加载

pg_dump或呢pg_basebackup？

  • 我的应用程序做了一些其他pg_dump不做的事情，例如更好的日志记录。此外，pg_dump 期望在源和目标以及许多其他问题上使用相同的用户。我试过了，不喜欢。我更喜欢我的工具，我想解决这个仅偶尔发生的小问题。
postgresql restore

2 个回答

  1. Best Answer

    正如评论中所建议的那样，标准工具pg_dump可能会更好地为您服务。它会拍摄一致的快照并在备份期间自动保留参照完整性 - 假设源数据库是一致的（FK 约束到位）。如果可以，请使用它！

    还提到了相关工具pg_dumpall和pg_basebackup ，但它们适用于整个数据库集群，而您显然只想复制（部分）单个数据库。

    但是，这个问题可能仍然有正当理由。拒绝投机似乎是不公平的。这个问题本身就很有趣：

    如何将不一致的数据导入 FK 关系并修复它？

    NOT VALID选项

    创建 FK 约束没有DELETE ANY WOULD-BE ORPHANS选项（如您所愿），但Postgres 9.1 或更高版本NOT VALID中约束的相关选项应该是有用的。手册：FOREIGN KEYALTER TABLE

    ADD table_constraint [ NOT VALID ]

    这种形式使用与 相同的语法向表添加新约束 CREATE TABLE，加上选项NOT VALID，目前仅允许用于外键和 CHECK 约束。如果约束标记为NOT VALID，则跳过可能很长的初始检查以验证表中的所有行是否满足约束。约束仍将针对后续的插入或更新强制执行（也就是说，除非引用表中存在匹配行，否则它们将失败，在外键的情况下；除非新行与指定的检查匹配，否则它们将失败约束）。但是数据库不会假定约束适用于表中的所有行，直到使用该VALIDATE CONSTRAINT选项对其进行验证。

    因此，您可以创建相关表并导入数据并创建 PK 约束和 FK 约束NOT VALID。

    ALTER TABLE child ADD CONSTRAINT child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) NOT VALID;

    这可以防止任何新的写入违反参照完整性，同时容忍现有的违反。（并且系统知道这一点。）

    尽早删除孤儿：

    DELETE FROM child c
WHERE  NOT EXISTS (
   SELECT 1 FROM parent p
   WHERE  p.parent_id = c.parent_id
   );

    如果数据是一致的，这将无济于事，并且需要以任何一种方式完成工作以验证完整性。因此，对于需要验证的不可靠数据，它不会变得更有效率。

    然后验证约束：

    ALTER TABLE child VALIDATE CONSTRAINT child_parent_id_fkey;

    不必在同一个事务中。阅读手册了解详情。

    有关的：

    • 3

  2. 我正在研究一个复制模式和数据的脚本。我首先在从源系统表中读取外键后创建除外键之外的所有结构，然后复制数据，然后创建 FK，以便在复制父表后将可能的孤儿插入子表中（而我正在运行脚本），我仍然得到基本模式和数据，即使 FK 创建由于孤立数据而失败。

    这不是声音设计。诚信第一，始终如一。您所描述的内容使您的数据库处于需要稍后清理的状态。

    您真正想要的是设置一个紧密且防呆的模式，然后 to INSERT .. ON CONFLICT DO NOTHING这将跳过未能建立外键约束的行。

    害羞，如果你已经有脏数据，你只想删除你需要的

    1. BEGIN一笔交易
    2. DELETE冲突的行
    3. 添加外键CONSTRAINTS
    4. COMMIT交易。

    没有什么可以做DELETE ANY WOULD-BE ORPHANS的，那将是一个坏主意。以上是你能得到的最接近的。

    • -1

相关问题