avoidAddingExclusionConstraint

Diagnostic Category: lint/safety/avoidAddingExclusionConstraint

Since: vnext

Sources: - Inspired from: pgfence/add-constraint-exclude

Description

Adding an exclusion constraint acquires an ACCESS EXCLUSIVE lock.

Exclusion constraints require a full table scan to validate and block all reads and writes while held. Unlike other constraints, there is no concurrent alternative. Use SET lock_timeout to limit the impact on concurrent operations.

This also applies to exclusion constraints defined inline in CREATE TABLE.

Examples

Invalid

alter table my_table add constraint my_excl exclude using gist (col with &&);

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

  ! Adding an exclusion constraint acquires an ACCESS EXCLUSIVE lock.

  > 1 │ alter table my_table add constraint my_excl exclude using gist (col with &&);
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i There is no concurrent alternative for exclusion constraints. Use SET lock_timeout to limit the impact on concurrent operations.

Valid

alter table my_table add constraint my_check check (col > 0) not valid;

How to configure

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