Re: NOT ENFORCED constraint feature

On 2025-Feb-03, Ashutosh Bapat wrote:

> ```
> If the
> constraint is <literal>NOT ENFORCED</literal>, the database system will
> not check the constraint. It is then up to the application code to
> ensure that the constraints are satisfied. The database system might
> still assume that the data actually satisfies the constraint for
> optimization decisions where this does not affect the correctness of the
> result.
> ```

IMO the third sentence should be removed because it is bogus. There's
no situation in which a not-enforced constraint can be used for any
query optimizations -- you cannot know if a constraint remains valid
after it's been turned NOT ENFORCED, because anyone could insert data
that violates it milliseconds after it stops being enforced. I think
the expectation that the application is going to correctly enforce the
constraint after it's told the database server not to enforce it, is
going to be problematic. As I recall, we already do this in FDWs for
instance and it's already a problem.

The second sentence is also somewhat bogus, but not as much, because it
doesn't have any implications for the database system. I think we
should simply say that no assumptions can be made about not enforced
constraints from the server side and that if the user wants to enforce
these at the application side, it's up to them to keep it all straight.

IMO if the patch is letting constraints that are marked NOT ENFORCED
continue to be used for query optimization, the patch is bogus and
should be fixed. For instance, I think CheckConstraintFetch() should
skip adding to TupleDesc->constr any constraints that are marked as not
enforced.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/