Re: NOT ENFORCED constraint feature

On Mon, Feb 3, 2025 at 5:55 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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