Re: PostgreSQL domains and NOT NULL constraint

I wrote:
> Isaac Morland <isaac.morland@gmail.com> writes:
>> If we decide we do want "CHECK (VALUE NOT NULL)" to work, then I wonder if
>> we could pass NULL to the constraint at CREATE DOMAIN time, and if it
>> returns FALSE, do exactly what we would have done (set pg_type.typnotnull)
>> if an actual NOT NULL clause had been specified?

> Maybe, but then ALTER DOMAIN would have to be prepared to update that
> flag when adding or dropping constraints.  Perhaps that's better than
> checking on-the-fly during DML commands, though.

After further thought I like that idea a lot, but we can't simply
overwrite pg_type.typnotnull without losing track of whether the user
had given a bare NOT NULL constraint.  Instead I think the details
should be like this:

1. Add a bool column "connotnull" (or some such name) to pg_constraint.
Set this to true when the constraint is a domain CHECK constraint that
returns FALSE for NULL input.  (In future we could maintain the flag
for table CHECK constraints too, perhaps, but I don't see value in
that right now.)  This requires assuming that the constraint is
immutable (which we assume already) and that it's okay to evaluate it
on a NULL immediately during CREATE DOMAIN or ALTER DOMAIN ADD
CONSTRAINT.  It seems possible that that could fail, but only with
rather questionable choices of constraints.

2. INSERT/UPDATE enforce not-nullness if pg_type.typnotnull is set
or there is any domain constraint with pg_constraint.connotnull
set.  This still requires thumbing through the constraints at
query start, but the check is cheaper and a good deal more bulletproof
than my previous suggestion of a purely-syntactic check.

We could make query start still cheaper by adding another pg_type
column that is the OR of the associated constraints' connotnull
flags, but I suspect it's not worth the trouble.  The typcache
can probably maintain that info with epsilon extra cost.

A variant approach could be to omit the catalog changes and have
this state be tracked entirely by the typcache.  That'd result in
rather more trial evaluations of the domain constraints on NULLs,
but it would have the advantage of not requiring any constraint
evaluations to occur during CREATE/ALTER DOMAIN, only during startup
of a query that's likely to evaluate them anyway.  That'd reduce
the odds of breaking things thanks to search_path dependencies
and suchlike.

            regards, tom lane