Horton

Re: NOT ENFORCED constraint feature

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

View thread
From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: Amul Sul <sulamul@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, jian he <jian.universality@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Joel Jacobson <joel@compiler.org>, Alexandra Wang <alexandra.wang.oss@gmail.com>, Suraj Kharage <suraj.kharage@enterprisedb.com>
Date: 2025-03-27T14:15:25Z
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-Mar-27, Amul Sul wrote:

> On Thu, Mar 27, 2025 at 6:28 PM Peter Eisentraut <peter@eisentraut.org> wrote:

> > That said, is there a simpler way?  Patch 0003 appears to add a lot of
> > complexity.  Could we make this simpler by saying, if you have otherwise
> > matching constraints with different enforceability, make this an error.
> > Then users can themselves adjust the enforceability how they want to
> > make it match.
> 
> We can simply discard this patch, as it still reflects the correct
> behavior. It creates a new constraint without affecting the existing
> constraint with differing enforceability on the child. I noticed
> similar behavior with deferrability -- when it differs, the
> constraints are not merged, and a new constraint is created on the
> child. Let me know your thoughts so I can avoid squashing patch 0006.

I didn't read that patch and I don't know what level of complexity we're
talking about, but the idea of creating a second constraint beside an
existing one itches me.  I'm pretty certain most users would rather not
end up with redundant constraints that only differ in enforceability or
whatever other properties.  I failed to realize that this was happening
when adding FKs on partitioned tables, and I now think it was a mistake.
(As I said in some previous thread, I'd rather have this kind of
situation raise an error so that the user can do something about it,
rather than silently moving ahead with a worse solution like creating a
redundant constraint.)

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL! PostgreSQL was
amazing when I first started using it at 7.2, and I'm continually astounded by
learning new features and techniques made available by the continuing work of
the development team."
Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php