Thread

Re: Fix bug of CHECK constraint enforceability recursion

Chao Li <li.evan.chao@gmail.com> — 2026-05-28T22:56:22Z

> On May 28, 2026, at 16:55, Álvaro Herrera <alvherre@kurilemu.de> wrote:
> 
> 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

Thank Jian for pointing out the doc.

Thank Álvaro for guiding the direction. 

I just tried to update the doc in v3-0002, while 0001 is unchanged.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/