public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Herrera <[email protected]>
To: jian he <[email protected]>
Cc: Chao Li <[email protected]>
Cc: L. pgsql-hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Subject: Re: Fix bug of CHECK constraint enforceability recursion
Date: Thu, 28 May 2026 10:55:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ@mail.gmail.com>

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/[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Fix bug of CHECK constraint enforceability recursion
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox