PostgreSQL 14中预创建外键后执行ATTACH PARTITION时被引用表触发ExclusiveLock的原因咨询

嘿，这个问题问到点子上了——PostgreSQL在处理分区附加时的锁行为，尤其是涉及预建外键的场景，确实容易让人困惑。我来给你拆解一下为什么会出现这种情况：

核心原因：分区附加时的约束一致性验证 #

当你执行ALTER TABLE ... ATTACH PARTITION命令时，PostgreSQL需要确保待附加的分区表完全符合主分区表的约束规则，不会破坏整个分区表的数据完整性。对于你提前创建的外键约束来说，这个验证过程不止涉及待附加的表，还会关联到被引用的表：

• 外键约束的有效性依赖于被引用表的主键/唯一约束——如果被引用表的这些约束在验证过程中被修改或删除，那你预建在分区上的外键就会变成无效约束，进而破坏整个分区表的数据完整性。
• 为了避免这种风险，PostgreSQL会给被引用表加上ExclusiveLock，在验证期间阻止任何可能修改被引用表结构（比如删除主键、修改约束）的DDL操作，确保外键约束的兼容性检查是在一个“稳定”的环境中完成的。

对比无预建外键的场景 #

如果你不提前创建外键，而是等分区附加完成后再添加：

• 此时执行ATTACH PARTITION时，PostgreSQL只需要验证分区的检查约束、索引等与主表匹配，不需要涉及被引用表，因此只会给主表加AccessExclusiveLock。
• 后续添加外键时，虽然也会给被引用表加锁，但这一步是独立的，你可以选择在业务低峰期执行，灵活度更高。

给你几个优化思路（如果这个锁影响到业务的话） #

• 评估锁的实际影响时长：其实这种ExclusiveLock的持有时间通常很短，只是完成约束兼容性验证的时间，如果你被引用表不是高并发的热点表，可能完全可以接受。
• 调整外键创建顺序：可以先附加分区，然后在主表上创建NOT VALID的外键（这一步主表的AccessExclusiveLock时间会稍微变长，但比提前建外键的锁影响范围小），之后再用ALTER TABLE ... VALIDATE CONSTRAINT来验证现有数据——这一步只需要给被引用表加ShareLock，不会阻塞普通的读写操作，而且可以在业务低峰期异步完成。
• 确认主表是否需要外键：如果主表本身不需要全局的外键约束，只是分区单独需要，那可能需要重新考虑设计，但这种场景比较少见。

备注：内容来源于stack exchange，提问作者piotrd