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.94.2) (envelope-from ) id 1tiApR-00CNHe-VC for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Feb 2025 11:22:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tiApQ-008wig-0r for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Feb 2025 11:22:52 +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.94.2) (envelope-from ) id 1tiApP-008wiY-JC for pgsql-hackers@lists.postgresql.org; Wed, 12 Feb 2025 11:22:52 +0000 Received: from mail-vk1-xa2a.google.com ([2607:f8b0:4864:20::a2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tiApO-000OxL-1K for pgsql-hackers@postgresql.org; Wed, 12 Feb 2025 11:22:51 +0000 Received: by mail-vk1-xa2a.google.com with SMTP id 71dfb90a1353d-5203017d0c2so739303e0c.0 for ; Wed, 12 Feb 2025 03:22:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739359369; x=1739964169; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=r5M31YbegxQat2sHpplmttFrLqZjwCFC8wceS/vDJCo=; b=NDr9oRY7vZOrSkaM6JtqX5EF9bpDqz/w+T/3sTE39aSOKV0QakQUlemCfATWE9owEy zmNNxfrtTW/bllLs1EtXHZHqirXH3VWZDl5Xvl9xuI/v5oGmG3CyG2auRhH7QOlEvMNl +qB6EGOfHvbSwXJ7A+hfevsT+n4/ysQR0Cy7AE5ILcXkgTJqmLPwfw/Lv5c7Fi444ekT TajF1L9LAPFWuhNg93HlUqyW2RwuMjdQ8+L6gNy65sL2pqAVYkiTGa0zlG5JPOfEmgiu YzIuAF2yLlPCsqPKUH705NryvnkyO9FssZZ1a3ZEAwpxRXKVt/WpXllrib/jZc0x+ZoN M21w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739359369; x=1739964169; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=r5M31YbegxQat2sHpplmttFrLqZjwCFC8wceS/vDJCo=; b=wKqO3PrLlMoux3dt8opNXBBjOzsAFyCUan2mmHLS1qgz/OjereJjFMynYOC9mnNAxN ALcdS+ZzsxriJwm0ECqEH+1cPndYkGye1GolM7qV/lkHMDBscZrZY46DioKkwuCadGnu EyCgPf1qOAPCEN7T2UAqmCuYnJN4OdyX8Y5bJdgVXnaYA8RrSlV8ltBC5ZH9dIsNiQSY hLeKspzHvxGPYMvdj4JpWcxpR8CU0r36rp0nFusScaG5RNuFhF0qqlV5ISsTyhFDdd4K nnwy0ZzsBXWXkQxWbrVdPsMEj7pYjCZLTt5KtQKo6i7iQiE1fLbNKdih1ouaipSIigqn Oy1A== X-Gm-Message-State: AOJu0Yyv0p7/5bB58yz+XfPpr/+Cm+xyUOnZJLcOuNJyAHXOhkEbJV4v H7Ya4RAWANSbxeWbUlEoUqyWKBO5u7UQNJCZlGCNnmF4m/XMJSFMcDYAHSAXYlSybym9exGGcl0 3qUGCv+MfTPNwcNhe9frwGCwYSw== X-Gm-Gg: ASbGncvXGW1zSHbrwJcNrCG1yA9GdBvuuka8YVQAmJbj9/kEyqFvtc4NpphuNULdE8a QKrnfaxDbPpnOltdd31+IJ2m4q1IYRby+NxaVOZRXi9Jl4KwY4Vui5XZSmVO8dqbWvC/T8FW4 X-Google-Smtp-Source: AGHT+IGGHCjIWbw6OnWePFl3/WHvURucyogdhT7Z+adCRGqhfvI8UYPr4/yMLrS73tL52+b6t8RmD5IFiwRuqnx3qJo= X-Received: by 2002:a05:6122:2021:b0:520:5a87:66ed with SMTP id 71dfb90a1353d-52067c5cd49mr2027786e0c.5.1739359369366; Wed, 12 Feb 2025 03:22:49 -0800 (PST) MIME-Version: 1.0 References: <202502071114.kosxwvgyblj7@alvherre.pgsql> In-Reply-To: From: Rushabh Lathia Date: Wed, 12 Feb 2025 16:52:36 +0530 X-Gm-Features: AWEUYZn7o0kc42E6VAkyf_ANGlUTx_2p5kuuLwuwrla0zfwIvrxechZlsFhWn4k Message-ID: Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints To: Alvaro Herrera Cc: PostgreSQL Hackers Content-Type: multipart/mixed; boundary="00000000000059f9b3062df0296e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000059f9b3062df0296e Content-Type: multipart/alternative; boundary="00000000000059f9b0062df0296c" --00000000000059f9b0062df0296c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 10, 2025 at 10:48=E2=80=AFPM Rushabh Lathia wrote: > Hi Alvaro, > > I have incorporated the suggested changes, and here is the latest version > of the patch: > > - Added more test cases to the regression suite. > - Included tests in the pg_dump test. > - Left objects with *INVALID NOT NULL* for pg_upgrade. > - Fixed an issue where recursion to child tables was incorrectly > attempting to locate the constraint by name. > - Introduced a new function, QueueNNConstraintValidation(), for > handling *NOT NULL* constraints. > > The only remaining task for this patch is updating the documentation. I > will work on that and submit the final version soon. > Attaching documentation and tab-complete patch here. > Please share your review comments. > Thanks, > > > On Mon, Feb 10, 2025 at 12:28=E2=80=AFAM Rushabh Lathia > wrote: > >> >> >> On Fri, Feb 7, 2025 at 4:44=E2=80=AFPM Alvaro Herrera >> wrote: >> >>> >>> Recursion to child tables is incorrectly trying to locate the constrain= t >>> by name: >>> >>> create table notnull_tbl1 (a int); >>> alter table notnull_tbl1 add constraint foo not null a not valid; >>> create table notnull_chld (a int); >>> alter table notnull_chld add constraint blah not null a not valid; >>> alter table notnull_chld inherit notnull_tbl1 ; >>> >>> -- this fails but shouldn't: >>> alter table notnull_tbl1 validate constraint foo; >>> ERROR: constraint "foo" of relation "notnull_chld" does not exist >>> >>> The end result here should be that the constraint `blah` in table >>> notnull_chld is marked as validated. >>> >> >> Yes, I agree. Here we need a separate Queue for NotNull constraint >> validation, >> which fetches the respective Non-Validate-Not-Null constraint name from >> the child table >> >> I am working on the patch and will post the update patch soon. >> >> >>> >>> -- >>> =C3=81lvaro Herrera 48=C2=B001'N 7=C2=B057'E =E2=80=94 >>> https://www.EnterpriseDB.com/ >>> Syntax error: function hell() needs an argument. >>> Please choose what hell you want to involve. >>> >> >> >> -- >> Rushabh Lathia >> > > > -- > Rushabh Lathia > www.EnterpriseDB.com > --=20 Rushabh Lathia --00000000000059f9b0062df0296c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, Feb 10,= 2025 at 10:48=E2=80=AFPM Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
Hi = Alvaro,

I have incorporated the suggested changes, and here is the l= atest version of the patch:

  • Added more test cases to the regress= ion suite.
  • Included tests in the pg_dump test.
    • Left objects with INVALID NOT NULL for pg_upgrade.
  • Fixed an issue where recursion to child tables was incorrect= ly attempting to locate the constraint by name.
  • Introduced a new fu= nction, QueueNNConstraintValidation(), for handling NO= T NULL constraints.

The only remaining task for this p= atch is updating the documentation. I will work on that and submit the fina= l version soon.


Attac= hing documentation and tab-complete patch here.

= =C2=A0

Please share your review comments.

Thanks,


On Mon, Feb 10, 2025 at 12:28=E2=80=AFAM Rus= habh Lathia <rushabh.lathia@gmail.com> wrote:


=
On Fri, Fe= b 7, 2025 at 4:44=E2=80=AFPM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Recursion to child tables is incorrectly trying to locate the constraint by name:

create table notnull_tbl1 (a int);
alter table notnull_tbl1 add constraint foo not null a not valid;
create table notnull_chld (a int);
alter table notnull_chld add constraint blah not null a not valid;
alter table notnull_chld inherit notnull_tbl1 ;

-- this fails but shouldn't:
alter table notnull_tbl1 validate constraint foo;
ERROR:=C2=A0 constraint "foo" of relation "notnull_chld"= ; does not exist

The end result here should be that the constraint `blah` in table
notnull_chld is marked as validated.

Ye= s, I agree.=C2=A0 Here we need a separate Queue for NotNull constraint vali= dation,
which fetches the respective Non-Validate-Not-Null constr= aint name from
the child table

I am work= ing on the patch and will post the update patch soon.
=C2=A0

--
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A04= 8=C2=B001'N 7=C2=B057'E=C2=A0 =E2=80=94=C2=A0 https://www.Enter= priseDB.com/
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.


--
Rushabh Lathia


--
Rushabh Lathia
www.EnterpriseDB.com
<= /div>


--
Rushabh Lathia
--00000000000059f9b0062df0296c-- --00000000000059f9b3062df0296e Content-Type: application/octet-stream; name="0004-Documentation-and-tab-complete-for-the-NOT-NULL-NOT-.patch" Content-Disposition: attachment; filename="0004-Documentation-and-tab-complete-for-the-NOT-NULL-NOT-.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_m71tn7qs0 RnJvbSA0ODhkMDQwZmNhNDc5NmI3MTJmOGVmOWI0YTBjNjFmNjc5YTQ4NmI2IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBSdXNoYWJoIExhdGhpYSA8cnVzaGFiaC5sYXRoaWFAZW50ZXJw cmlzZWRiLmNvbT4KRGF0ZTogV2VkLCAxMiBGZWIgMjAyNSAxNjo0MzozNyArMDUzMApTdWJqZWN0 OiBbUEFUQ0ggNC80XSBEb2N1bWVudGF0aW9uIGFuZCB0YWItY29tcGxldGUgZm9yIHRoZSBOT1Qg TlVMTCBOT1QKIFZBTElELgoKLS0tCiBkb2Mvc3JjL3NnbWwvY2F0YWxvZ3Muc2dtbCAgICAgICAg fCA2ICsrKystLQogZG9jL3NyYy9zZ21sL3JlZi9hbHRlcl90YWJsZS5zZ21sIHwgMiArLQogc3Jj L2Jpbi9wc3FsL3RhYi1jb21wbGV0ZS5pbi5jICAgIHwgMiArLQogMyBmaWxlcyBjaGFuZ2VkLCA2 IGluc2VydGlvbnMoKyksIDQgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21s L2NhdGFsb2dzLnNnbWwgYi9kb2Mvc3JjL3NnbWwvY2F0YWxvZ3Muc2dtbAppbmRleCBlZTU5YTdl Li5jODM1ZDIxIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvY2F0YWxvZ3Muc2dtbAorKysgYi9k b2Mvc3JjL3NnbWwvY2F0YWxvZ3Muc2dtbApAQCAtMTI1NywxMCArMTI1NywxMiBAQAogCiAgICAg IDxyb3c+CiAgICAgICA8ZW50cnkgcm9sZT0iY2F0YWxvZ190YWJsZV9lbnRyeSI+PHBhcmEgcm9s ZT0iY29sdW1uX2RlZmluaXRpb24iPgotICAgICAgIDxzdHJ1Y3RmaWVsZD5hdHRub3RudWxsPC9z dHJ1Y3RmaWVsZD4gPHR5cGU+Ym9vbDwvdHlwZT4KKyAgICAgICA8c3RydWN0ZmllbGQ+YXR0bm90 bnVsbDwvc3RydWN0ZmllbGQ+IDx0eXBlPmNoYXI8L3R5cGU+CiAgICAgICA8L3BhcmE+CiAgICAg ICA8cGFyYT4KLSAgICAgICBUaGlzIGNvbHVtbiBoYXMgYSBub3QtbnVsbCBjb25zdHJhaW50Lgor ICAgICAgIDxsaXRlcmFsPnQ8L2xpdGVyYWw+ID0gbm90IG51bGwgdHJ1ZSwKKyAgICAgICA8bGl0 ZXJhbD5mPC9saXRlcmFsPiA9IG5vdCBudWxsIGZhbHNlLAorICAgICAgIDxsaXRlcmFsPmk8L2xp dGVyYWw+ID0gbm90IG51bGwgaW52YWxpZCwKICAgICAgIDwvcGFyYT48L2VudHJ5PgogICAgICA8 L3Jvdz4KIApkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3JlZi9hbHRlcl90YWJsZS5zZ21sIGIv ZG9jL3NyYy9zZ21sL3JlZi9hbHRlcl90YWJsZS5zZ21sCmluZGV4IDhlNTZiOGUuLmJiODVjZDcg MTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9yZWYvYWx0ZXJfdGFibGUuc2dtbAorKysgYi9kb2Mv c3JjL3NnbWwvcmVmL2FsdGVyX3RhYmxlLnNnbWwKQEAgLTk4LDcgKzk4LDcgQEAgV0lUSCAoIE1P RFVMVVMgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPm51bWVyaWNfbGl0ZXJhbDwvcmVw bGFjZWFibGU+LCBSRU0KIDxwaHJhc2U+YW5kIDxyZXBsYWNlYWJsZSBjbGFzcz0icGFyYW1ldGVy Ij5jb2x1bW5fY29uc3RyYWludDwvcmVwbGFjZWFibGU+IGlzOjwvcGhyYXNlPgogCiBbIENPTlNU UkFJTlQgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPmNvbnN0cmFpbnRfbmFtZTwvcmVw bGFjZWFibGU+IF0KLXsgTk9UIE5VTEwgWyBOTyBJTkhFUklUIF0gfAoreyBOT1QgTlVMTCBbIE5P VCBWQUxJRCBdIFsgTk8gSU5IRVJJVCBdIHwKICAgTlVMTCB8CiAgIENIRUNLICggPHJlcGxhY2Vh YmxlIGNsYXNzPSJwYXJhbWV0ZXIiPmV4cHJlc3Npb248L3JlcGxhY2VhYmxlPiApIFsgTk8gSU5I RVJJVCBdIHwKICAgREVGQVVMVCA8cmVwbGFjZWFibGU+ZGVmYXVsdF9leHByPC9yZXBsYWNlYWJs ZT4gfApkaWZmIC0tZ2l0IGEvc3JjL2Jpbi9wc3FsL3RhYi1jb21wbGV0ZS5pbi5jIGIvc3JjL2Jp bi9wc3FsL3RhYi1jb21wbGV0ZS5pbi5jCmluZGV4IDVmNjg5N2MuLmMyYmEwZDUgMTAwNjQ0Ci0t LSBhL3NyYy9iaW4vcHNxbC90YWItY29tcGxldGUuaW4uYworKysgYi9zcmMvYmluL3BzcWwvdGFi LWNvbXBsZXRlLmluLmMKQEAgLTI3MDEsNyArMjcwMSw3IEBAIG1hdGNoX3ByZXZpb3VzX3dvcmRz KGludCBwYXR0ZXJuX2lkLAogCQlDT01QTEVURV9XSVRIX1NDSEVNQV9RVUVSWShRdWVyeV9mb3Jf bGlzdF9vZl9kYXRhdHlwZXMpOwogCS8qIEFMVEVSIFRBQkxFIHh4eCBBREQgQ09OU1RSQUlOVCB5 eXkgKi8KIAllbHNlIGlmIChNYXRjaGVzKCJBTFRFUiIsICJUQUJMRSIsIE1hdGNoQW55LCAiQURE IiwgIkNPTlNUUkFJTlQiLCBNYXRjaEFueSkpCi0JCUNPTVBMRVRFX1dJVEgoIkNIRUNLIiwgIlVO SVFVRSIsICJQUklNQVJZIEtFWSIsICJFWENMVURFIiwgIkZPUkVJR04gS0VZIik7CisJCUNPTVBM RVRFX1dJVEgoIkNIRUNLIiwgIlVOSVFVRSIsICJQUklNQVJZIEtFWSIsICJFWENMVURFIiwgIkZP UkVJR04gS0VZIiwgIk5PVCBOVUxMIik7CiAJLyogQUxURVIgVEFCTEUgeHh4IEFERCBbQ09OU1RS QUlOVCB5eXldIChQUklNQVJZIEtFWXxVTklRVUUpICovCiAJZWxzZSBpZiAoTWF0Y2hlcygiQUxU RVIiLCAiVEFCTEUiLCBNYXRjaEFueSwgIkFERCIsICJQUklNQVJZIiwgIktFWSIpIHx8CiAJCQkg TWF0Y2hlcygiQUxURVIiLCAiVEFCTEUiLCBNYXRjaEFueSwgIkFERCIsICJVTklRVUUiKSB8fAot LSAKMS44LjMuMQoK --00000000000059f9b3062df0296e--