主页 / dba / 问题 / 343791
Accepted
Ruud van den Boomen
Asked: 2024-11-22 20:35:54 +0800 CST

添加新的外键列时是否需要 NOT VALID

  • 772

我知道添加外键约束需要进行表扫描并对SHARE ROW EXCLUSIVE两个表进行锁定。

为了防止可能冗长的表扫描，可以使用该方法添加约束NOT VALID。但我想知道，在添加新列时，是否也应该使用NOT VALID，或者 Postgres 是否足够智能，可以识别出这是一个新列，因此不需要扫描整个表？

我正在使用Django，添加外键列的生成的SQL如下所示：

ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;
postgresql

1 个回答

  1. Best Answer

    NOT VALID只能添加到ALTER TABLE ... ADD CONSTRAINT，而不能ALTER TABLE ... ADD COLUMN直接添加到 。有效的 SQL 脚本如下（可以在单个事务中）：

    ALTER TABLE example
ADD COLUMN new_column_id integer NULL;

ALTER TABLE example
ADD CONSTRAINT example_new_column_id_fk FOREIGN KEY (new_column_id) REFERENCES another_table(id) DEFERRABLE INITIALLY DEFERRED NOT VALID;

    我通过在 Postgres 16 中锁定目标表的方式进行了测试 - 与创建 FK 约束在目标表上冲突的最轻的锁- 在一个事务中：ROW EXCLUSIVESHARE ROW EXCLUSIVE

    BEGIN;
LOCK TABLE usr IN SHARE ROW EXCLUSIVE MODE;

-- waiting idle in transaction ...

-- ROLLBACK;  -- later

    然后我在第二个并发事务中运行上述脚本。

    结果：第二个事务必须等到第一个事务释放锁ROW EXCLUSIVE- 不管有没有。因此，无论如何NOT VALID添加 FK 都会锁定目标表。SHARE ROW EXCLUSIVE

    根据Laurenz 的评论，以下检查要么非常快，要么由于引用列中缺少值而实际上没有进行。

    所以：

    • 如果问题在于裸成本或锁的持续时间，我想说，NOT VALID在这种特殊情况下是不必要的。
    • 如果问题出在锁本身，那么NOT VALID遗憾的是无法避免。
    • 1

相关问题