From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
cc: Richard Guo <guofenglinux@gmail.com>,
        Alvaro Herrera <alvherre@alvh.no-ip.org>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: ERROR: no NOT NULL constraint found to drop
In-reply-to: <ZDPA8BJ4YgZymdoY@paquier.xyz>
References: 
 <CAMbWs48astPDb3K+L89wb8Yju0jM_Czm8svmU=Uzd+WM61Cr6Q@mail.gmail.com>
 <ZDPA8BJ4YgZymdoY@paquier.xyz>
Comments: In-reply-to Michael Paquier <michael@paquier.xyz>
	message dated "Mon, 10 Apr 2023 16:55:28 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4031604.1681134419.1@sss.pgh.pa.us>
Date: Mon, 10 Apr 2023 09:46:59 -0400
Message-ID: <4031605.1681134419@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4031605.1681134419%40sss.pgh.pa.us>
Precedence: bulk

Michael Paquier <michael@paquier.xyz> writes:
> On Mon, Apr 10, 2023 at 03:16:06PM +0800, Richard Guo wrote:
>> I run into error $subject with the query below:
>> # create table t1 (c int primary key null unique);
>> CREATE TABLE
>> # create table t2 (like t1);
>> CREATE TABLE
>> # alter table t2 alter c drop not null;
>> ERROR:  no NOT NULL constraint found to drop

> Thanks for the report.  This is not the only issue that has been
> pointed out with this patch, so it is going to be reverted if you look
> around here:

It's still good to know about it for next time.  The issue I guess is
that LIKE with no options propagates column attnotnull bits, but not
constraints, so we now have an inconsistency: t2.c has attnotnull set
but there is nothing in pg_constraint to justify it.  It seems to me
we're going to have to think about what we want to happen in this
case.  In a green field we'd probably not propagate NOT NULL unless
told to copy constraints ... but is it okay to break functional
compatibility with the old behavior?

			regards, tom lane