Re: NOT Null constraint on foreign table not working

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> I believe that a column of a foreign table should be NOT NULL only if
> it is guaranteed that it cannot contain NULL values. Doesn't the planner
> rely on that?

The planner does expect that constraints tell the truth. I don't remember
how significant a false NOT NULL constraint might be, but certainly false
CHECK constraints can give rise to incorrect plans.

> But PostgreSQL cannot guarantee that, that has to happen on the remote side
> (or in the FDW). I think that it is best that an error for a constraint
> violation is thrown by the same entity that guarantees that the constraint
> is respected.

A point worth making here is that it's already implicit in the contract
that the CREATE FOREIGN TABLE command accurately represents what the
far-side table is. If you get the column datatypes wrong, for example,
it's possible to have subtle semantic bugs not all that different from
what will happen with an incorrectly-deduced plan. And we don't make
any attempt to slap your wrist for that. So I don't see that there's
anything fundamentally wrong with the position that any NOT NULL or CHECK
constraints attached to a foreign table must be accurate reflections of
constraints that exist on the far side, rather than something we should
enforce locally. (Note that this argument applies to FDWs for remote SQL
servers, but not necessarily for FDWs for non-SQL data sources, where
conceivably the CREATE FOREIGN TABLE command actually is itself the
authoritative truth. Such an FDW would then be responsible for enforcing
constraints.)

I agree though that we've failed miserably to explain this in the docs.

regards, tom lane