Migration Guard › sample report 1
ADD COLUMN with a DEFAULT
Illustrative: written by hand to show the report format. The Action is not built yet, and this statement is a minimal synthetic example, not taken from a real repository.
| Statement | ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new'; |
|---|---|
| Lock taken | ACCESS EXCLUSIVE on orders (the ALTER TABLE default). It blocks all reads and writes on the table while held; for a constant default the hold is brief. |
| Rewrites the table | Depends on the PostgreSQL version. On PostgreSQL 11 and later, a non-volatile default is stored in the catalog and the table is not rewritten. On PostgreSQL 10 and earlier, the table is rewritten. |
| Verdict | REVIEW |
Why
On PostgreSQL 11 or later this statement takes a brief ACCESS EXCLUSIVE lock without a table rewrite, but the lock can still queue behind a long-running transaction and block other queries behind it. If your server is PostgreSQL 10 or earlier, or the default is volatile (for example clock_timestamp()), the table is rewritten under that lock, which would be BLOCK-RISK on a large table.
Lower-risk rewrite
Check your server version first. Keep the default a constant, and set a short lock_timeout so the migration fails instead of queueing:
SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new';
Sources: PostgreSQL docs: explicit locking; PostgreSQL docs: ALTER TABLE (notes on ADD COLUMN with DEFAULT).
Table size is unknown to this report. Statements outside the fixed rule list would be marked REVIEW.