PostgreSQL：带DEFAULT分区的表如何无AccessExclusiveLock添加分区？

当给带有DEFAULT分区的PostgreSQL分区表添加新分区时，默认情况下PostgreSQL会扫描整个DEFAULT分区以确认无匹配新分区规则的数据，这会导致长时间持有AccessExclusiveLock，阻塞所有读写操作。通过添加特定约束可以跳过扫描，但你的操作仍出现锁问题，大概率是约束逻辑或操作细节有误。

问题根源 #

你的操作流程框架正确，但可能存在以下细节错误：

• DEFAULT分区的排除约束逻辑不严谨，未完全覆盖新分区的范围
• 新分区的CHECK约束与ATTACH时指定的范围不匹配
• 分区键（task_time，带时区的timestamp）的时区处理不一致，导致范围判断失效

正确操作步骤 #

以下是经过验证的、能避免长时锁的完整流程：

1. 创建空的新分区表（作为普通表）

CREATE TABLE tasks_partitions.tasks_20230111 (
    LIKE tasks INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS
);

2. 给新分区添加严格匹配目标范围的CHECK约束
   确保约束条件与后续ATTACH的范围完全一致，注意时区统一（这里使用UTC示例，根据你的实际时区调整）：

ALTER TABLE tasks_partitions.tasks_20230111
ADD CONSTRAINT chk_tasks_20230111_time CHECK (
    task_time >= '2023-01-11 00:00:00+00'
    AND task_time < '2023-01-12 00:00:00+00'
);

3. 给DEFAULT分区添加排除新分区范围的NOT VALID约束
   NOT VALID关键字会让PostgreSQL跳过对现有数据的扫描，仅约束未来写入的数据，避免加长时锁：

ALTER TABLE tasks_partitions.tasks_default
ADD CONSTRAINT chk_tasks_default_exclude_20230111 CHECK (
    NOT (task_time >= '2023-01-11 00:00:00+00' AND task_time < '2023-01-12 00:00:00+00')
) NOT VALID;

4. 执行ATTACH PARTITION操作
   此时PostgreSQL会通过已有的两个约束确认DEFAULT分区中无匹配新分区的数据，跳过全表扫描，仅持有极短时间的AccessExclusiveLock（仅用于元数据修改）：

ALTER TABLE tasks
ATTACH PARTITION tasks_partitions.tasks_20230111
FOR VALUES FROM ('2023-01-11 00:00:00+00') TO ('2023-01-12 00:00:00+00');

关键注意事项 #

• 约束的范围必须完全匹配ATTACH时指定的区间，不能有重叠或遗漏，否则PostgreSQL仍会触发DEFAULT分区扫描
• 对于带时区的timestamp类型，必须确保所有时间范围使用统一时区（如UTC），避免因时区转换导致范围判断错误
• NOT VALID约束仅对未来数据生效，若你能确认DEFAULT分区中确实无匹配新分区的数据，后续可以执行VALIDATE CONSTRAINT来验证约束（此操作会加ShareUpdateExclusiveLock，不阻塞读写）：

ALTER TABLE tasks_partitions.tasks_default
VALIDATE CONSTRAINT chk_tasks_default_exclude_20230111;

内容的提问来源于stack exchange，提问作者user797963