banAlterIdentity¶

Group destructive · Migrations only · Since Unreleased
Diagnostic lint/banAlterIdentity · Postgres error 428C9

Sources: inspired by squawk/ban-alter-identity

Description¶

Changing the identity of a column may break inserts from existing clients.

Inserts that provide an explicit value fail with 428C9 generated_always once the column is GENERATED ALWAYS, and inserts that omit the column fail once its identity is dropped.

Adding or switching to GENERATED BY DEFAULT keeps both kinds of inserts working, so it is not reported.

Examples¶

Invalid¶

alter table users alter column id add generated always as identity;

code-block.sql:1:1 lint/banAlterIdentity ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Changing the identity of column id may break inserts from existing clients.

  > 1 │ alter table users alter column id add generated always as identity;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Inserts that provide an explicit value fail once the column is GENERATED ALWAYS.

  i Update client inserts before changing the identity.

alter table users alter column id drop identity;

code-block.sql:1:1 lint/banAlterIdentity ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Changing the identity of column id may break inserts from existing clients.

  > 1 │ alter table users alter column id drop identity;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Inserts that omit the column fail once it no longer generates values.

  i Update client inserts before changing the identity.

alter table users alter column id set generated always;

code-block.sql:1:1 lint/banAlterIdentity ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Changing the identity of column id may break inserts from existing clients.

  > 1 │ alter table users alter column id set generated always;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Inserts that provide an explicit value fail once the column is GENERATED ALWAYS.

  i Update client inserts before changing the identity.

Valid¶

alter table users alter column id add generated by default as identity;

How to configure¶

{
  "linter": {
    "rules": {
      "banAlterIdentity": "error"
    }
  }
}

How to suppress¶

Suppress this diagnostic with a comment:

-- pgls-ignore banAlterIdentity