requireSeparateConstraintValidation

Diagnostic Category: lint/safety/requireSeparateConstraintValidation

Since: vnext

Sources: - Inspired from: pgfence/not-valid-validate-same-tx

Description

Validating a constraint in the same transaction it was added as NOT VALID defeats the purpose.

Adding a constraint with NOT VALID avoids a full table scan and lock during creation. But if you immediately VALIDATE CONSTRAINT in the same transaction, the validation still holds the lock from the ADD CONSTRAINT, blocking reads and writes.

Run VALIDATE CONSTRAINT in a separate transaction to get the benefit of NOT VALID.

Examples

Invalid

Adding a NOT VALID constraint and validating it in the same transaction:

ALTER TABLE orders ADD CONSTRAINT orders_check CHECK (total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_check;

Valid

select 1;

How to configure

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