po 23. 10. 2023 v 19:34 odesÃlatel Tom Lane <t...@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. > > Thoughts? > > regards, tom lane > > >
