Feedback-ID: i131946ab:Fastmail
Message-ID: <bac841ed-b86d-e3c2-030d-02a3db067307@enterprisedb.com>
Date: Wed, 14 Sep 2022 22:03:50 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.2.2
Subject: Re: cataloguing NOT NULL constraints
Content-Language: en-US
To: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Amit Langote <amitlangote09@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 Pg Hackers <pgsql-hackers@lists.postgresql.org>
References: <20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <20220909175813.vu5kd2m67mrrqkjv@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/bac841ed-b86d-e3c2-030d-02a3db067307%40enterprisedb.com>
Precedence: bulk

On 09.09.22 19:58, Alvaro Herrera wrote:
> There were a lot more problems in that submission than I at first
> realized, and I had to rewrite a lot of code in order to fix them.  I
> have fixed all the user-visible problems I found in this version, and
> reviewed the tests results more carefully so I am now more confident
> that behaviourally it's doing the right thing; but

Reading through the SQL standard again, I think this patch goes a bit 
too far in folding NOT NULL and CHECK constraints together.  The spec 
says that you need to remember whether a column was defined as NOT NULL, 
and that the commands DROP NOT NULL and SET NOT NULL only affect 
constraints defined in that way.  In this implementation, a constraint 
defined as NOT NULL is converted to a CHECK (x IS NOT NULL) constraint 
and the original definition is forgotten.

Besides that, I think that users are not going to like that pg_dump 
rewrites their NOT NULL constraints into CHECK table constraints.

I suspect that this needs a separate contype for NOT NULL constraints 
that is separate from CONSTRAINT_CHECK.