postgresql - 将没有长独占锁的表中的整型键更改为bigint键

Question

在 PostgreSQL 9.1 中是否可以从具有 bigint 类型列的表继承具有 int 类型列的表？

如果第一个表不是一个选项，请将列类型更改为 bigint。

我有一个包含数千万个条目的表，其 ID 为 int。现在ID正在慢慢接近2^32，我在想是否合理地创建一个具有bigint ID的facade表并让原来的facade继承facade。这有什么意义吗？

Answer 1

不可以，当您从表继承时，无法更改父表的列类型。

列类型必须匹配，因为当您查询父表时(FROM 子句中没有 ONLY)，PostgreSQL 也会隐式扫描子表并附加其结果。如果类型不同，它就无法做到这一点，就像您无法UNION不同类型的表一样。

根据评论更新:

使用 View 和DO INSTEAD触发器

我建议使用两个表的并集 View ，并将旧表的内容向上转换为 bigint。定义一个 INSTEAD OF 触发器，将 INSERT 重定向到新表。

如果您执行UPDATE和DELETE，您可能应该在每个表上定义一个CHECK约束，将ID的范围限制为非重叠范围，然后根据 ID 决定将 DELETE 或 UPDATE 路由到哪个表。

对于UPDATE，您甚至可以将其转换为DELETE ... RETURNING和INSERT(可能在wCTE中)来移 Action 为更新的一部分，从旧表到新表的行。

您将因此而遭受性能损失，但您将避免整个表重写的需要。

逐步就地更改 key 类型

您说更改键类型不是一个选项，但实际上您的意思似乎是“以需要在独占锁下进行全表重写的方式更改键类型不是一个选项”。

您可以做的是:

• 更改表...添加列 new_key bigint; 。 不要将其标记为NOT NULL或为其指定DEFAULT
• 向表添加一个 BEFORE INSERT OR UPDATE ... BEFORE EACH ROW ... 触发器，将整数 ID 列复制到 bigint id 列，例如NEW.new_id := NEW.id
• 分批UPDATE表将整数键复制到bigint列，并在每批之后VACUUM
• 所有新行和现有行都有 bigint 键后，使用同时创建唯一索引...在其上创建唯一索引。
• 创建索引后，添加not null 约束。不幸的是，这将进行顺序扫描来验证约束。如果您甚至无法容忍这一点，可以通过一些技巧来解决它，但我不准备在公开场合向他们提供建议，因为您需要确切知道自己在做什么才能安全地进行，并在适当的情况下使用它。
• 开始事务，删除触发器，删除旧的主键约束和旧的id列，并在 bigint 键上添加新的主键约束，指定您同时创建的现有索引作为约束索引，然后提交。这避免了在独占锁下构建索引的需要。

如果 PostgreSQL 支持将 not null 约束添加为 not valid，然后让您使用较弱的锁来验证它，那么这个过程会更好。不幸的是，它还没有这样做。欢迎提供补丁或其他贡献。

对于 PostgreSQL 9.5 上的某些操作，alter table 所采用的较弱锁定将使您受益匪浅。

理论上，PostgreSQL 可以通过在幕后完成所有这些操作来同时支持alter table ... alter type ...。不过，要正确地做到这一点需要做很多工作，所以我预计在不久的将来不会看到简单且固定的方法。