Piège de la partition par défaut : une nouvelle partition échoue avec 'would be violated by some row'

Commande

-- 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;

Ce que fait la commande

Compte par période les lignes tombées dans la partition par défaut ; déplace les lignes de la plage de la nouvelle partition dans une nouvelle table et l'attache, le tout en une seule transaction.

Quand l'utiliser

Lorsque CREATE TABLE ... PARTITION OF ou ATTACH échoue avec 'updated partition constraint for default partition would be violated by some row', ou lorsqu'on constate que la partition par défaut grossit.

Attention

En présence d'une partition par défaut, chaque nouvelle partition parcourt entièrement la partition par défaut et y maintient ACCESS EXCLUSIVE pendant ce temps ; une grosse partition par défaut signifie une interruption à chaque maintenance. La partition par défaut est fermée aux écritures pendant le déplacement des lignes ; faites-le en période de faible trafic. Correction permanente : créez les partitions à l'avance et gardez la partition par défaut vide sous supervision (alerte si nombre de lignes > 0), ou n'en utilisez pas du tout, pour que les INSERT hors plage échouent et que le problème apparaisse tôt. Une partition par défaut empêche aussi DETACH ... CONCURRENTLY.