Re: ALTER DOMAIN ADD NOT NULL NOT VALID

On 2025/5/21 18:44, jian he wrote:
> hi.
> 
> attached patch is for $subject implementation
> 
> per https://www.postgresql.org/docs/current/sql-alterdomain.html
> """
> Although ALTER DOMAIN ADD CONSTRAINT attempts to verify that existing stored
> data satisfies the new constraint, this check is not bulletproof, because the
> command cannot “see” table rows that are newly inserted or updated and not yet
> committed. If there is a hazard that concurrent operations might insert bad
> data, the way to proceed is to add the constraint using the NOT VALID option,
> commit that command, wait until all transactions started before that commit have
> finished, and then issue ALTER DOMAIN VALIDATE CONSTRAINT to search for data
> violating the constraint.
> """
> 
> Obviously, the above behavior can also happen to not-null constraints.
> add NOT NULL NOT VALID is good for validation invalid data too.
> 
> the not valid information is displayed at column "Nullable"
> for example:
> 
> \dD things
>                                        List of domains
>   Schema |  Name  |  Type   | Collation |      Nullable      | Default
> |       Check
> --------+--------+---------+-----------+--------------------+---------+--------------------
>   public | things | integer |           | not null not valid |
> | CHECK (VALUE < 11)

It makes sense to support the "NOT NULL NOT VALID" option.

The two if statements in the AlterDomainNotNull() should be adjusted.

if (typTup->typnotnull == notNull && !notNull)
==>
if (!notNull && !typTup->typnotnull)


if (typTup->typnotnull == notNull && notNull)
==>
if (notNull && typTup->typnotnull)

--
Quan Zongliang