Delayed validation of SET NOT NULL constraint for new column
As discussed in #38060 (closed):
The proposal here is to change how a NOT NULL
constraint on a column added with add_column_with_default... allow_nulls: false
is handled.
Currently, we simply run this after having fill the column:
ALTER TABLE 'ci_build_needs' ALTER COLUMN 'artifacts' SET NOT NULL
This has two problems:
- It requires an exclusive lock on the table, possibly interfering with traffic
- The validation can take an extended period of time (while holding the lock)
The proposal from @gerardo.herzig in the original issue #38060 (closed) is:
ALTER TABLE ci_build_needs ADD CONSTRAINT ci_builds_needs_artifacts_not_null
CHECK (artifacts IS NOT NULL)
NOT VALID;
and then
ALTER TABLE ci_builds_needs VALIDATE CONSTRAINT ci_builds_needs_artifacts_not_null;
This separates the change of the table (which requires the exclusive lock) from the validation of the constraint (which only needs a SHARE UPDATE EXCLUSIVE
type of lock.
The benefit is that we don't hold the exclusive lock for an extended period of time (but we still need it, to add the constraint in the first place).