Re: Catalog domain not-null constraints

On Fri, 22 Mar 2024 at 08:28, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Thu, Mar 21, 2024 at 7:23 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> >
> > Hmm. CREATE DOMAIN uses column constraint syntax, but ALTER DOMAIN uses
> > table constraint syntax. Attached is a patch to try to sort this out.
>
> also you should also change src/backend/utils/adt/ruleutils.c?
>
> src6=# \dD
> List of domains
> Schema | Name | Type | Collation | Nullable | Default |
> Check
> --------+-------------+---------+-----------+----------+---------+----------------------------------
> public | domain_test | integer | | not null | |
> CHECK (VALUE > 0) NOT NULL VALUE
> (1 row)
>
> probably change to CHECK (VALUE IS NOT NULL)

I'd say it should just output "NOT NULL", since that's the input
syntax that created the constraint. But then again, why display NOT
NULL constraints in that column at all, when there's a separate
"Nullable" column?

Also (not this patch's fault), psql doesn't seem to offer a way to
display domain constraint names -- something you need to know to drop
or alter them. Perhaps \dD+ could be made to do that?

+ The syntax <literal>NOT NULL</literal> in this command is a
+ <productname>PostgreSQL</productname> extension. (A standard-conforming
+ way to write the same would be <literal>CHECK (VALUE IS NOT
+ NULL)</literal>. However, per <xref linkend="sql-createdomain-notes"/>,
+ such constraints a best avoided in practice anyway.) The
+ <literal>NULL</literal> <quote>constraint</quote> is a
+ <productname>PostgreSQL</productname> extension (see also <xref
+ linkend="sql-createtable-compatibility"/>).

I didn't verify this, but I thought that according to the SQL
standard, only non-NULL values should be passed to CHECK constraints,
so there is no standard-conforming way to write a NOT NULL domain
constraint.

FWIW, I think NOT NULL domain constraints are a useful feature to
have, and I suspect that there are more people out there who use them
and like them, than who care what the SQL standard says. If so, I'm in
favour of allowing them to be named and managed in the same way as NOT
NULL table constraints.

+ processCASbits($5, @5, "CHECK",
+ NULL, NULL, &n->skip_validation,
+ &n->is_no_inherit, yyscanner);
+ n->initially_valid = !n->skip_validation;

+ /* no NOT VALID support yet */
+ processCASbits($3, @3, "NOT NULL",
+ NULL, NULL, NULL,
+ &n->is_no_inherit, yyscanner);
+ n->initially_valid = true;

NO INHERIT is allowed for domain constraints? What does that even mean?

There's something very wonky about this:

CREATE DOMAIN d1 AS int CHECK (value > 0) NO INHERIT; -- Rejected
ERROR: check constraints for domains cannot be marked NO INHERIT

CREATE DOMAIN d1 AS int;
ALTER DOMAIN d1 ADD CHECK (value > 0) NO INHERIT; -- Allowed

CREATE DOMAIN d2 AS int NOT NULL NO INHERIT; -- Now allowed (used to
syntax error)

CREATE DOMAIN d3 AS int;
ALTER DOMAIN d3 ADD NOT NULL NO INHERIT; -- Allowed

Presumably all of those should be rejected in the grammar.

Regards,
Dean