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/
