Re: PostgreSQL domains and NOT NULL constraint

Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> Regardless of what the spec may or may not say about v1.d, it still
> remains that nulls should not be allowed in a *base table* if the domain
> says nulls are not allowed. Not mentioned in this thread but the
> constraints are also applied when CASTing to the domain.

Hmph. The really basic problem here, I think, is that the spec
wants to claim that a domain is a data type, but then it backs
off and limits where the domain's constraints need to hold.
That's fundamentally inconsistent. It's like claiming that
'foobarbaz' is a valid value of type numeric as long as it's
only in flight within a query and you haven't tried to store it
into a table.

Practical problems with this include:

* If a function declares its argument as being of a domain type,
can it expect that the passed value obeys the constraints?

* If a function declares its result as being of a domain type,
is it required to return a result that obeys the constraints?
(This has particular force for RETURNS NULL ON NULL INPUT
functions, for which we just automatically return NULL given
a NULL input without any consideration of whether the result
type nominally prohibits that.)

* If a plpgsql function has a variable that is declared to be of
domain type, do we enforce the domain's constraints when assigning?

* If a composite type has a column of a domain type, do we enforce
the domain's constraints when assigning or casting to that?

AFAICS, the spec's position leaves all of these as judgment calls,
or else you might claim that none of the above cases are even allowed
to be declared per spec. I don't find either of those satisfactory,
so I reiterate my position that the committee hasn't thought this
through.

> As you know, I am more than happy to (try to) amend the spec where
> needed, but Erki's complaint of a null value being allowed in a base
> table is clearly a bug in our implementation regardless of what we do
> with views.

I agree it's not a good behavior, but I still say it's traceable
to schizophenia in the spec. If the result of a sub-select is
nominally of a domain type, we should not have to recheck the
domain constraints in order to assign it to a domain-typed target.
If it's not nominally of a domain type, please cite chapter and
verse that says it isn't.

regards, tom lane