Ловушка секции по умолчанию: создание новой секции завершается ошибкой '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.