pgsafe_

← all rules

attach-partition

warning

ATTACH PARTITION

Why it's unsafe

ATTACH PARTITION takes an ACCESS EXCLUSIVE lock on the table being attached and scans it to validate the partition bound (the parent stays available under SHARE UPDATE EXCLUSIVE), so the table being attached is unavailable for the scan's duration.

Safe rewrite

Add a CHECK constraint on the child matching the partition bound and validate it separately first (ADD CONSTRAINT ... CHECK (...) NOT VALID, then VALIDATE CONSTRAINT); ATTACH then skips the scan and the lock is brief.

Example

Try it in the playground →

Unsafe

ALTER TABLE measurement ATTACH PARTITION measurement_y2021
  FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

Safe

-- add a matching, validated CHECK first so ATTACH can skip the scan
ALTER TABLE measurement_y2021
  ADD CONSTRAINT measurement_y2021_bound
  CHECK (logdate >= '2021-01-01' AND logdate < '2022-01-01') NOT VALID;
ALTER TABLE measurement_y2021 VALIDATE CONSTRAINT measurement_y2021_bound;
ALTER TABLE measurement ATTACH PARTITION measurement_y2021
  FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

Related