Are NOT NULL constraint recognized by Teiid prior to an atomic insert attempt?

[virtualdatabase]

If a column has the "NULLABLE" attribute set as NO_NULLS shouldn't TeiiD recognize such and not attempt an insert if NULL is provided for said column?

[shawkins]

Values are statically checked and enforced at a virtual level, but just like other physical constraints it's assumed the source will provide final checks.  Can you elaborate if you are seeing a specific situation?

[virtualdatabase]

I have a postgreSQL table fronted by a model where the model clearly has NO_NULLS for the NULL-ABLE attribute of a column

I did an insert and got a rather ambiguous error which turns out is some issue with the Postgres driver but I  I thought further that the query should have never gotten that far (much like an AccessPattern).

Is this incorrect thinking or a bug?

[shawkins]

> Is this incorrect thinking or a bug?

It's mostly ambiguous.  Again most physical model constraints are not rechecked by Teiid, as we assume that will be the responsibility of the source.  An access pattern is purely a virtualization construct, so it is always checked.

We do check the values for nullabilty at validation time, but not later on if the value is something that is derived - is the insert value in your case a subquery, bind value, etc.?  There can also be cases that we cannot or at least have difficulty / introduce overhead to check, such as when the insert uses a pushdown query expression or the insert value comes from a must pushdown function and the source does not support values / select without from.