Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: no NOT NULL constraint found to drop

ERROR: no NOT NULL constraint found to drop

From
Richard Guo
Date:
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

This starts since e056c557ae.  I guess this shouldn't happen since the
comment says so.

        /* this shouldn't happen */
        elog(ERROR, "no NOT NULL constraint found to drop");

Thanks
Richard

Re: ERROR: no NOT NULL constraint found to drop

From
Michael Paquier
Date:
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
>
> This starts since e056c557ae.  I guess this shouldn't happen since the
> comment says so.
>
>         /* this shouldn't happen */
>         elog(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:
https://www.postgresql.org/message-id/3863449.1681071102@sss.pgh.pa.us
--
Michael
Attachment

Re: ERROR: no NOT NULL constraint found to drop

From
Tom Lane
Date:
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

Re: ERROR: no NOT NULL constraint found to drop

From
Michael Paquier
Date:
On Mon, Apr 10, 2023 at 09:46:59AM -0400, Tom Lane wrote:
> 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?

I am not sure about that, TBH, though I would tend to not break
compatibility just for the sake of breaking it.  Anyway, shouldn't we
have a test that does a DROP NOT NULL after a LIKE copies it?  At
least, we'll be able to track that.
--
Michael
Attachment