public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rushabh Lathia <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
Date: Wed, 12 Feb 2025 16:52:36 +0530
Message-ID: <CAGPqQf0MyqYL_8_ikEyMTE4pKt_692fAwpJvOn1s6rCB_=2B4g@mail.gmail.com> (raw)
In-Reply-To: <CAGPqQf2f11SNZv_ZCi9Z4SR_c4GpmmHsO7eiF2Z2eyibQ=acXg@mail.gmail.com>
References: <CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com>
<[email protected]>
<CAGPqQf21cWsCujW8U90VR4wdr+VEB+LMAwkNjYwS112_gS20pA@mail.gmail.com>
<CAGPqQf2f11SNZv_ZCi9Z4SR_c4GpmmHsO7eiF2Z2eyibQ=acXg@mail.gmail.com>
On Mon, Feb 10, 2025 at 10:48 PM Rushabh Lathia <[email protected]>
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 AM Rushabh Lathia <[email protected]>
> wrote:
>
>>
>>
>> On Fri, Feb 7, 2025 at 4:44 PM Alvaro Herrera <[email protected]>
>> 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: 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.
>>
>>
>>>
>>> --
>>> Álvaro Herrera 48°01'N 7°57'E —
>>> 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
>
--
Rushabh Lathia
Attachments:
[application/octet-stream] 0004-Documentation-and-tab-complete-for-the-NOT-NULL-NOT-.patch (2.5K, ../CAGPqQf0MyqYL_8_ikEyMTE4pKt_692fAwpJvOn1s6rCB_=2B4g@mail.gmail.com/3-0004-Documentation-and-tab-complete-for-the-NOT-NULL-NOT-.patch)
download | inline diff:
From 488d040fca4796b712f8ef9b4a0c61f679a486b6 Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Wed, 12 Feb 2025 16:43:37 +0530
Subject: [PATCH 4/4] Documentation and tab-complete for the NOT NULL NOT
VALID.
---
doc/src/sgml/catalogs.sgml | 6 ++++--
doc/src/sgml/ref/alter_table.sgml | 2 +-
src/bin/psql/tab-complete.in.c | 2 +-
3 files changed, 6 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ee59a7e..c835d21 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1257,10 +1257,12 @@
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>attnotnull</structfield> <type>bool</type>
+ <structfield>attnotnull</structfield> <type>char</type>
</para>
<para>
- This column has a not-null constraint.
+ <literal>t</literal> = not null true,
+ <literal>f</literal> = not null false,
+ <literal>i</literal> = not null invalid,
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8e56b8e..bb85cd7 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -98,7 +98,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
-{ NOT NULL [ NO INHERIT ] |
+{ NOT NULL [ NOT VALID ] [ NO INHERIT ] |
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 5f6897c..c2ba0d5 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2701,7 +2701,7 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
/* ALTER TABLE xxx ADD CONSTRAINT yyy */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "CONSTRAINT", MatchAny))
- COMPLETE_WITH("CHECK", "UNIQUE", "PRIMARY KEY", "EXCLUDE", "FOREIGN KEY");
+ COMPLETE_WITH("CHECK", "UNIQUE", "PRIMARY KEY", "EXCLUDE", "FOREIGN KEY", "NOT NULL");
/* ALTER TABLE xxx ADD [CONSTRAINT yyy] (PRIMARY KEY|UNIQUE) */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "PRIMARY", "KEY") ||
Matches("ALTER", "TABLE", MatchAny, "ADD", "UNIQUE") ||
--
1.8.3.1
view thread (8+ messages) latest in thread
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]
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
In-Reply-To: <CAGPqQf0MyqYL_8_ikEyMTE4pKt_692fAwpJvOn1s6rCB_=2B4g@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