Re: PostgreSQL domains and NOT NULL constraint

Hello

Equaling a domain with a type is really confusing because why, for instance, in this case the following is possible without defining any additional operators.

CREATE DOMAIN d_name VARCHAR(50) NOT NULL;
CREATE DOMAIN d_description VARCHAR(1000) NOT NULL;
CREATE TABLE x(name d_name, description d_description);
SELECT *
FROM x
WHERE name=description;

Isn't it so that domains are not types and for this reason there are separate CREATE DOMAIN and CREATE TYPE statements?!

In my opinion the Notes section of CREATE DOMAIN documentation should offer better examples. The two examples that I provided in my demonstration seemed very far fetched and artificial. Frankly, I have difficulties in imagining why someone would like to write statements like that in a production environment and how the proper enforcement of NOT NULL constraints of domains could break things.

Lets say I have a column that I have declared mandatory by using a domain, but somehow I have added NULLs to the column, and if it is not possible any more, then things break down.

If I want to permit NULLs, then ALTER DOMAIN d DROP NOT NULL; will fix it with one stroke. If I do not want to permit NULLs but I have registered NULLs, then this is a data quality issue that has to be addressed.

Currently there is a feature (NOT NULL of domain) that the documentation explicitly suggests not to use. Isn't it in this case better to remove this feature completely?! If this would break something, then it would mean that systems actually rely on this constraint.

Best regards
Erki Eessaar

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, October 13, 2023 08:37
To: Vik Fearing <vik(at)postgresfriends(dot)org>
Cc: Erki Eessaar <erki(dot)eessaar(at)taltech(dot)ee>; pgsql-hackers(at)lists(dot)postgresql(dot)org <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: 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