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