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 1wS7dY-002y7r-1Q for pgsql-hackers@arkaria.postgresql.org; Wed, 27 May 2026 06:21:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wS7dW-007MGI-1U for pgsql-hackers@arkaria.postgresql.org; Wed, 27 May 2026 06:21:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wS7dW-007MG9-0L for pgsql-hackers@lists.postgresql.org; Wed, 27 May 2026 06:21:03 +0000 Received: from mail-dy1-x1335.google.com ([2607:f8b0:4864:20::1335]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wS7dU-00000001eB6-1h17 for pgsql-hackers@lists.postgresql.org; Wed, 27 May 2026 06:21:02 +0000 Received: by mail-dy1-x1335.google.com with SMTP id 5a478bee46e88-303dbfbec77so13506730eec.0 for ; Tue, 26 May 2026 23:21:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1779862858; x=1780467658; 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=CAVfUNRGg53v1E0R1Ed6OgOUGrObEbi+hFkVjqYbGjA=; b=QDOmaWOGm2boTBAgV++rnj4k1I/7MX/ruHBDLRfbBbi368D4JSBBVic8etooos9XVA l3IKSlKz3hC0xveXtRtE6PaHJaGaojJN/6hQuHoj1nhkxIfFIGHhS6nXHs3i6wz3g8Hp cCDssHZfp3OMKtL13usIFNZNEkOZVk/1ACq+StwpLpzv+cViLfG+K4bPMgRv6mK+DbqW 7rqMi/EV4ydRX4g0EQqZbZGeQi7kys8qh+LbXyH515io3cGn0IBzPMy5MxQZvsnYYQR/ 79aaiy6g7GE5KsYwH3xhUqUw5Ii1YaIu2Qa0F7UdoXM482xHAu1NJgLx05ixcBS6SPjs RCrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1779862858; x=1780467658; 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=CAVfUNRGg53v1E0R1Ed6OgOUGrObEbi+hFkVjqYbGjA=; b=GUgk5Zql8d1olTIIs9O08bwXOlVZnIG19JXwTp5DAWaUlxCER0kekwPi2OgSqxXi2+ HP+zBbxHxavp/h5xGS7Brl54Py29/XTw83eK2yAiMOGKIpU2OrtLb2wTZ00/PxnpVrNn ELc49VbfOuvvX92VtSFx3HLuun8mjabfQH7+j7dzuYuHson65CRaOT4nqHn6fAAde/Wq VmqZstF7E2sHXxlkUYZQSgCOs7aWZHIvpPiAbIjABfQRdIe6rRjrzKZj+AcwTOEGDG5z mYISsgA86DfYcnQ6kN2OIZ2SrbBI3l9LSB5YzzYed3UZ13W/nAX+lyQ+4ZlvE6ro1mLp Ys5Q== X-Forwarded-Encrypted: i=1; AFNElJ/ktAN3lzH6qaP4UkC4k2epCNSfIHRwPUlds2xIWIO6Eh0IL2ytW01hk/UooaccD++y/DVpShYiNIbr02BL@lists.postgresql.org X-Gm-Message-State: AOJu0Ywi24Kk/OjzKYeKfVkuTA0faZcSHItit36E9EmPAyUBYqI4ig3u hZrOSwL9z7SS7XPAZ9ksBWcXA8KVsi2iuodwDh9IRZaXwAVBl7oGv8SH X-Gm-Gg: Acq92OECuHZR3BQFxxTfoD4IIOaVQID3z9V/VqhxZ7KYEznu3nLGD3fNeoHYuSla/Qz MlclFgmabieGwP57HOZvpyPZBPwU8BpbidqEinOG+IEmY8uLWViEd3wRJVGRp8RNESq8k5V76e3 9A+r27kvWDromHtaPfSmX0Zj7eGZkpN/QXbf3OQnw1Jv+WeM33mC0zSKD6WqUYd7R9JbEFnLXP5 /c9Nor84wLmgxC05+BkK/8S5BNvg8xXOr+snE9lRy0QzJYp4tNeRpjyhysRKZrYSXTIJqmBfOFF 9e4c4bmvRNp32JeAdIPmCkMhKSQMk8RFd0rgYJ2993QLlEUvv9M1eBXpuPThlqJiNycamADYliP uim3pIuoWxysJzXVwDIrw6PgCMRVr8wOdK5Thi5PMwmeOgO99LcKF6OlfFwf/TOJT4jQtHxb8ty Jtd4vWK12ev+Jdn6lnKGcfXzAzDm/1lw== X-Received: by 2002:a05:7300:ad2d:b0:2ba:a2fb:403f with SMTP id 5a478bee46e88-30449051b2fmr8556308eec.21.1779862857828; Tue, 26 May 2026 23:20:57 -0700 (PDT) Received: from smtpclient.apple ([64.32.14.230]) by smtp.gmail.com with ESMTPSA id 5a478bee46e88-3045245d6aesm11578036eec.26.2026.05.26.23.20.54 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 26 May 2026 23:20:56 -0700 (PDT) From: Chao Li Message-Id: <7F0EA98A-6DBC-436A-8FF4-4A511A05ABE6@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_B3D63AC8-07A5-4BF4-A40A-97CBD84FD8F5" 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: Wed, 27 May 2026 14:20:21 +0800 In-Reply-To: Cc: =?utf-8?Q?=C3=81lvaro_Herrera?= , "L. pgsql-hackers" , Andrew Dunstan To: jian he References: <33E9C4C2-B6A8-4FCC-BEEA-461EA5FB98C8@gmail.com> 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=_B3D63AC8-07A5-4BF4-A40A-97CBD84FD8F5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On May 26, 2026, at 16:32, jian he = wrote: >=20 > On Tue, May 26, 2026 at 3:47=E2=80=AFPM Chao Li = wrote: >>=20 >>>=20 >>> I think this is a bug that we need to fix in 19 as well =E2=80=94 I = mean we should reject the ALTER TABLE. >>>=20 >>> -- >>> =C3=81lvaro Herrera >>=20 >> Thanks for your comment. Let me rework the patch. >>=20 >=20 > Hi. > Here are the comments placed in ATExecAlterCheckConstrEnforceability I > came up with: >=20 > + /* > + * If the check constraint qual definitions match but their = enforcement > + * statuses conflict (parent enforced, child unenforced), it = creates > + * ambiguity around how insert operations should handle the = mismatch. > + * Therefore, we should avoid states where the parent check = constraint is > + * enforced while the child is not. We actually enforced this = within > + * MergeConstraintsIntoExisting and MergeWithExistingConstraint. > + */ > + if (currcon->coninhcount > 0 && !recursing) > + ereport(ERROR, > + errcode(ERRCODE_INVALID_TABLE_DEFINITION), > + errmsg("cannot alter inherited constraint \"%s\" of > relation \"%s\" enforciability", > + NameStr(currcon->conname), > RelationGetRelationName(rel))); >=20 >=20 >=20 > -- > jian > https://www.enterprisedb.com/ > = Hi Jian, Thanks for your help. Your implementation is simple and clever: ``` + if (currcon->coninhcount > 0 && !recursing) + ereport(ERROR, + = errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot alter inherited = constraint \"%s\" of relation \"%s\" enforciability", + NameStr(currcon->conname), = RelationGetRelationName(rel))); ``` Basically, it disallows all enforceability changes on inherited = constraints (currcon->coninhcount > 0) at the recursion root = (!recursing), or in other words, it disallows the operation on any child = table. But I see several problems with this implementation: 1. As you pointed out earlier, when a parent is ENFORCED, changing a = child from ENFORCED to NOT ENFORCED should not be allowed. But when a = parent is NOT ENFORCED, changing a child from NOT ENFORCED to ENFORCED = should be allowed. The existing phase 3 checking also proves that. 2. Suppose a parent table is NOT ENFORCED, and a user changes a child = from NOT ENFORCED to ENFORCED, which is allowed. Later, if the user = wants to change the child back from ENFORCED to NOT ENFORCED, that = should also be allowed. But with your v1 patch, the user would have to = do the change through the parent table, which I think hurts the user = experience. 3. Suppose a child table is already ENFORCED, and a user issues a = command to change it to ENFORCED again, which is actually a no-op. = PostgreSQL usually allows this kind of no-op, but with your v1 patch, = this no-op would get an error as well, which I think also hurts the user = experience. 4. It cannot handle some complicated inheritance hierarchies. For = example, the following test passes with your v1: ``` evantest=3D# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) = ENFORCED); CREATE TABLE evantest=3D# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) = ENFORCED); CREATE TABLE evantest=3D# evantest=3D# CREATE TABLE ch () INHERITS (p1, p2); NOTICE: merging multiple inherited definitions of column "a" CREATE TABLE evantest=3D# 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=3D# SELECT conrelid::regclass, conname, conenforced, = coninhcount, conislocal evantest-# FROM pg_constraint WHERE conname =3D '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=3D# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) NOT = ENFORCED); CREATE TABLE evantest=3D# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) = ENFORCED); CREATE TABLE evantest=3D# CREATE TABLE ch () INHERITS (p1, p2); NOTICE: merging multiple inherited definitions of column "a" CREATE TABLE evantest=3D# SELECT conrelid::regclass, conname, conenforced, = coninhcount, conislocal evantest-# FROM pg_constraint WHERE conname =3D =E2=80=98c'; 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. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_B3D63AC8-07A5-4BF4-A40A-97CBD84FD8F5 Content-Disposition: attachment; filename=v2-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch" Content-Transfer-Encoding: quoted-printable =46rom=203db18b528e579ef6b7bf77a5af79b33e23a15cdb=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Wed,=2027=20May=202026=2013:49:29=20+0800=0ASubject:=20[PATCH=20= v2]=20Prevent=20inherited=20CHECK=20constraints=20from=20being=20= weakened=0A=0ADisallow=20marking=20an=20inherited=20CHECK=20constraint=20= as=20NOT=20ENFORCED=20when=20an=0Aequivalent=20parent=20constraint=20= remains=20ENFORCED.=20This=20prevents=20ALTER=0ACONSTRAINT=20from=20= producing=20a=20child=20constraint=20that=20is=20weaker=20than=20one=20= of=0Aits=20inherited=20parent=20definitions.=0A=0AWhen=20recursively=20= altering=20a=20CHECK=20constraint=20to=20NOT=20ENFORCED,=20collect=20the=0A= equivalent=20constraints=20in=20the=20affected=20inheritance=20subtree=20= and=20ignore=0Athose=20parent=20constraints=20while=20checking=20= descendants.=20If=20a=20descendant=20also=0Ainherits=20an=20equivalent=20= ENFORCED=20constraint=20from=20a=20parent=20outside=20the=0Acurrent=20= ALTER,=20keep=20the=20descendant=20ENFORCED=20by=20merging=20to=20the=20= stricter=0Astate.=0A=0AAdd=20regression=20coverage=20for=20direct=20= child=20ALTER,=20ONLY=20ALTER,=20mixed-parent=0Ainheritance,=20and=20a=20= common-ancestor=20diamond=20where=20all=20equivalent=20inherited=0A= constraints=20can=20be=20changed=20together.=0A=0AAuthor:=20Chao=20Li=20= =0AReviewed-by:=0ADiscussion:=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|=20266=20= ++++++++++++++++++++--=0A=20src/test/regress/expected/constraints.out=20= |=20=2012=20+-=0A=20src/test/regress/expected/inherit.out=20=20=20=20=20= |=20=2054=20+++++=0A=20src/test/regress/sql/constraints.sql=20=20=20=20=20= =20|=20=20=205=20+-=0A=20src/test/regress/sql/inherit.sql=20=20=20=20=20=20= =20=20=20=20|=20=2032=20+++=0A=205=20files=20changed,=20344=20= insertions(+),=2025=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/commands/tablecmds.c=20b/src/backend/commands/tablecmds.c=0A= index=20a1845240a98..5c3a09cc666=20100644=0A---=20= a/src/backend/commands/tablecmds.c=0A+++=20= b/src/backend/commands/tablecmds.c=0A@@=20-437,6=20+437,7=20@@=20static=20= bool=20ATExecAlterFKConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*=0A=20static=20bool=20= ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=09=09=09=20= Relation=20conrel,=20HeapTuple=20contuple,=0A=20=09=09=09=09=09=09=09=09=09= =09=09=09=20bool=20recurse,=20bool=20recursing,=0A+=09=09=09=09=09=09=09=09= =09=09=09=09=20List=20*changing_conids,=0A=20=09=09=09=09=09=09=09=09=09=09= =09=09=20LOCKMODE=20lockmode);=0A=20static=20bool=20= ATExecAlterConstrDeferrability(List=20**wqueue,=20ATAlterConstraint=20= *cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=09=20=20=20Relation=20conrel,=20= Relation=20tgrel,=20Relation=20rel,=0A@@=20-459,6=20+460,7=20@@=20static=20= void=20AlterFKConstrEnforceabilityRecurse(List=20**wqueue,=20= ATAlterConstraint=0A=20static=20void=20= AlterCheckConstrEnforceabilityRecurse(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=09=09=09=20= =20Relation=20conrel,=20Oid=20conrelid,=0A=20=09=09=09=09=09=09=09=09=09=09= =09=09=20=20bool=20recurse,=20bool=20recursing,=0A+=09=09=09=09=09=09=09=09= =09=09=09=09=20=20List=20*changing_conids,=0A=20=09=09=09=09=09=09=09=09=09= =09=09=09=20=20LOCKMODE=20lockmode);=0A=20static=20void=20= AlterConstrDeferrabilityRecurse(List=20**wqueue,=20ATAlterConstraint=20= *cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=09=09Relation=20conrel,=20= Relation=20tgrel,=20Relation=20rel,=0A@@=20-466,6=20+468,13=20@@=20= static=20void=20AlterConstrDeferrabilityRecurse(List=20**wqueue,=20= ATAlterConstraint=20*cm=0A=20=09=09=09=09=09=09=09=09=09=09=09List=20= **otherrelids,=20LOCKMODE=20lockmode);=0A=20static=20void=20= AlterConstrUpdateConstraintEntry(ATAlterConstraint=20*cmdcon,=20Relation=20= conrel,=0A=20=09=09=09=09=09=09=09=09=09=09=09=20HeapTuple=20contuple);=0A= +static=20Oid=09ATGetEquivalentCheckConstraintOid(Relation=20conrel,=20= Oid=20conrelid,=0A+=09=09=09=09=09=09=09=09=09=09=09=20=20const=20char=20= *conname,=0A+=09=09=09=09=09=09=09=09=09=09=09=20=20HeapTuple=20= contuple);=0A+static=20bool=20= ATCheckCheckConstrHasEnforcedParent(Relation=20conrel,=20Relation=20rel,=0A= +=09=09=09=09=09=09=09=09=09=09=09=09HeapTuple=20contuple,=0A+=09=09=09=09= =09=09=09=09=09=09=09=09List=20*changing_conids,=0A+=09=09=09=09=09=09=09= =09=09=09=09=09Oid=20*enforced_parentoid);=0A=20static=20ObjectAddress=20= ATExecValidateConstraint(List=20**wqueue,=0A=20=09=09=09=09=09=09=09=09=09= =09=09=20=20Relation=20rel,=20char=20*constrName,=0A=20=09=09=09=09=09=09= =09=09=09=09=09=20=20bool=20recurse,=20bool=20recursing,=20LOCKMODE=20= lockmode);=0A@@=20-477,6=20+486,7=20@@=20static=20void=20= QueueCheckConstraintValidation(List=20**wqueue,=20Relation=20conrel,=20= Relat=0A=20static=20void=20QueueNNConstraintValidation(List=20**wqueue,=20= Relation=20conrel,=20Relation=20rel,=0A=20=09=09=09=09=09=09=09=09=09=09= HeapTuple=20contuple,=20bool=20recurse,=20bool=20recursing,=0A=20=09=09=09= =09=09=09=09=09=09=09LOCKMODE=20lockmode);=0A+static=20bool=20= constraints_equivalent(HeapTuple=20a,=20HeapTuple=20b,=20TupleDesc=20= tupleDesc);=0A=20static=20int=09transformColumnNameList(Oid=20relId,=20= List=20*colList,=0A=20=09=09=09=09=09=09=09=09=09int16=20*attnums,=20Oid=20= *atttypids,=20Oid=20*attcollids);=0A=20static=20int=09= transformFkeyGetPrimaryKey(Relation=20pkrel,=20Oid=20*indexOid,=0A@@=20= -12484,7=20+12494,7=20@@=20ATExecAlterConstraintInternal(List=20= **wqueue,=20ATAlterConstraint=20*cmdcon,=0A=20=09=09else=20if=20= (currcon->contype=20=3D=3D=20CONSTRAINT_CHECK)=0A=20=09=09=09changed=20=3D= =20ATExecAlterCheckConstrEnforceability(wqueue,=20cmdcon,=20conrel,=0A=20= =09=09=09=09=09=09=09=09=09=09=09=09=09=09=20=20=20contuple,=20recurse,=20= false,=0A-=09=09=09=09=09=09=09=09=09=09=09=09=09=09=20=20=20lockmode);=0A= +=09=09=09=09=09=09=09=09=09=09=09=09=09=09=20=20=20NIL,=20lockmode);=0A=20= =09}=0A=20=09else=20if=20(cmdcon->alterDeferrability=20&&=0A=20=09=09=09=20= ATExecAlterConstrDeferrability(wqueue,=20cmdcon,=20conrel,=20tgrel,=20= rel,=0A@@=20-12671,12=20+12681,16=20@@=20= ATExecAlterFKConstrEnforceability(List=20**wqueue,=20ATAlterConstraint=20= *cmdcon,=0A=20static=20bool=0A=20= ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=20Relation=20= conrel,=20HeapTuple=20contuple,=0A-=09=09=09=09=09=09=09=09=09=20bool=20= recurse,=20bool=20recursing,=20LOCKMODE=20lockmode)=0A+=09=09=09=09=09=09= =09=09=09=20bool=20recurse,=20bool=20recursing,=0A+=09=09=09=09=09=09=09=09= =09=20List=20*changing_conids,=0A+=09=09=09=09=09=09=09=09=09=20LOCKMODE=20= lockmode)=0A=20{=0A=20=09Form_pg_constraint=20currcon;=0A=20=09Relation=09= rel;=0A=20=09bool=09=09changed=20=3D=20false;=0A=20=09List=09=20=20=20= *children=20=3D=20NIL;=0A+=09bool=09=09target_enforced=20=3D=20= cmdcon->is_enforced;=0A+=09Oid=09=09=09enforced_parentoid=20=3D=20= InvalidOid;=0A=20=0A=20=09/*=20Since=20this=20function=20recurses,=20it=20= could=20be=20driven=20to=20stack=20overflow=20*/=0A=20=09= check_stack_depth();=0A@@=20-12693,16=20+12707,52=20@@=20= ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=20*/=0A=20=09rel=20=3D=20= table_open(currcon->conrelid,=20NoLock);=0A=20=0A-=09if=20= (currcon->conenforced=20!=3D=20cmdcon->is_enforced)=0A+=09/*=0A+=09=20*=20= When=20setting=20a=20constraint=20to=20NOT=20ENFORCED,=20check=20whether=20= any=20matching=0A+=09=20*=20parent=20constraint=20remains=20ENFORCED=20= and=20is=20not=20part=20of=20this=20ALTER.=0A+=09=20*=0A+=09=20*=20For=20= a=20direct=20ALTER=20of=20an=20inherited=20constraint,=20reject=20the=20= command,=0A+=09=20*=20because=20the=20child=20cannot=20be=20weakened=20= while=20its=20parent=20remains=20enforced.=0A+=09=20*=0A+=09=20*=20= During=20recursion,=20another=20parent=20outside=20this=20ALTER=20may=20= still=20enforce=20the=0A+=09=20*=20same=20constraint.=20In=20that=20= case,=20keep=20the=20child=20constraint=20ENFORCED=20so=20that=0A+=09=20= *=20its=20merged=20enforceability=20still=20reflects=20the=20remaining=20= enforced=20parent.=0A+=09=20*/=0A+=09if=20(!cmdcon->is_enforced=20&&=0A+=09= =09ATCheckCheckConstrHasEnforcedParent(conrel,=20rel,=20contuple,=0A+=09=09= =09=09=09=09=09=09=09=09=09changing_conids,=0A+=09=09=09=09=09=09=09=09=09= =09=09&enforced_parentoid))=0A=20=09{=0A-=09=09= AlterConstrUpdateConstraintEntry(cmdcon,=20conrel,=20contuple);=0A+=09=09= if=20(!recursing)=0A+=09=09=09ereport(ERROR,=0A+=09=09=09=09=09= errcode(ERRCODE_INVALID_OBJECT_DEFINITION),=0A+=09=09=09=09=09= errmsg("cannot=20mark=20inherited=20constraint=20\"%s\"=20as=20NOT=20= ENFORCED=20because=20"=0A+=09=09=09=09=09=09=20=20=20"matching=20= constraint=20on=20parent=20table=20\"%s\"=20is=20ENFORCED",=0A+=09=09=09=09= =09=09=20=20=20NameStr(currcon->conname),=0A+=09=09=09=09=09=09=20=20=20= get_rel_name(enforced_parentoid)));=0A+=0A+=09=09target_enforced=20=3D=20= true;=0A+=09}=0A+=0A+=09/*=0A+=09=20*=20Update=20to=20the=20merged=20= enforceability=20if=20needed.=20This=20may=20differ=20from=20the=0A+=09=20= *=20requested=20enforceability=20when=20another=20matching=20parent=20= constraint=20remains=0A+=09=20*=20enforced.=0A+=09=20*/=0A+=09if=20= (currcon->conenforced=20!=3D=20target_enforced)=0A+=09{=0A+=09=09= ATAlterConstraint=20updatecon=20=3D=20*cmdcon;=0A+=0A+=09=09= updatecon.is_enforced=20=3D=20target_enforced;=0A+=09=09= AlterConstrUpdateConstraintEntry(&updatecon,=20conrel,=20contuple);=0A=20= =09=09changed=20=3D=20true;=0A=20=09}=0A=20=0A=20=09/*=0A=20=09=20*=20= Note=20that=20we=20must=20recurse=20even=20when=20trying=20to=20change=20= a=20check=20constraint=0A=20=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*=20constraints=20might=20be=20enforced=20= and=20need=20to=20be=20changed=20to=20not=20enforced,=0A+=09=20*=20= unless=20they=20still=20inherit=20an=20enforced=20constraint=20from=20= another=20parent.=0A=20=09=20*=20Conversely,=20we=20should=20do=20= nothing=20if=20a=20constraint=20is=20being=20set=20to=0A=20=09=20*=20= enforced=20and=20is=20already=20enforced,=20as=20descendant=20= constraints=20cannot=20be=0A=20=09=20*=20different=20in=20that=20case.=0A= @@=20-12715,28=20+12765,63=20@@=20= ATExecAlterCheckConstrEnforceability(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=20*=20try=20to=20look=20for=20= it=20in=20the=20children.=0A=20=09=09=20*/=0A=20=09=09if=20(!recursing=20= &&=20!currcon->connoinherit)=0A+=09=09{=0A=20=09=09=09children=20=3D=20= find_all_inheritors(RelationGetRelid(rel),=0A=20=09=09=09=09=09=09=09=09=09= =09=20=20=20lockmode,=20NULL);=0A=20=0A+=09=09=09foreach_oid(childoid,=20= children)=0A+=09=09=09{=0A+=09=09=09=09if=20(childoid=20=3D=3D=20= RelationGetRelid(rel))=0A+=09=09=09=09=09continue;=0A+=0A+=09=09=09=09/*=0A= +=09=09=09=09*=20If=20we=20are=20told=20not=20to=20recurse,=20there=20= had=20better=20not=20be=20any=0A+=09=09=09=09*=20child=20tables,=20= because=20we=20can't=20change=20constraint=20enforceability=0A+=09=09=09=09= *=20on=20the=20parent=20unless=20we=20have=20changed=20enforceability=20= for=20all=0A+=09=09=09=09*=20child.=0A+=09=09=09=09*/=0A+=09=09=09=09if=20= (!recurse)=0A+=09=09=09=09=09ereport(ERROR,=0A+=09=09=09=09=09=09=09= errcode(ERRCODE_INVALID_TABLE_DEFINITION),=0A+=09=09=09=09=09=09=09= errmsg("constraint=20must=20be=20altered=20on=20child=20tables=20too"),=0A= +=09=09=09=09=09=09=09errhint("Do=20not=20specify=20the=20ONLY=20= keyword."));=0A+=09=09=09}=0A+=0A+=09=09=09if=20(!cmdcon->is_enforced)=0A= +=09=09=09{=0A+=09=09=09=09/*=0A+=09=09=09=09=20*=20Build=20the=20set=20= of=20equivalent=20CHECK=20constraints=20that=20this=0A+=09=09=09=09=20*=20= command=20will=20attempt=20to=20change=20before=20visiting=20= descendants.=0A+=09=09=09=09=20*=20Each=20descendant=20is=20compared=20= to=20the=20original=20target=0A+=09=09=09=09=20*=20constraint,=20not=20= only=20by=20name.=20The=20root=20itself=20has=20already=0A+=09=09=09=09=20= *=20been=20checked=20above.=0A+=09=09=09=09=20*/=0A+=09=09=09=09= changing_conids=20=3D=20lappend_oid(list_copy(changing_conids),=0A+=09=09= =09=09=09=09=09=09=09=09=09=20=20currcon->oid);=0A+=0A+=09=09=09=09= foreach_oid(childoid,=20children)=0A+=09=09=09=09{=0A+=09=09=09=09=09if=20= (childoid=20=3D=3D=20RelationGetRelid(rel))=0A+=09=09=09=09=09=09= continue;=0A+=0A+=09=09=09=09=09changing_conids=20=3D=0A+=09=09=09=09=09=09= list_append_unique_oid(changing_conids,=0A+=09=09=09=09=09=09=09=09=09=09= =09=20=20=20ATGetEquivalentCheckConstraintOid(conrel,=0A+=09=09=09=09=09=09= =09=09=09=09=09=09=09=09=09=09=09=09=09=09=20childoid,=0A+=09=09=09=09=09= =09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=20cmdcon->conname,=0A+=09=09= =09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=20contuple));=0A+=09= =09=09=09}=0A+=09=09=09}=0A+=09=09}=0A+=0A=20=09=09foreach_oid(childoid,=20= children)=0A=20=09=09{=0A=20=09=09=09if=20(childoid=20=3D=3D=20= RelationGetRelid(rel))=0A=20=09=09=09=09continue;=0A=20=0A-=09=09=09/*=0A= -=09=09=09=20*=20If=20we=20are=20told=20not=20to=20recurse,=20there=20= had=20better=20not=20be=20any=0A-=09=09=09=20*=20child=20tables,=20= because=20we=20can't=20change=20constraint=20enforceability=0A-=09=09=09=20= *=20on=20the=20parent=20unless=20we=20have=20changed=20enforceability=20= for=20all=0A-=09=09=09=20*=20child.=0A-=09=09=09=20*/=0A-=09=09=09if=20= (!recurse)=0A-=09=09=09=09ereport(ERROR,=0A-=09=09=09=09=09=09= errcode(ERRCODE_INVALID_TABLE_DEFINITION),=0A-=09=09=09=09=09=09= errmsg("constraint=20must=20be=20altered=20on=20child=20tables=20too"),=0A= -=09=09=09=09=09=09errhint("Do=20not=20specify=20the=20ONLY=20= keyword."));=0A-=0A=20=09=09=09= AlterCheckConstrEnforceabilityRecurse(wqueue,=20cmdcon,=20conrel,=0A=20=09= =09=09=09=09=09=09=09=09=09=09=09=20=20childoid,=20false,=20true,=0A+=09=09= =09=09=09=09=09=09=09=09=09=09=20=20changing_conids,=0A=20=09=09=09=09=09= =09=09=09=09=09=09=09=20=20lockmode);=0A=20=09=09}=0A=20=09}=0A@@=20= -12748,7=20+12833,7=20@@=20ATExecAlterCheckConstrEnforceability(List=20= **wqueue,=20ATAlterConstraint=20*cmdcon,=0A=20=09=20*/=0A=20=09if=20= (rel->rd_rel->relkind=20=3D=3D=20RELKIND_RELATION=20&&=0A=20=09=09= !currcon->conenforced=20&&=0A-=09=09cmdcon->is_enforced)=0A+=09=09= target_enforced)=0A=20=09{=0A=20=09=09AlteredTableInfo=20*tab;=0A=20=09=09= NewConstraint=20*newcon;=0A@@=20-12788,6=20+12873,7=20@@=20static=20void=0A= =20AlterCheckConstrEnforceabilityRecurse(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=09=09=09=09=09=09=09=20=20= Relation=20conrel,=20Oid=20conrelid,=0A=20=09=09=09=09=09=09=09=09=09=20=20= bool=20recurse,=20bool=20recursing,=0A+=09=09=09=09=09=09=09=09=09=20=20= List=20*changing_conids,=0A=20=09=09=09=09=09=09=09=09=09=20=20LOCKMODE=20= lockmode)=0A=20{=0A=20=09SysScanDesc=20pscan;=0A@@=20-12817,11=20= +12903,153=20@@=20AlterCheckConstrEnforceabilityRecurse(List=20**wqueue,=20= ATAlterConstraint=20*cmdcon,=0A=20=09=09=09=09=09=20=20=20= cmdcon->conname,=20get_rel_name(conrelid)));=0A=20=0A=20=09= ATExecAlterCheckConstrEnforceability(wqueue,=20cmdcon,=20conrel,=20= childtup,=0A-=09=09=09=09=09=09=09=09=09=09=20recurse,=20recursing,=20= lockmode);=0A+=09=09=09=09=09=09=09=09=09=09=20recurse,=20recursing,=20= changing_conids,=0A+=09=09=09=09=09=09=09=09=09=09=20lockmode);=0A=20=0A=20= =09systable_endscan(pscan);=0A=20}=0A=20=0A+/*=0A+=20*=20Look=20up=20a=20= CHECK=20constraint=20by=20relation=20OID=20and=20constraint=20name=20= that=20is=0A+=20*=20equivalent=20to=20the=20given=20constraint=20tuple.=0A= +=20*/=0A+static=20Oid=0A+ATGetEquivalentCheckConstraintOid(Relation=20= conrel,=20Oid=20conrelid,=0A+=09=09=09=09=09=09=09=09=20=20const=20char=20= *conname,=20HeapTuple=20contuple)=0A+{=0A+=09SysScanDesc=20scan;=0A+=09= HeapTuple=09tup;=0A+=09ScanKeyData=20skey[3];=0A+=09Oid=09=09=09conid=20= =3D=20InvalidOid;=0A+=0A+=09ScanKeyInit(&skey[0],=0A+=09=09=09=09= Anum_pg_constraint_conrelid,=0A+=09=09=09=09BTEqualStrategyNumber,=20= F_OIDEQ,=0A+=09=09=09=09ObjectIdGetDatum(conrelid));=0A+=09= ScanKeyInit(&skey[1],=0A+=09=09=09=09Anum_pg_constraint_contypid,=0A+=09=09= =09=09BTEqualStrategyNumber,=20F_OIDEQ,=0A+=09=09=09=09= ObjectIdGetDatum(InvalidOid));=0A+=09ScanKeyInit(&skey[2],=0A+=09=09=09=09= Anum_pg_constraint_conname,=0A+=09=09=09=09BTEqualStrategyNumber,=20= F_NAMEEQ,=0A+=09=09=09=09CStringGetDatum(conname));=0A+=0A+=09scan=20=3D=20= systable_beginscan(conrel,=20ConstraintRelidTypidNameIndexId,=20true,=0A= +=09=09=09=09=09=09=09=20=20NULL,=203,=20skey);=0A+=0A+=09while=20= (HeapTupleIsValid(tup=20=3D=20systable_getnext(scan)))=0A+=09{=0A+=09=09= Form_pg_constraint=20con=20=3D=20(Form_pg_constraint)=20GETSTRUCT(tup);=0A= +=0A+=09=09if=20(con->contype=20=3D=3D=20CONSTRAINT_CHECK=20&&=0A+=09=09=09= constraints_equivalent(tup,=20contuple,=20RelationGetDescr(conrel)))=0A+=09= =09{=0A+=09=09=09conid=20=3D=20con->oid;=0A+=09=09=09break;=0A+=09=09}=0A= +=09}=0A+=0A+=09systable_endscan(scan);=0A+=0A+=09if=20= (!OidIsValid(conid))=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_UNDEFINED_OBJECT),=0A+=09=09=09=09errmsg("equivalent=20= CHECK=20constraint=20\"%s\"=20of=20relation=20\"%s\"=20does=20not=20= exist",=0A+=09=09=09=09=09=20=20=20conname,=20get_rel_name(conrelid)));=0A= +=0A+=09return=20conid;=0A+}=0A+=0A+/*=0A+=20*=20When=20setting=20an=20= inherited=20CHECK=20constraint=20to=20NOT=20ENFORCED,=20look=20for=20a=0A= +=20*=20matching=20parent=20constraint=20that=20remains=20ENFORCED=20and=20= is=20not=20part=20of=20the=20same=0A+=20*=20ALTER.=0A+=20*/=0A+static=20= bool=0A+ATCheckCheckConstrHasEnforcedParent(Relation=20conrel,=20= Relation=20rel,=0A+=09=09=09=09=09=09=09=09=09HeapTuple=20contuple,=0A+=09= =09=09=09=09=09=09=09=09List=20*changing_conids,=0A+=09=09=09=09=09=09=09= =09=09Oid=20*enforced_parentoid)=0A+{=0A+=09Form_pg_constraint=20= currcon;=0A+=09Relation=09inhrel;=0A+=09SysScanDesc=20scan;=0A+=09= ScanKeyData=20skey;=0A+=09HeapTuple=09inheritsTuple;=0A+=0A+=09currcon=20= =3D=20(Form_pg_constraint)=20GETSTRUCT(contuple);=0A+=09= Assert(currcon->contype=20=3D=3D=20CONSTRAINT_CHECK);=0A+=0A+=09if=20= (currcon->coninhcount=20<=3D=200)=0A+=09=09return=20false;=0A+=0A+=09= inhrel=20=3D=20table_open(InheritsRelationId,=20AccessShareLock);=0A+=0A= +=09ScanKeyInit(&skey,=0A+=09=09=09=09Anum_pg_inherits_inhrelid,=0A+=09=09= =09=09BTEqualStrategyNumber,=20F_OIDEQ,=0A+=09=09=09=09= ObjectIdGetDatum(RelationGetRelid(rel)));=0A+=09scan=20=3D=20= systable_beginscan(inhrel,=20InheritsRelidSeqnoIndexId,=0A+=09=09=09=09=09= =09=09=20=20true,=20NULL,=201,=20&skey);=0A+=0A+=09while=20= (HeapTupleIsValid(inheritsTuple=20=3D=20systable_getnext(scan)))=0A+=09{=0A= +=09=09Oid=09=09=09parentoid;=0A+=09=09SysScanDesc=20pscan;=0A+=09=09= ScanKeyData=20pkey[3];=0A+=09=09HeapTuple=09parenttup;=0A+=0A+=09=09= parentoid=20=3D=20((Form_pg_inherits)=20= GETSTRUCT(inheritsTuple))->inhparent;=0A+=0A+=09=09ScanKeyInit(&pkey[0],=0A= +=09=09=09=09=09Anum_pg_constraint_conrelid,=0A+=09=09=09=09=09= BTEqualStrategyNumber,=20F_OIDEQ,=0A+=09=09=09=09=09= ObjectIdGetDatum(parentoid));=0A+=09=09ScanKeyInit(&pkey[1],=0A+=09=09=09= =09=09Anum_pg_constraint_contypid,=0A+=09=09=09=09=09= BTEqualStrategyNumber,=20F_OIDEQ,=0A+=09=09=09=09=09= ObjectIdGetDatum(InvalidOid));=0A+=09=09ScanKeyInit(&pkey[2],=0A+=09=09=09= =09=09Anum_pg_constraint_conname,=0A+=09=09=09=09=09= BTEqualStrategyNumber,=20F_NAMEEQ,=0A+=09=09=09=09=09= NameGetDatum(&currcon->conname));=0A+=0A+=09=09pscan=20=3D=20= systable_beginscan(conrel,=20ConstraintRelidTypidNameIndexId,=0A+=09=09=09= =09=09=09=09=09=20=20=20true,=20NULL,=203,=20pkey);=0A+=0A+=09=09while=20= (HeapTupleIsValid(parenttup=20=3D=20systable_getnext(pscan)))=0A+=09=09{=0A= +=09=09=09Form_pg_constraint=20parentcon;=0A+=0A+=09=09=09parentcon=20=3D=20= (Form_pg_constraint)=20GETSTRUCT(parenttup);=0A+=0A+=09=09=09if=20= (list_member_oid(changing_conids,=20parentcon->oid)=20||=0A+=09=09=09=09= parentcon->contype=20!=3D=20CONSTRAINT_CHECK=20||=0A+=09=09=09=09= parentcon->connoinherit=20||=0A+=09=09=09=09!parentcon->conenforced)=0A+=09= =09=09=09continue;=0A+=0A+=09=09=09if=20= (constraints_equivalent(parenttup,=20contuple,=0A+=09=09=09=09=09=09=09=09= =09=20=20=20RelationGetDescr(conrel)))=0A+=09=09=09{=0A+=09=09=09=09= *enforced_parentoid=20=3D=20parentoid;=0A+=09=09=09=09= systable_endscan(pscan);=0A+=09=09=09=09systable_endscan(scan);=0A+=09=09= =09=09table_close(inhrel,=20AccessShareLock);=0A+=09=09=09=09return=20= true;=0A+=09=09=09}=0A+=09=09}=0A+=0A+=09=09systable_endscan(pscan);=0A+=09= }=0A+=0A+=09systable_endscan(scan);=0A+=09table_close(inhrel,=20= AccessShareLock);=0A+=0A+=09return=20false;=0A+}=0A+=0A=20/*=0A=20=20*=20= Returns=20true=20if=20the=20constraint's=20deferrability=20is=20altered.=0A= =20=20*=0Adiff=20--git=20a/src/test/regress/expected/constraints.out=20= b/src/test/regress/expected/constraints.out=0Aindex=20= e54fec7fb57..dada27a4cba=20100644=0A---=20= a/src/test/regress/expected/constraints.out=0A+++=20= b/src/test/regress/expected/constraints.out=0A@@=20-446,8=20+446,12=20@@=20= alter=20table=20parted_ch_2=20alter=20constraint=20cc_2=20enforced;=20= --error=0A=20ERROR:=20=20check=20constraint=20"cc_2"=20of=20relation=20= "parted_ch_2"=20is=20violated=20by=20some=20row=0A=20delete=20from=20= parted_ch=20where=20a=20=3D=2016;=0A=20alter=20table=20parted_ch_2=20= alter=20constraint=20cc_2=20enforced;=0A-alter=20table=20parted_ch_2=20= alter=20constraint=20cc=20not=20enforced;=0A-alter=20table=20parted_ch_2=20= alter=20constraint=20cc_1=20not=20enforced;=0A+alter=20table=20= parted_ch_2=20alter=20constraint=20cc=20not=20enforced;=20--error=0A= +ERROR:=20=20cannot=20mark=20inherited=20constraint=20"cc"=20as=20NOT=20= ENFORCED=20because=20matching=20constraint=20on=20parent=20table=20= "parted_ch"=20is=20ENFORCED=0A+alter=20table=20only=20parted_ch_2=20= alter=20constraint=20cc=20not=20enforced;=20--error=0A+ERROR:=20=20= cannot=20mark=20inherited=20constraint=20"cc"=20as=20NOT=20ENFORCED=20= because=20matching=20constraint=20on=20parent=20table=20"parted_ch"=20is=20= ENFORCED=0A+alter=20table=20parted_ch_2=20alter=20constraint=20cc_1=20= not=20enforced;=20--error=0A+ERROR:=20=20cannot=20mark=20inherited=20= constraint=20"cc_1"=20as=20NOT=20ENFORCED=20because=20matching=20= constraint=20on=20parent=20table=20"parted_ch"=20is=20ENFORCED=0A=20= alter=20table=20parted_ch_2=20alter=20constraint=20cc_2=20not=20= enforced;=0A=20--check=20these=20CHECK=20constraint=20status=20again=0A=20= select=20*=20from=20check_constraint_status;=0A@@=20-457,12=20+461,12=20= @@=20select=20*=20from=20check_constraint_status;=0A=20=20cc=20=20=20=20=20= =20|=20parted_ch_1=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A=20= =20cc=20=20=20=20=20=20|=20parted_ch_11=20|=20t=20=20=20=20=20=20=20=20=20= =20=20|=20t=0A=20=20cc=20=20=20=20=20=20|=20parted_ch_12=20|=20t=20=20=20= =20=20=20=20=20=20=20=20|=20t=0A-=20cc=20=20=20=20=20=20|=20parted_ch_2=20= =20|=20f=20=20=20=20=20=20=20=20=20=20=20|=20f=0A+=20cc=20=20=20=20=20=20= |=20parted_ch_2=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A=20=20= cc_1=20=20=20=20|=20parted_ch=20=20=20=20|=20t=20=20=20=20=20=20=20=20=20= =20=20|=20t=0A=20=20cc_1=20=20=20=20|=20parted_ch_1=20=20|=20t=20=20=20=20= =20=20=20=20=20=20=20|=20t=0A=20=20cc_1=20=20=20=20|=20parted_ch_11=20|=20= t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A=20=20cc_1=20=20=20=20|=20= parted_ch_12=20|=20t=20=20=20=20=20=20=20=20=20=20=20|=20t=0A-=20cc_1=20=20= =20=20|=20parted_ch_2=20=20|=20f=20=20=20=20=20=20=20=20=20=20=20|=20f=0A= +=20cc_1=20=20=20=20|=20parted_ch_2=20=20|=20t=20=20=20=20=20=20=20=20=20= =20=20|=20t=0A=20=20cc_2=20=20=20=20|=20parted_ch_2=20=20|=20f=20=20=20=20= =20=20=20=20=20=20=20|=20f=0A=20(11=20rows)=0A=20=0Adiff=20--git=20= a/src/test/regress/expected/inherit.out=20= b/src/test/regress/expected/inherit.out=0Aindex=20= 3d8e8d8afd2..86e5b892a98=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,60=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=20inherited=20= CHECK=20constraint=20cannot=20be=20NOT=20ENFORCED=20under=20an=20= ENFORCED=20parent=0A+create=20table=20p1(f1=20int=20constraint=20= p1_a_check=20check=20(f1=20>=200)=20enforced);=0A+create=20table=20= p1_c1()=20inherits(p1);=0A+alter=20table=20p1_c1=20alter=20constraint=20= p1_a_check=20not=20enforced;=20--error=0A+ERROR:=20=20cannot=20mark=20= inherited=20constraint=20"p1_a_check"=20as=20NOT=20ENFORCED=20because=20= matching=20constraint=20on=20parent=20table=20"p1"=20is=20ENFORCED=0A= +alter=20table=20p1=20alter=20constraint=20p1_a_check=20not=20enforced;=20= --ok=0A+alter=20table=20p1_c1=20alter=20constraint=20p1_a_check=20not=20= enforced;=20--ok=0A+drop=20table=20p1=20cascade;=0A+NOTICE:=20=20drop=20= cascades=20to=20table=20p1_c1=0A+--=20recursive=20NOT=20ENFORCED=20= merges=20with=20ENFORCED=20constraints=20from=20other=20parents=0A= +create=20table=20p1(a=20int=20constraint=20p1_a_check=20check=20(a=20>=20= 0)=20enforced);=0A+create=20table=20p2(a=20int=20constraint=20p1_a_check=20= check=20(a=20>=200)=20enforced);=0A+create=20table=20p1_c1()=20inherits=20= (p1,=20p2);=0A+NOTICE:=20=20merging=20multiple=20inherited=20definitions=20= of=20column=20"a"=0A+alter=20table=20p1=20alter=20constraint=20= p1_a_check=20not=20enforced;=20--ok=0A+select=20=20conname,=20= conenforced,=20convalidated,=20conrelid::regclass=0A+from=20=20=20=20= pg_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|=20f=20=20=20=20=20=20=20=20=20=20=20|=20f=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+=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|=20p2=0A+(3=20rows)=0A+=0A+alter=20table=20p1_c1=20= alter=20constraint=20p1_a_check=20not=20enforced;=20--error=0A+ERROR:=20=20= cannot=20mark=20inherited=20constraint=20"p1_a_check"=20as=20NOT=20= ENFORCED=20because=20matching=20constraint=20on=20parent=20table=20"p2"=20= is=20ENFORCED=0A+drop=20table=20p1,=20p2=20cascade;=0A+NOTICE:=20=20drop=20= cascades=20to=20table=20p1_c1=0A+--=20recursive=20NOT=20ENFORCED=20can=20= change=20all=20matching=20enforced=20parents=20together=0A+create=20= table=20gp(a=20int=20constraint=20gp_a_check=20check=20(a=20>=200)=20= enforced);=0A+create=20table=20p1()=20inherits=20(gp);=0A+create=20table=20= p2()=20inherits=20(gp);=0A+create=20table=20p1_c1()=20inherits=20(p1,=20= p2);=0A+NOTICE:=20=20merging=20multiple=20inherited=20definitions=20of=20= column=20"a"=0A+alter=20table=20gp=20alter=20constraint=20gp_a_check=20= not=20enforced;=20--ok=0A+select=20=20conname,=20conenforced,=20= convalidated,=20conrelid::regclass=0A+from=20=20=20=20pg_constraint=0A= +where=20=20=20conname=20=3D=20'gp_a_check'=20and=20contype=20=3D=20'c'=0A= +order=20by=20conrelid::regclass::text=20collate=20"C";=0A+=20=20conname=20= =20=20|=20conenforced=20|=20convalidated=20|=20conrelid=20=0A= +------------+-------------+--------------+----------=0A+=20gp_a_check=20= |=20f=20=20=20=20=20=20=20=20=20=20=20|=20f=20=20=20=20=20=20=20=20=20=20= =20=20|=20gp=0A+=20gp_a_check=20|=20f=20=20=20=20=20=20=20=20=20=20=20|=20= f=20=20=20=20=20=20=20=20=20=20=20=20|=20p1=0A+=20gp_a_check=20|=20f=20=20= =20=20=20=20=20=20=20=20=20|=20f=20=20=20=20=20=20=20=20=20=20=20=20|=20= p1_c1=0A+=20gp_a_check=20|=20f=20=20=20=20=20=20=20=20=20=20=20|=20f=20=20= =20=20=20=20=20=20=20=20=20=20|=20p2=0A+(4=20rows)=0A+=0A+drop=20table=20= gp=20cascade;=0A+NOTICE:=20=20drop=20cascades=20to=203=20other=20objects=0A= +DETAIL:=20=20drop=20cascades=20to=20table=20p1=0A+drop=20cascades=20to=20= table=20p2=0A+drop=20cascades=20to=20table=20p1_c1=0A=20--for=20"no=20= inherit"=20check=20constraint,=20it=20will=20not=20recurse=20to=20child=20= table=0A=20create=20table=20p1(f1=20int=20constraint=20p1_a_check=20= check=20(f1=20>=200)=20no=20inherit=20not=20enforced);=0A=20create=20= table=20p1_c1(f1=20int=20constraint=20p1_a_check=20check=20(f1=20>=200)=20= not=20enforced);=0Adiff=20--git=20a/src/test/regress/sql/constraints.sql=20= b/src/test/regress/sql/constraints.sql=0Aindex=20= dc133b124bb..9705962eb9f=20100644=0A---=20= a/src/test/regress/sql/constraints.sql=0A+++=20= b/src/test/regress/sql/constraints.sql=0A@@=20-309,8=20+309,9=20@@=20= select=20*=20from=20check_constraint_status;=0A=20alter=20table=20= parted_ch_2=20alter=20constraint=20cc_2=20enforced;=20--error=0A=20= delete=20from=20parted_ch=20where=20a=20=3D=2016;=0A=20alter=20table=20= parted_ch_2=20alter=20constraint=20cc_2=20enforced;=0A-alter=20table=20= parted_ch_2=20alter=20constraint=20cc=20not=20enforced;=0A-alter=20table=20= parted_ch_2=20alter=20constraint=20cc_1=20not=20enforced;=0A+alter=20= table=20parted_ch_2=20alter=20constraint=20cc=20not=20enforced;=20= --error=0A+alter=20table=20only=20parted_ch_2=20alter=20constraint=20cc=20= not=20enforced;=20--error=0A+alter=20table=20parted_ch_2=20alter=20= constraint=20cc_1=20not=20enforced;=20--error=0A=20alter=20table=20= parted_ch_2=20alter=20constraint=20cc_2=20not=20enforced;=0A=20=0A=20= --check=20these=20CHECK=20constraint=20status=20again=0Adiff=20--git=20= a/src/test/regress/sql/inherit.sql=20b/src/test/regress/sql/inherit.sql=0A= index=208f986904389..3803fb5c769=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,38=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=20inherited=20CHECK=20= constraint=20cannot=20be=20NOT=20ENFORCED=20under=20an=20ENFORCED=20= parent=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=20table=20p1_c1=20alter=20constraint=20p1_a_check=20not=20= enforced;=20--error=0A+alter=20table=20p1=20alter=20constraint=20= p1_a_check=20not=20enforced;=20--ok=0A+alter=20table=20p1_c1=20alter=20= constraint=20p1_a_check=20not=20enforced;=20--ok=0A+drop=20table=20p1=20= cascade;=0A+=0A+--=20recursive=20NOT=20ENFORCED=20merges=20with=20= ENFORCED=20constraints=20from=20other=20parents=0A+create=20table=20p1(a=20= int=20constraint=20p1_a_check=20check=20(a=20>=200)=20enforced);=0A= +create=20table=20p2(a=20int=20constraint=20p1_a_check=20check=20(a=20>=20= 0)=20enforced);=0A+create=20table=20p1_c1()=20inherits=20(p1,=20p2);=0A= +alter=20table=20p1=20alter=20constraint=20p1_a_check=20not=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+alter=20table=20p1_c1=20= alter=20constraint=20p1_a_check=20not=20enforced;=20--error=0A+drop=20= table=20p1,=20p2=20cascade;=0A+=0A+--=20recursive=20NOT=20ENFORCED=20can=20= change=20all=20matching=20enforced=20parents=20together=0A+create=20= table=20gp(a=20int=20constraint=20gp_a_check=20check=20(a=20>=200)=20= enforced);=0A+create=20table=20p1()=20inherits=20(gp);=0A+create=20table=20= p2()=20inherits=20(gp);=0A+create=20table=20p1_c1()=20inherits=20(p1,=20= p2);=0A+alter=20table=20gp=20alter=20constraint=20gp_a_check=20not=20= enforced;=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'gp_a_check'=20and=20contype=20=3D=20'c'=0A+order=20by=20= conrelid::regclass::text=20collate=20"C";=0A+drop=20table=20gp=20= cascade;=0A+=0A=20--for=20"no=20inherit"=20check=20constraint,=20it=20= will=20not=20recurse=20to=20child=20table=0A=20create=20table=20p1(f1=20= int=20constraint=20p1_a_check=20check=20(f1=20>=200)=20no=20inherit=20= not=20enforced);=0A=20create=20table=20p1_c1(f1=20int=20constraint=20= p1_a_check=20check=20(f1=20>=200)=20not=20enforced);=0A--=20=0A2.50.1=20= (Apple=20Git-155)=0A=0A= --Apple-Mail=_B3D63AC8-07A5-4BF4-A40A-97CBD84FD8F5--