Horton

Re: NOT ENFORCED constraint feature

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Amul Sul <sulamul@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, jian he <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-02-04T04:39:29Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add support for NOT ENFORCED in foreign key constraints

  2. Expand test a bit

  3. refactor: Pass relation OID instead of Relation to createForeignKeyCheckTriggers()

  4. refactor: Split ATExecAlterConstraintInternal()

  5. refactor: Move some code that updates pg_constraint to a separate function

  6. Move RemoveInheritedConstraint() call slightly earlier

  7. refactor: Split tryAttachPartitionForeignKey()

  8. refactor: re-add ATExecAlterChildConstr()

  9. Add ATAlterConstraint struct for ALTER .. CONSTRAINT

  10. refactor: split ATExecAlterConstrRecurse()

  11. Add support for NOT ENFORCED in CHECK constraints

On Mon, Feb 3, 2025 at 5:55 PM Alvaro Herrera <alvherre@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