Horton

Re: NOT ENFORCED constraint feature

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
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-03T12:19:30Z
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 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/