Re: PostgreSQL domains and NOT NULL constraint

On 10/13/23 06:37, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.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.


I don't think that is an accurate depiction of domains.

First of all, I am not seeing where it says that a domain is a data 
type.  It allows domains to be used in some places where a data type is 
used, but that is not equivalent to a domain /being/ a data type.

Section 4.14 says, "A domain is a set of permissible values." and then 
goes on to say that that is a combination of a predefined type and zero 
or more search conditions.  It can also have a default value, but it 
does not seem relevant to talk about that in this discussion.

Section 4.25.4, "Domain constraints" has this to say (emphasis mine):

- A domain constraint is satisfied by SQL-data *if and only if*, for 
every *table* T that has a column named C based on that domain, the 
applicable <search condition> recorded in the appropriate domain 
constraint usage evaluates to True or Unknown.

- A domain constraint is satisfied by the result of a <cast 
specification> if and only if the specified template <search condition>, 
with each occurrence of the <general value specification> VALUE replaced 
by that result, evaluates to True or Unknown.

This tells me that the constraints should only be checked at those two 
points.

Secondly, why are you so concerned about outer join nulls here and not 
for any other column marked NOT NULL?


> 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.


It's like claiming that null 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 with that column marked NOT NULL.


> 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?


Routines are not allowed to have domains in their parameters or result 
types.

I am all for PostgreSQL expanding the spec wherever we can, but in the 
above cases we have to define things ourselves.


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


I don't see that a composite type is able to have a member of a domain. 
As for what PostgreSQL should do in this case, my opinion is "yes".


> 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.


My claim is indeed that these cases are not allowed per-spec and 
therefore the spec doesn't *need* to think about them.  We do.


>> 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.


Well, yes, we should.

Allowing a null to be stored in a column where the user has specified 
NOT NULL, no matter how the user did that, is unacceptable and I am 
frankly surprised that you are defending it.


> If it's not nominally of a domain type, please cite chapter and
> verse that says it isn't.

I don't see anything for or against this, I just see that the domain 
constraints are only checked on storage or casting.

And therefore, I think with these definitions:

CREATE DOMAIN dom AS INTEGER CHECK (VALUE >= 0);
CREATE TABLE t (d dom);
INSERT INTO t (d) VALUES (1);

this should be valid according to the spec:

SELECT -d FROM t;

and this should error:

SELECT CAST(-d AS dom) FROM t;
-- 
Vik Fearing