On Mon, Feb 3, 2025 at 5:55 PM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > 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.
What's the use of NOT ENFORCED constraint then? To me NOT ENFORCED looks similar to informational constraints of IBM DB2 [1]. It seems that they have TRUSTED/NOT TRUSTED similar to PostgreSQL's VALID/NOT VALID [2]. [1] https://www.ibm.com/docs/en/db2/11.1?topic=constraints-informational [2] https://www.ibm.com/docs/en/db2/11.1?topic=statements-create-table -- Best Wishes, Ashutosh Bapat
