Horton

Re: NOT ENFORCED constraint feature

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Amul Sul <sulamul@gmail.com>, jian he <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-02-04T13:56:52Z
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 03.02.25 13:19, Alvaro Herrera 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.

The database system could use the presence of a not enforced constraint 
for selectivity estimation, for example.