默认分区陷阱：新分区因 'would be violated by some row' 而失败

命令

-- Which periods have rows in the default partition?
SELECT date_trunc('month', created_at)::date AS month, count(*) AS row_count
FROM sales_default
GROUP BY 1
ORDER BY 1;

-- Move the rows into a new partition and attach it in one transaction
BEGIN;
SET LOCAL lock_timeout = '5s';
LOCK TABLE sales_default IN ACCESS EXCLUSIVE MODE;
CREATE TABLE sales_2027_02 (LIKE sales INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
WITH moved AS (
  DELETE FROM sales_default
  WHERE created_at >= DATE '2027-02-01' AND created_at < DATE '2027-03-01'
  RETURNING *
)
INSERT INTO sales_2027_02 SELECT * FROM moved;
ALTER TABLE sales ATTACH PARTITION sales_2027_02
  FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');
COMMIT;

作用

按周期统计落入默认分区的行数；在一个事务中将属于新分区范围的行移动到新表并将其挂载。

适用场景

当 CREATE TABLE ... PARTITION OF 或 ATTACH 因 'updated partition constraint for default partition would be violated by some row' 而失败，或发现默认分区在增长时。

注意

存在默认分区时，每个新分区都会全量扫描默认分区，并在此期间对其持有 ACCESS EXCLUSIVE；大的默认分区意味着每次维护都会中断。移动行期间默认分区不可写入；请在低流量时进行。永久解决方法：预先创建分区，并通过监控保持默认分区为空（行数 > 0 时告警），或者根本不使用默认分区，让超出范围的 INSERT 失败，使问题尽早暴露。默认分区还会阻止 DETACH ... CONCURRENTLY。