Validate a CHECK constraint without a long lock
Add a CHECK as NOT VALID and it enforces new writes immediately while skipping the scan of existing rows; VALIDATE later scans without blocking writes. The constraint goes convalidated f then t, and bad inserts fail from the start.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Adding a CHECK constraint normally scans the whole table under a lock to prove existing rows comply. The NOT VALID form skips that scan, starts enforcing on new rows immediately, and lets you validate the old rows in a separate, non-blocking step.
Meridian adds a 'weight must be positive' check to freight_charges. NOT VALID makes it enforce new writes right away, and a later VALIDATE confirms the existing rows without freezing the table.
Simple terms
Adding a CHECK constraint the normal way makes PostgreSQL scan the entire table under a lock to prove the existing rows already comply. Marking it NOT VALID skips that scan and starts enforcing the rule on new writes immediately, so bad inserts fail from that moment on. You then run VALIDATE as a separate, non-blocking step to confirm the old rows, spreading the work out instead of freezing the table.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Schema migrations runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.