avoidAttachingPartition¶

Group safety · Recommended · Migrations only · Since 0.25.0
Diagnostic lint/avoidAttachingPartition

Sources: inspired by 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/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": {
      "avoidAttachingPartition": "error"
    }
  }
}

How to suppress¶

Suppress this diagnostic with a comment:

-- pgls-ignore avoidAttachingPartition