avoidAttachingPartition

Diagnostic Category: lint/safety/avoidAttachingPartition

Since: vnext

Sources: - Inspired from: pgfence/attach-partition

Description

Attaching a partition acquires an ACCESS EXCLUSIVE lock on the parent table.

ALTER TABLE ... ATTACH PARTITION locks the parent table, blocking all reads and writes. For large tables, this can cause significant downtime. Consider creating the partition with the correct constraints upfront, or use a staging table approach.

Examples

Invalid

alter table my_table attach partition my_partition for values from ('2024-01-01') to ('2025-01-01');

code-block.sql:1:1 lint/safety/avoidAttachingPartition ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Attaching a partition acquires an ACCESS EXCLUSIVE lock on the parent table.

  > 1 │ alter table my_table attach partition my_partition for values from ('2024-01-01') to ('2025-01-01');
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i This blocks all reads and writes on the parent table. Consider adding a matching CHECK constraint to the child table before attaching to minimize lock duration.

Valid

select 1;

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "avoidAttachingPartition": "error"
      }
    }
  }
}