Re: PostgreSQL domains and NOT NULL constraint

po 23. 10. 2023 v 19:34 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> 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:
>
> (1A) It satisfies the plain language of the SQL spec about how
> CAST to a domain type behaves.
>
> (1B) It legitimizes our behavior of allowing nullable outer join
> columns, sub-SELECT outputs, etc to be considered to be of the
> source column's domain type and not just the base type.
>
> 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.
>

+1

I think only this interpretation makes sense.

> 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.
>
> 3. Left unsaid here is whether we should treat assignments to,
> e.g., plpgsql variables as acting like assignments to table
> columns. I'm inclined not to, because
>
> (3A) I'm lazy, and I'm also worried that we'd miss places where
> this arguably should happen.
>
> (3B) I don't think the SQL spec contemplates any such thing
> happening.
>
> (3C) Not doing that means we have a pretty consistent view of
> what the semantics are for "values in flight" within a query.
> Anything that's not stored in a table is "in flight" and so
> can be NULL.
>
> (3D) Again, if you don't like it, there's already ways to attach
> a separate NOT NULL constraint to plpgsql variables.
>

Although I don't fully like it, I think ignoring the NOT NULL constraint
for plpgsql's variables is a better way, then apply it. Elsewhere there can
be issues related to variable's initialization.

Regards

Pavel

>
>
> Documenting this in an intelligible fashion might be tricky,
> but explaining the exact spec-mandated behavior wouldn't be
> much fun either.
>
> Thoughts?
>
> regards, tom lane
>
>
>