ERROR: no NOT NULL constraint found to drop

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:
/messages/by-id/3863449.1681071102@sss.pgh.pa.us
--
Michael

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

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