public inbox for [email protected]  

help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Chao Li <[email protected]>
Cc: Álvaro Herrera <[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:31:35 +0800
Message-ID: <CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CACJufxGRkCSqqRwd+3MMVFbosKXHv=Jc+KyGK+NM3YAHN7eu+g@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CACJufxGxY=1mxcNz8-bjp6R+ZoCRE3O7DYt2ZqObv-Le+aMOqw@mail.gmail.com>
	<[email protected]>

On Wed, May 27, 2026 at 2:20 PM Chao Li <[email protected]> wrote:
>
> 4. It cannot handle some complicated inheritance hierarchies. For example, the following test passes with your v1:
> ```
> evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=#
> evantest=# CREATE TABLE ch () INHERITS (p1, p2);
> NOTICE:  merging multiple inherited definitions of column "a"
> CREATE TABLE
> evantest=# ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
> ALTER TABLE
> ```
>
> I originally thought this should fail, but it now changes ch.c to NOT ENFORCED, so it breaks the rule because its parent p2 is still ENFORCED:
> ```
> evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
> evantest-# FROM pg_constraint WHERE conname = 'c';
>  conrelid | conname | conenforced | coninhcount | conislocal
> ----------+---------+-------------+-------------+------------
>  p1       | c       | f           |           0 | t
>  p2       | c       | t           |           0 | t
>  ch       | c       | f           |           2 | f
> (3 rows)
> ```
>
> Then I realized that the initial CREATE TABLE case passes:
> ```
> evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) NOT ENFORCED);
> CREATE TABLE
> evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
> CREATE TABLE
> evantest=# CREATE TABLE ch () INHERITS (p1, p2);
> NOTICE:  merging multiple inherited definitions of column "a"
> CREATE TABLE
> evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
> evantest-# FROM pg_constraint WHERE conname = ‘c';
>  conrelid | conname | conenforced | coninhcount | conislocal
> ----------+---------+-------------+-------------+------------
>  ch       | c       | t           |           2 | f
>  p1       | c       | f           |           0 | t
>  p2       | c       | t           |           0 | t
> (3 rows)
> ```
>
> When the two parents have different enforceability, the stricter one is applied to the child. So I think the test above in item 4 should also perform similar merge logic rather than fail. This seems to uncover a new issue in the original feature patch.
>

> For the fix, my design is:
>
> * Directly reject changing an inherited child CHECK constraint to NOT ENFORCED if an equivalent parent constraint remains ENFORCED.
> * Changing a child to ENFORCED is allowed.
> * During recursing, if a child also inherits an equivalent ENFORCED constraint from another parent outside the current ALTER, the child keeps the stricter ENFORCED state.
>
> Please see my implementation in the attached v2 patch.

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:
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
<<>>


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).
<<>>



--
jian
https://www.enterprisedb.com/

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: <CACJufxFwJH5K1jgL1ryckYX8gxp1sfqz_2ypey1sYtWZ5u45oQ@mail.gmail.com>

* 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