po 23. 10. 2023 v 19:34 odesílatel Tom Lane <tgl@sss.pgh.pa.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.
