Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wRkei-002ej9-0N for pgsql-hackers@arkaria.postgresql.org; Tue, 26 May 2026 05:48:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wRkef-0036Dt-1N for pgsql-hackers@arkaria.postgresql.org; Tue, 26 May 2026 05:48:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wRkee-0036Dl-2s for pgsql-hackers@lists.postgresql.org; Tue, 26 May 2026 05:48:42 +0000 Received: from mail-dy1-x1331.google.com ([2607:f8b0:4864:20::1331]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wRked-00000000oAd-2yur for pgsql-hackers@lists.postgresql.org; Tue, 26 May 2026 05:48:40 +0000 Received: by mail-dy1-x1331.google.com with SMTP id 5a478bee46e88-2f0ad52830cso12932660eec.1 for ; Mon, 25 May 2026 22:48:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1779774518; x=1780379318; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=BYiD4LMj7tq4fF3AMzq+XoP1JVEklykfIyPkgcu1Z8M=; b=CS8EWFf3yPpQUdsv0N9tlzEs0y3P8Qd2GIYFL5SCTF6gwUkP3fJWDmHhDNvKH1wWI3 qadu1qeyWkJFCLkzXSIaUBSpnYpbiWLVD0j3GvdxWa8GXkLZXx7ziT4fpaloKmv99083 B+PI9KP5hXjAMY1IyqXgIcgED95QP58AAORYuwVtWMDc5GkixM94JTuqdDlXi/XeQZuv 99lO8/0qc+7zSH8eG0wWt3OT+Rxq0gxmTr837waGc/zpAWZfRyeOz7pBQTbmQ550BTKb 24H8gwSzKj/EibhrSEdR6Ndodmg6ez6jDuya9MdRgjlxvDhK1QMUF7Hj6wHoa4t855At bfYA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1779774518; x=1780379318; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=BYiD4LMj7tq4fF3AMzq+XoP1JVEklykfIyPkgcu1Z8M=; b=oMBtkzsKLPciOm0VuKumB1uBs2TOzCiBoA5ewZxFzPGXCA3allE4tM7rLKEduzj9VC qPSlcqVa0haPuoA2HHjMTPa0pAThOhYNIoi/UukWUTuTxyHUel79r7bTLkxjD90A2Zmj En/DcqrIzgcSsMWKaKREZ4jGyItiulobi+cFV8TFqGTg3sfHS0SjWR/wB1qdQXGMaiv7 xPoSByHVD7g+ZrkeiFlkZtexXaz+iwnaMtu/lrBPAKgHaYDumcGoDSBbXYcwRIaC8GFm bitccf0nkqlB/8xYj/E5Uya+0qHSfxgpQ8TUkzAnTEQPglgGmxoyIG8djXQ9tQqEOQsz OtHQ== X-Gm-Message-State: AOJu0Yy5zVsJr+R1g9m/D/67Pvefobngn6oNj+jDBMB35VTfpbEoSyih aM1H6kKhXAj6y4r0UXdyrVnMzUzqZNZSrY9b5NpAZRtvw4FOj1AK+3Yk1MkkN1Al X-Gm-Gg: Acq92OFa0whKjy7AEQyZzEazXfgFj/PiJA+LC2SZNipmEdWboV7zX7yMvg+uWDC/wd3 TrarhoA2+N6OrOhoUInW0+zOZf+oOK86BlR4eFWb4B6GNVdAGC+DlLFark4vq0+n8LXf1/ULDh6 RnmeamAAJdF9cYnxW1+38hFX0Qs9qQyYI40tTfkEVpe5gXNQKlKqQzAQcDPeE0a9IFMjD8hLnyt OJCsAH/ONQsrs/1shm/vHGHSTXc/oFfS9WnceWfkTstKJ6KeVAS51KHa3x2spmo2i7rpw3/ZzQ9 3EkeTECv9KiSnDJqgxYfJFRwMGfGZV5OZAexERogKa87ufqII7oui5Ykm7jx8hLVk85FbwNC76d BUaJx/6F3PLv3JwSI67/Hc3qEQzihp3xKVIKuXHV5zCAJPi95i66umNXZ6YhcKRGHDWNaGTpl1O L+acJ5cyt6XisvQC3TClPwdV6SoWS7NIreFUmwsiSY X-Received: by 2002:a05:7300:cb86:b0:2ea:b85c:153d with SMTP id 5a478bee46e88-304491db7e8mr7733668eec.27.1779774518285; Mon, 25 May 2026 22:48:38 -0700 (PDT) Received: from smtpclient.apple ([64.32.14.230]) by smtp.gmail.com with ESMTPSA id 5a478bee46e88-30452230dddsm12548506eec.17.2026.05.25.22.48.36 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 25 May 2026 22:48:37 -0700 (PDT) From: Chao Li Message-Id: <75584501-84F3-4F8D-A315-D5E37DEAEC10@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_5A33854C-1BAD-4B7A-B597-7DD1EB3A8835" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\)) Subject: Re: Fix bug of CHECK constraint enforceability recursion Date: Tue, 26 May 2026 13:48:02 +0800 In-Reply-To: Cc: Andrew Dunstan , jian he To: PostgreSQL Hackers References: X-Mailer: Apple Mail (2.3864.600.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5A33854C-1BAD-4B7A-B597-7DD1EB3A8835 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On May 26, 2026, at 11:51, Chao Li wrote: >=20 > Hi, >=20 > I just tested =E2=80=9CAdd support for altering CHECK constraint = enforceability=E2=80=9D and found an issue where recursion is not = handled properly. >=20 > Here is a repro with inheritance tables: > ``` > evantest=3D# create table p(a int constraint ck check (a > 0) = enforced); > CREATE TABLE > evantest=3D# create table c() inherits (p); > CREATE TABLE > evantest=3D# alter table c alter constraint ck not enforced; > ALTER TABLE > evantest=3D# insert into c values (-1); > INSERT 0 1 > evantest=3D# alter table p alter constraint ck enforced; > ALTER TABLE > evantest=3D# insert into c values (-2); > INSERT 0 1 > evantest=3D# select * from p; > a > ---- > -1 > -2 > (2 rows) > ``` >=20 > In this repro, the constraint on parent table p is already ENFORCED, = but the constraint on child table c was altered to NOT ENFORCED. So when = altering p to ENFORCED again, it didn't recurse to c. >=20 > The same problem can happen with partitioned tables as well: > ``` > evantest=3D# create table p (a int, constraint ck check (a > 0) = enforced) partition by range (a); > CREATE TABLE > evantest=3D# create table p1 partition of p for values from (-100) to = (100); > CREATE TABLE > evantest=3D# insert into p1 values (-1); > ERROR: new row for relation "p1" violates check constraint "ck" > DETAIL: Failing row contains (-1). > evantest=3D# alter table p1 alter constraint ck not enforced; > ALTER TABLE > evantest=3D# insert into p1 values (-1); > INSERT 0 1 > evantest=3D# alter table p alter constraint ck enforced; > ALTER TABLE > evantest=3D# insert into p1 values (-2); > INSERT 0 1 > evantest=3D# > evantest=3D# select * from p; > a > ---- > -1 > -2 > (2 rows) > ``` >=20 > For the solution, I think we should always recurse to descendant = tables unless the constraint is NO INHERIT, because both partitioned = tables and inheritance children can currently be altered to have = different enforceability. So we cannot rely on whether the parent = constraint itself was changed. >=20 > See the attached patch for details. I also added regress test cases = for the fix. >=20 > Best regards, > -- > Chao Li (Evan) > HighGo Software Co., Ltd. > https://www.highgo.com/ >=20 >=20 >=20 >=20 > Merged the doc change from [1] into this thread as they are for the same = feature. [1] https://postgr.es/m/711B1ED3-1781-4B6C-A573-B58AF20770E5@gmail.com Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_5A33854C-1BAD-4B7A-B597-7DD1EB3A8835 Content-Disposition: attachment; filename=v2-0001-Fix-CHECK-constraint-enforceability-recursion.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Fix-CHECK-constraint-enforceability-recursion.patch" Content-Transfer-Encoding: quoted-printable =46rom=20c28ecbe5ac850fb77c9f0b859c7d6a2b867cc4b9=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Tue,=2026=20May=202026=2011:03:15=20+0800=0ASubject:=20[PATCH=20= v2=201/2]=20Fix=20CHECK=20constraint=20enforceability=20recursion=0A=0A= ALTER=20TABLE=20...=20ALTER=20CONSTRAINT=20...=20ENFORCED=20skipped=20= recursion=20when=0Athe=20constraint=20on=20the=20target=20table=20was=20= already=20enforced.=20That=20assumed=0Adescendant=20CHECK=20constraints=20= could=20not=20have=20different=20enforceability,=20but=0Aboth=20regular=20= inheritance=20and=20some=20partition=20cases=20can=20have=20descendants=20= with=0Adifferent=20conenforced=20states.=0A=0ARecurse=20for=20= inheritable=20CHECK=20constraints=20regardless=20of=20whether=20the=20= target=0Aconstraint=20row=20itself=20changed,=20while=20still=20skipping=20= NO=20INHERIT=0Aconstraints.=20This=20ensures=20descendant=20constraints=20= are=20updated=20and=20validated=0Awhen=20needed.=0A=0AAdd=20regression=20= tests=20for=20both=20regular=20inheritance=20and=20partitioning:=20= change=20a=0Adescendant=20CHECK=20constraint=20to=20NOT=20ENFORCED=20= while=20the=20parent=20remains=20ENFORCED,=0Athen=20re-enforce=20the=20= parent=20and=20verify=20that=20the=20operation=20recurses=20to=20the=0A= descendant.=0A=0AAuthor:=20Chao=20Li=20=0AReviewed-by:=0A= Discussion:=20= https://postgr.es/m/E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com=0A---=0A= =20src/backend/commands/tablecmds.c=20=20=20=20=20=20=20=20=20=20|=2015=20= ++++++-------=0A=20src/test/regress/expected/constraints.out=20|=2026=20= +++++++++++++++++++++++=0A=20src/test/regress/expected/inherit.out=20=20=20= =20=20|=2024=20+++++++++++++++++++++=0A=20= src/test/regress/sql/constraints.sql=20=20=20=20=20=20|=2018=20= ++++++++++++++++=0A=20src/test/regress/sql/inherit.sql=20=20=20=20=20=20=20= =20=20=20|=2015=20+++++++++++++=0A=205=20files=20changed,=2090=20= insertions(+),=208=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/commands/tablecmds.c=20b/src/backend/commands/tablecmds.c=0A= index=201e0bacf85fc..05289207305=20100644=0A---=20= a/src/backend/commands/tablecmds.c=0A+++=20= b/src/backend/commands/tablecmds.c=0A@@=20-12700,14=20+12700,13=20@@=20= ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09}=0A=20=0A=20=09/*=0A-=09=20*=20= Note=20that=20we=20must=20recurse=20even=20when=20trying=20to=20change=20= a=20check=20constraint=0A-=09=20*=20to=20not=20enforced=20if=20it=20is=20= already=20not=20enforced,=20in=20case=20descendant=0A-=09=20*=20= constraints=20might=20be=20enforced=20and=20need=20to=20be=20changed=20= to=20not=20enforced.=0A-=09=20*=20Conversely,=20we=20should=20do=20= nothing=20if=20a=20constraint=20is=20being=20set=20to=0A-=09=20*=20= enforced=20and=20is=20already=20enforced,=20as=20descendant=20= constraints=20cannot=20be=0A-=09=20*=20different=20in=20that=20case.=0A-=09= =20*/=0A-=09if=20(!cmdcon->is_enforced=20||=20changed)=0A+=09=20*=20= Recurse=20for=20inheritable=20constraints=20even=20when=20this=20= constraint=20already=0A+=09=20*=20has=20the=20requested=20= enforceability.=20=20For=20both=20partitioning=20and=20regular=0A+=09=20= *=20inheritance,=20descendant=20constraints=20can=20have=20different=20= enforceability=0A+=09=20*=20and=20still=20need=20to=20be=20updated.=20=20= Only=20NO=20INHERIT=20constraints=20do=20not=0A+=09=20*=20recurse.=0A+=09= =20*/=0A+=09if=20(!currcon->connoinherit)=0A=20=09{=0A=20=09=09/*=0A=20=09= =09=20*=20If=20we're=20recursing,=20the=20parent=20has=20already=20done=20= this,=20so=20skip=20it.=0Adiff=20--git=20= a/src/test/regress/expected/constraints.out=20= b/src/test/regress/expected/constraints.out=0Aindex=20= e54fec7fb57..579882e75f6=20100644=0A---=20= a/src/test/regress/expected/constraints.out=0A+++=20= b/src/test/regress/expected/constraints.out=0A@@=20-468,6=20+468,32=20@@=20= select=20*=20from=20check_constraint_status;=0A=20=0A=20drop=20table=20= parted_ch;=0A=20drop=20view=20check_constraint_status;=0A+--=20an=20= already=20enforced=20partitioned=20parent=20must=20still=20recurse=20to=20= partitions=0A+create=20table=20parted_ch_recurse(=0A+=20=20a=20int,=0A+=20= =20constraint=20cc=20check=20(a=20>=200)=20enforced=0A+)=20partition=20= by=20range(a);=0A+create=20table=20parted_ch_recurse_1=20partition=20of=20= parted_ch_recurse=20for=20values=20from=20(-100)=20to=20(100);=0A+alter=20= table=20parted_ch_recurse_1=20alter=20constraint=20cc=20not=20enforced;=0A= +insert=20into=20parted_ch_recurse_1=20values(-1);=0A+alter=20table=20= parted_ch_recurse=20alter=20constraint=20cc=20enforced;=20--error=0A= +ERROR:=20=20check=20constraint=20"cc"=20of=20relation=20= "parted_ch_recurse_1"=20is=20violated=20by=20some=20row=0A+delete=20from=20= parted_ch_recurse_1=20where=20a=20=3D=20-1;=0A+alter=20table=20= parted_ch_recurse=20alter=20constraint=20cc=20enforced;=0A+select=20= conrelid::regclass,=20conenforced,=20convalidated=0A+from=20= pg_constraint=0A+where=20conname=20=3D=20'cc'=20and=20= conrelid::regclass::text=20like=20'parted_ch_recurse%'=0A+order=20by=20= conrelid::regclass::text;=0A+=20=20=20=20=20=20conrelid=20=20=20=20=20=20= =20|=20conenforced=20|=20convalidated=20=0A= +---------------------+-------------+--------------=0A+=20= parted_ch_recurse=20=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A= +=20parted_ch_recurse_1=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A= +(2=20rows)=0A+=0A+insert=20into=20parted_ch_recurse_1=20values(-1);=20= --error=0A+ERROR:=20=20new=20row=20for=20relation=20= "parted_ch_recurse_1"=20violates=20check=20constraint=20"cc"=0A+DETAIL:=20= =20Failing=20row=20contains=20(-1).=0A+drop=20table=20parted_ch_recurse;=0A= =20--=0A=20--=20Primary=20keys=0A=20--=0Adiff=20--git=20= a/src/test/regress/expected/inherit.out=20= b/src/test/regress/expected/inherit.out=0Aindex=20= 3d8e8d8afd2..05a1604d609=20100644=0A---=20= a/src/test/regress/expected/inherit.out=0A+++=20= b/src/test/regress/expected/inherit.out=0A@@=20-1479,6=20+1479,30=20@@=20= NOTICE:=20=20drop=20cascades=20to=203=20other=20objects=0A=20DETAIL:=20=20= drop=20cascades=20to=20table=20p1_c1=0A=20drop=20cascades=20to=20table=20= p1_c2=0A=20drop=20cascades=20to=20table=20p1_c3=0A+--=20an=20already=20= enforced=20parent=20must=20still=20recurse=20to=20regular=20inheritance=20= children=0A+create=20table=20p1(f1=20int=20constraint=20p1_a_check=20= check=20(f1=20>=200)=20enforced);=0A+create=20table=20p1_c1()=20= inherits(p1);=0A+alter=20table=20p1_c1=20alter=20constraint=20p1_a_check=20= not=20enforced;=0A+insert=20into=20p1_c1=20values(-1);=0A+alter=20table=20= p1=20alter=20constraint=20p1_a_check=20enforced;=20--error=0A+ERROR:=20=20= check=20constraint=20"p1_a_check"=20of=20relation=20"p1_c1"=20is=20= violated=20by=20some=20row=0A+delete=20from=20p1_c1;=0A+alter=20table=20= p1=20alter=20constraint=20p1_a_check=20enforced;=20--ok=0A+select=20=20= conname,=20conenforced,=20convalidated,=20conrelid::regclass=0A+from=20=20= =20=20pg_constraint=0A+where=20=20=20conname=20=3D=20'p1_a_check'=20and=20= contype=20=3D=20'c'=0A+order=20by=20conrelid::regclass::text=20collate=20= "C";=0A+=20=20conname=20=20=20|=20conenforced=20|=20convalidated=20|=20= conrelid=20=0A+------------+-------------+--------------+----------=0A+=20= p1_a_check=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=20=20=20=20=20=20= =20=20=20=20=20=20|=20p1=0A+=20p1_a_check=20|=20t=20=20=20=20=20=20=20=20= =20=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20=20|=20p1_c1=0A+(2=20= rows)=0A+=0A+insert=20into=20p1_c1=20values(-1);=20--error=0A+ERROR:=20=20= new=20row=20for=20relation=20"p1_c1"=20violates=20check=20constraint=20= "p1_a_check"=0A+DETAIL:=20=20Failing=20row=20contains=20(-1).=0A+drop=20= table=20p1=20cascade;=0A+NOTICE:=20=20drop=20cascades=20to=20table=20= p1_c1=0A=20--for=20"no=20inherit"=20check=20constraint,=20it=20will=20= not=20recurse=20to=20child=20table=0A=20create=20table=20p1(f1=20int=20= constraint=20p1_a_check=20check=20(f1=20>=200)=20no=20inherit=20not=20= enforced);=0A=20create=20table=20p1_c1(f1=20int=20constraint=20= p1_a_check=20check=20(f1=20>=200)=20not=20enforced);=0Adiff=20--git=20= a/src/test/regress/sql/constraints.sql=20= b/src/test/regress/sql/constraints.sql=0Aindex=20= dc133b124bb..c84eaa44b9c=20100644=0A---=20= a/src/test/regress/sql/constraints.sql=0A+++=20= b/src/test/regress/sql/constraints.sql=0A@@=20-318,6=20+318,24=20@@=20= select=20*=20from=20check_constraint_status;=0A=20drop=20table=20= parted_ch;=0A=20drop=20view=20check_constraint_status;=0A=20=0A+--=20an=20= already=20enforced=20partitioned=20parent=20must=20still=20recurse=20to=20= partitions=0A+create=20table=20parted_ch_recurse(=0A+=20=20a=20int,=0A+=20= =20constraint=20cc=20check=20(a=20>=200)=20enforced=0A+)=20partition=20= by=20range(a);=0A+create=20table=20parted_ch_recurse_1=20partition=20of=20= parted_ch_recurse=20for=20values=20from=20(-100)=20to=20(100);=0A+alter=20= table=20parted_ch_recurse_1=20alter=20constraint=20cc=20not=20enforced;=0A= +insert=20into=20parted_ch_recurse_1=20values(-1);=0A+alter=20table=20= parted_ch_recurse=20alter=20constraint=20cc=20enforced;=20--error=0A= +delete=20from=20parted_ch_recurse_1=20where=20a=20=3D=20-1;=0A+alter=20= table=20parted_ch_recurse=20alter=20constraint=20cc=20enforced;=0A= +select=20conrelid::regclass,=20conenforced,=20convalidated=0A+from=20= pg_constraint=0A+where=20conname=20=3D=20'cc'=20and=20= conrelid::regclass::text=20like=20'parted_ch_recurse%'=0A+order=20by=20= conrelid::regclass::text;=0A+insert=20into=20parted_ch_recurse_1=20= values(-1);=20--error=0A+drop=20table=20parted_ch_recurse;=0A+=0A=20--=0A= =20--=20Primary=20keys=0A=20--=0Adiff=20--git=20= a/src/test/regress/sql/inherit.sql=20b/src/test/regress/sql/inherit.sql=0A= index=208f986904389..8a7417765aa=20100644=0A---=20= a/src/test/regress/sql/inherit.sql=0A+++=20= b/src/test/regress/sql/inherit.sql=0A@@=20-535,6=20+535,21=20@@=20where=20= =20=20conname=20=3D=20'inh_check_constraint3'=20and=20contype=20=3D=20= 'c'=0A=20order=20by=20conrelid::regclass::text=20collate=20"C";=0A=20= drop=20table=20p1=20cascade;=0A=20=0A+--=20an=20already=20enforced=20= parent=20must=20still=20recurse=20to=20regular=20inheritance=20children=0A= +create=20table=20p1(f1=20int=20constraint=20p1_a_check=20check=20(f1=20= >=200)=20enforced);=0A+create=20table=20p1_c1()=20inherits(p1);=0A+alter=20= table=20p1_c1=20alter=20constraint=20p1_a_check=20not=20enforced;=0A= +insert=20into=20p1_c1=20values(-1);=0A+alter=20table=20p1=20alter=20= constraint=20p1_a_check=20enforced;=20--error=0A+delete=20from=20p1_c1;=0A= +alter=20table=20p1=20alter=20constraint=20p1_a_check=20enforced;=20--ok=0A= +select=20=20conname,=20conenforced,=20convalidated,=20= conrelid::regclass=0A+from=20=20=20=20pg_constraint=0A+where=20=20=20= conname=20=3D=20'p1_a_check'=20and=20contype=20=3D=20'c'=0A+order=20by=20= conrelid::regclass::text=20collate=20"C";=0A+insert=20into=20p1_c1=20= values(-1);=20--error=0A+drop=20table=20p1=20cascade;=0A+=0A=20--for=20= "no=20inherit"=20check=20constraint,=20it=20will=20not=20recurse=20to=20= child=20table=0A=20create=20table=20p1(f1=20int=20constraint=20= p1_a_check=20check=20(f1=20>=200)=20no=20inherit=20not=20enforced);=0A=20= create=20table=20p1_c1(f1=20int=20constraint=20p1_a_check=20check=20(f1=20= >=200)=20not=20enforced);=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_5A33854C-1BAD-4B7A-B597-7DD1EB3A8835 Content-Disposition: attachment; filename=v2-0002-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0002-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch" Content-Transfer-Encoding: quoted-printable =46rom=20060abd87eddc336fe4a73c980f095aeda6522b3c=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Mon,=2025=20May=202026=2016:59:21=20+0800=0ASubject:=20[PATCH=20= v2=202/2]=20doc:=20Clarify=20ALTER=20CONSTRAINT=20enforceability=20= behavior=0A=0AThe=20ALTER=20TABLE=20documentation=20said=20that=20= FOREIGN=20KEY=20and=20CHECK=0Aconstraints=20may=20be=20altered,=20but=20= did=20not=20distinguish=20between=0Adeferrability=20and=20enforceability=20= attributes.=0A=0AClarify=20that=20deferrability=20attributes=20can=20= currently=20be=20altered=20only=20for=0AFOREIGN=20KEY=20constraints,=20= while=20enforceability=20can=20be=20altered=20for=20both=0AFOREIGN=20KEY=20= and=20CHECK=20constraints.=20=20Also=20document=20that=20setting=20a=0A= constraint=20to=20ENFORCED=20verifies=20existing=20rows=20and=20resumes=20= checking=20new=0Aor=20updated=20rows.=0A=0AAuthor:=20Chao=20Li=20= =0AReviewed-by:=0ADiscussion:=20= https://postgr.es/m/E74C57FA-1DD0-4C8E-8FB1-538034752592@gmail.com=0A= Discussion:=20= https://postgr.es/m/711B1ED3-1781-4B6C-A573-B58AF20770E5@gmail.com=0A---=0A= =20doc/src/sgml/ref/alter_table.sgml=20|=2014=20++++++++++++--=0A=201=20= file=20changed,=2012=20insertions(+),=202=20deletions(-)=0A=0Adiff=20= --git=20a/doc/src/sgml/ref/alter_table.sgml=20= b/doc/src/sgml/ref/alter_table.sgml=0Aindex=20dec34337d1a..af247d82902=20= 100644=0A---=20a/doc/src/sgml/ref/alter_table.sgml=0A+++=20= b/doc/src/sgml/ref/alter_table.sgml=0A@@=20-586,8=20+586,18=20@@=20WITH=20= (=20MODULUS=20numeric_literal,=20REM=0A=20=20=20=20=20= =0A=20=20=20=20=20=20=0A=20=20=20=20=20=20=20This=20form=20= alters=20the=20attributes=20of=20a=20constraint=20that=20was=20= previously=0A-=20=20=20=20=20=20created.=20Currently=20FOREIGN=20= KEY=20and=20CHECK=0A-=20=20=20=20=20=20= constraints=20may=20be=20altered=20in=20this=20fashion,=20but=20see=20= below.=0A+=20=20=20=20=20=20created.=20=20Currently,=20the=20= deferrability=20attributes=20can=20be=20altered=20only=0A+=20=20=20=20=20= =20for=20FOREIGN=20KEY=20constraints.=20=20The=20= enforceability=0A+=20=20=20=20=20=20attribute=20can=20be=20altered=20for=20= FOREIGN=20KEY=20and=0A+=20=20=20=20=20=20= CHECK=20constraints.=0A+=20=20=20=20=20=0A+=0A= +=20=20=20=20=20=0A+=20=20=20=20=20=20Setting=20a=20constraint=20= to=20NOT=20ENFORCED=20causes=20the=0A+=20=20=20=20=20=20= database=20system=20to=20stop=20checking=20it=20for=20new=20or=20updated=20= rows.=20=20Setting=0A+=20=20=20=20=20=20a=20constraint=20to=20= ENFORCED=20causes=20the=20database=20system=0A+=20=20=20= =20=20=20to=20verify=20that=20existing=20rows=20satisfy=20the=20= constraint=20and=20to=20check=20it=0A+=20=20=20=20=20=20for=20new=20or=20= updated=20rows.=0A=20=20=20=20=20=20=0A=20=20=20=20=20=0A= =20=20=20=20=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_5A33854C-1BAD-4B7A-B597-7DD1EB3A8835--