Feedback-ID: ie3de48e3:Fastmail
Date: Thu, 28 May 2026 10:55:45 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: jian he <jian.universality@gmail.com>
Cc: Chao Li <li.evan.chao@gmail.com>,
	"L. pgsql-hackers" <pgsql-hackers@lists.postgresql.org>,
 Andrew Dunstan <andrew@dunslane.net>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
Message-ID: <ahgAsvfSt3kbw88l@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ahgAsvfSt3kbw88l%40alvherre.pgsql>
Precedence: bulk

On 2026-May-28, jian he wrote:

> CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE ch () INHERITS (p1, p2);
> ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
> 
> The v2 patch marks check constraint c on table ch as ENFORCED, which
> seems to contradict the documentation's wording:

I think what v2 is doing in this case is correct.  The child's
constraint must preserve whatever the strictest of the inherited
constraints status is.  In this case, because the constraint on p2
remains ENFORCED, then it must remain ENFORCED in the child as well.
Changing it to NOT ENFORCED after the ALTER TABLE would be wrong.

> https://www.postgresql.org/docs/devel/ddl-inherit.html
> <<>>
> ALTER TABLE will propagate any changes in column data definitions and check
> constraints down the inheritance hierarchy. Again, dropping columns that are
> depended on by other tables is only possible when using the CASCADE option.
> ALTER TABLE follows the same rules for duplicate column merging and rejection
> that apply during CREATE TABLE
> <<>>

I think this text is a bit vague in that it isn't really considering
multiple inheritance -- it appears to be written with the perspective of
each child table having a single parent.

It may be a bit obsolete also; it talks about "check constraints" but we
also allow not-null constraints to have these kind of properties as
well (which we didn't when this was written).

> The wording (https://www.postgresql.org/docs/devel/ddl-inherit.html)
> below also discourages directly altering check constraints on child tables.
> <<>>
> A parent table cannot be dropped while any of its children remain. Neither can
> columns or check constraints of child tables be dropped or altered if they are
> inherited from any parent tables. If you wish to remove a table and all of its
> descendants, one easy way is to drop the parent table with the CASCADE option
> (see Section 5.17).
> <<>>

Hmm, I think this text is borderline obsolete, in the sense that we know
allow changing some properties of some constraints in child tables.
I'm not really sure to what extent it is useful to make it more explicit
about that.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"You're _really_ hosed if the person doing the hiring doesn't understand
relational systems: you end up with a whole raft of programmers, none of
whom has had a Date with the clue stick."              (Andrew Sullivan)
https://postgr.es/m/20050809113420.GD2768@phlogiston.dyndns.org