デフォルトパーティションの落とし穴: 新しいパーティションが'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;

概要

デフォルトパーティションに入った行を期間ごとに数え、新しいパーティションの範囲内の行を新しい表に移してアタッチする。すべてを1つのトランザクションで行う。

使用する場面

CREATE TABLE ... PARTITION OFまたはATTACHが'updated partition constraint for default partition would be violated by some row'で失敗したとき、またはデフォルトパーティションの増大が見つかったときに。

注意

デフォルトパーティションが存在すると、新しいパーティションを作成するたびにデフォルトがフルスキャンされ、その間ACCESS EXCLUSIVEが保持される。デフォルトパーティションが大きいと、メンテナンスのたびに停止が発生する。行の移動中はデフォルトへの書き込みができないため、トラフィックの少ない時間帯に行う。恒久的な対策: パーティションを事前作成し、監視でデフォルトを空に保つ(行数 > 0でアラート)、またはデフォルトをまったく使用せず、範囲外のINSERTを失敗させて問題を早期に表面化させる。デフォルトパーティションはDETACH ... CONCURRENTLYも妨げる。