Eugene 🔒 trace report of examples/W12/bad/1.sql

Statement number 1 for 10ms

SQL

-- 1.sql
create table authors(
    id integer generated always as identity
        primary key,
    name text,
    email text
)

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Eugene 🔒 trace report of examples/W12/bad/2.sql

Statement number 1 for 10ms

SQL

-- 2.sql
set lock_timeout = '2s'

Locks at start

No locks held at the start of this statement.

New locks taken

No new locks taken by this statement.

Statement number 2 for 10ms

SQL

alter table authors
    alter column name set not null

Locks at start

No locks held at the start of this statement.

New locks taken

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

Hints

Validating table with a new NOT NULL column

ID: E2

A column was changed from NULL to NOT NULL. This blocks all table access until all rows are validated. A safer way is: Add a CHECK constraint as NOT VALID, validate it later, then make the column NOT NULL.

The column name in the table public.authors was changed to NOT NULL. If there is a CHECK (name IS NOT NULL) constraint on public.authors, this is safe. Splitting this kind of change into 3 steps can make it safe:

  1. Add a CHECK (name IS NOT NULL) NOT VALID; constraint on public.authors.
  2. Validate the constraint in a later transaction, with ALTER TABLE public.authors VALIDATE CONSTRAINT ....
  3. Make the column NOT NULL

Statement number 3 for 10ms

SQL

-- eugene: ignore E2, E4
alter table authors
    alter column email set not null

Locks at start

SchemaObjectModeRelkindOIDSafeDuration held (ms)
publicauthorsAccessExclusiveLockTable110

New locks taken

No new locks taken by this statement.