Re: Proposal : composite type not null constraints

Wesley Massuda <wesley(dot)massuda(at)gmail(dot)com> writes:
> I would like to propose extending composite types with constraints.

I'm not convinced we should go there, *particularly* not for not-null
constraints. Data-type-related not-null constraints are broken by design,
because they are fundamentally inconsistent with outer joins. Here's an
example:

regression=# create domain nnint as int check (value is not null);
CREATE DOMAIN
regression=# create table tt (id int, nn nnint);
CREATE TABLE
regression=# insert into tt values (1,1), (2,1);
INSERT 0 2
regression=# select * from tt a left join tt b on a.id = b.nn;
id | nn | id | nn
----+----+----+----
1 | 1 | 1 | 1
1 | 1 | 2 | 1
2 | 1 | |
(3 rows)

We have here a column that claims to be of type nnint but contains
nulls.

The only really good solution to this problem for domains, IMO,
is to consider that the type of the join output column is changed
to the domain's base type, so that there's no relevant constraint
for it to violate. We don't do that at present, but if anyone got
really bent out of shape about this behavior, I'd tell them to
submit a patch that does that.

However, if we allow constraints on elements of a composite type,
there's no similar "out" available to describe values that look
like the type but don't satisfy its constraints. That's a hard
place that I don't want to get wedged into.

There are other implementation problems that you'd fall foul of
as well, for instance that plpgsql lacks any way to initialize
rowtype variables to non-null. Those are probably surmountable
with enough work, but the outer join problem is built into SQL.

regards, tom lane