Horton

Re: NOT ENFORCED constraint feature

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: Amul Sul <sulamul@gmail.com>
Cc: jian he <jian.universality@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>
Date: 2025-01-28T16:17:07Z
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 28.01.25 11:58, Amul Sul wrote:
>> This behavior is not correct:
>>
>> +-- Changing it back to ENFORCED will leave the constraint in the NOT
>> VALID state
>> +ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
>> +-- Which needs to be explicitly validated.
>> +ALTER TABLE FKTABLE VALIDATE CONSTRAINT fktable_ftest1_fkey;
>>
>> Setting the constraint to enforced should enforce it immediately.  This
>> SQL statement is covered by the SQL standard.  Also, I think it's a
>> better user experience if you don't require two steps.
>>
> Let me clarify: the constraint will be enforced for new inserts and
> updates, but it won't be validated against existing data, so those
> will remain marked as invalid.

Yes, I understand, but that is the not the correct behavior of this 
command per SQL standard.