Re: PostgreSQL domains and NOT NULL constraint

On Mon, 23 Oct 2023, 19:34 Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I wrote:
> > Given the exception the spec makes for CAST, I wonder if we shouldn't
> > just say "NULL is a valid value of every domain type, as well as every
> > base type. If you don't like it, too bad; write a separate NOT NULL
> > constraint for your table column."
>
> After ruminating on this for awhile, here's a straw-man proposal:
>
> 1. Domains are data types, with the proviso that NULL is always
> a valid value no matter what the domain constraints might say.
> Implementation-wise, this'd just require that CoerceToDomain
> immediately return any null input without checking the constraints.
> This has two big attractions:

Agreed.

> 2. In INSERT and UPDATE queries, thumb through the constraints of
> any domain-typed target columns to see if any of them are NOT NULL
> or CHECK(VALUE IS NOT NULL). If so, act as though there's a table
> NOT NULL constraint on that column.

How does this work w.r.t. concurrently created tables that contain the
domain? Right now, you can do something along the lines of the
following due to a lack of locking on domains for new columns/tables
that use said domain, and I believe that this is the main source of
domain constraint violations:

CREATE DOMAIN mydomain text;
CREATE TABLE c (d mydomain);

S1: BEGIN; INSERT INTO c VALUES (''); CREATE TABLE t (d mydomain);
INSERT INTO t VALUES (NULL);

S2: BEGIN; ALTER DOMAIN mydomain SET NOT NULL;
-- waits for S1 to release lock on c

S1: COMMIT;
-- S2's ALTER DOMAIN gets unblocked and succeeds, despite the NULL
value in "t" because that table is invisible to the transaction of
ALTER DOMAIN.

So my base question is, should we then require e.g. SHARE locks on
types that depend on domains when we do DDL that depends on the type,
and SHARE UPDATE EXCLUSIVE when we modify the type?

> The idea of point #2 is to have a cheap check that 99% satisfies
> what the spec says about not-null constraints on domains. If we
> don't do #2, I think we have to fully recheck all the domain's
> constraints during column assignment. I find that ugly as well
> as expensive performance-wise. It does mean that if you have
> some domain constraint that would act to reject NULLs, but it's
> spelled in some weird way, it won't reject NULLs. I don't find
> that possibility compelling enough to justify the performance hit
> of recomputing every constraint just in case it acts like that.

Makes sense.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)