Re: PostgreSQL domains and NOT NULL constraint - Mailing list pgsql-hackers

I wrote:
> Given the exception the spec makes for CAST, I wonder if we shouldn't
> just say "NULL is a valid value of every domain type, as well as every
> base type.  If you don't like it, too bad; write a separate NOT NULL
> constraint for your table column."

After ruminating on this for awhile, here's a straw-man proposal:

1. Domains are data types, with the proviso that NULL is always
a valid value no matter what the domain constraints might say.
Implementation-wise, this'd just require that CoerceToDomain
immediately return any null input without checking the constraints.
This has two big attractions:

(1A) It satisfies the plain language of the SQL spec about how
CAST to a domain type behaves.

(1B) It legitimizes our behavior of allowing nullable outer join
columns, sub-SELECT outputs, etc to be considered to be of the
source column's domain type and not just the base type.

2. In INSERT and UPDATE queries, thumb through the constraints of
any domain-typed target columns to see if any of them are NOT NULL
or CHECK(VALUE IS NOT NULL).  If so, act as though there's a table
NOT NULL constraint on that column.

The idea of point #2 is to have a cheap check that 99% satisfies
what the spec says about not-null constraints on domains.  If we
don't do #2, I think we have to fully recheck all the domain's
constraints during column assignment.  I find that ugly as well
as expensive performance-wise.  It does mean that if you have
some domain constraint that would act to reject NULLs, but it's
spelled in some weird way, it won't reject NULLs.  I don't find
that possibility compelling enough to justify the performance hit
of recomputing every constraint just in case it acts like that.

3. Left unsaid here is whether we should treat assignments to,
e.g., plpgsql variables as acting like assignments to table
columns.  I'm inclined not to, because

(3A) I'm lazy, and I'm also worried that we'd miss places where
this arguably should happen.

(3B) I don't think the SQL spec contemplates any such thing
happening.

(3C) Not doing that means we have a pretty consistent view of
what the semantics are for "values in flight" within a query.
Anything that's not stored in a table is "in flight" and so
can be NULL.

(3D) Again, if you don't like it, there's already ways to attach
a separate NOT NULL constraint to plpgsql variables.


Documenting this in an intelligible fashion might be tricky,
but explaining the exact spec-mandated behavior wouldn't be
much fun either.

Thoughts?

            regards, tom lane