Re: Bug of ALTER TABLE DROP CONSTRAINT

Considering the following sequence:
create table t(a int primary key);
alter table t drop constraint t_pkey;
insert into t values(null);
ERROR: null value in column "a" violates not-null constraint

My question is, why "null" is not allowed to be inserted after primary key
constraint has been dropped.

On Thu, Apr 2, 2009 at 3:25 AM, Jacky Leng <lengjianquan(at)163(dot)com> wrote:
> Considering the following sequence:
>    create table t(a int primary key);
>    alter table t drop constraint t_pkey;
>    insert into t values(null);
>    ERROR:  null value in column "a" violates not-null constraint
>
> My question is, why "null" is not allowed to be inserted after primary key
> constraint has been dropped.

Making a column into the primary key forces the column to NOT NULL.
You'll need to DROP NOT NULL separately.

It's probably possible to beat on the code hard enough to fix this,
but I'm not really sure there's much point, since the situation is
rare and the workaround is easy.

...Robert

Hi,

> > Considering the following sequence:
> > create table t(a int primary key);
> > alter table t drop constraint t_pkey;
> > insert into t values(null);
> > ERROR: null value in column "a" violates not-null constraint
> >
> > My question is, why "null" is not allowed to be inserted after primary
> key
> > constraint has been dropped.
>
> Making a column into the primary key forces the column to NOT NULL.
> You'll need to DROP NOT NULL separately.
>
> It's probably possible to beat on the code hard enough to fix this,

Yeah it will be a matter of finding the affected column entries and invoking
the removal of the not null entry from their corresponding pg_attribute
rows.

> but I'm not really sure there's much point, since the situation is
> rare and the workaround is easy.
>

Yeah and it is documented already. Although it is not obvious immediately
that a not-null attribute gets tagged onto the involved columns separately
for primary, unique-not-null types of constraints.

Regards,
Nikhils
--
http://www.enterprisedb.com

On Thu, Apr 2, 2009 at 8:24 AM, Nikhil Sontakke
<nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:
> Hi,
>>
>> > Considering the following sequence:
>> >    create table t(a int primary key);
>> >    alter table t drop constraint t_pkey;
>> >    insert into t values(null);
>> >    ERROR:  null value in column "a" violates not-null constraint
>> >
>> > My question is, why "null" is not allowed to be inserted after primary
>> > key
>> > constraint has been dropped.
>>
>> Making a column into the primary key forces the column to NOT NULL.
>> You'll need to DROP NOT NULL separately.
>>
>> It's probably possible to beat on the code hard enough to fix this,
>
> Yeah it will be a matter of finding the affected column entries and invoking
> the removal of the not null entry from their corresponding pg_attribute
> rows.

Actually it's more complicated than that. You'd need to remember
whether or not the NOT NULL was added when the primary key was added,
or whether it was there before, and only drop it if it wasn't there
before.

...Robert

Hi,

> >>
> >> Making a column into the primary key forces the column to NOT NULL.
> >> You'll need to DROP NOT NULL separately.
> >>
> >> It's probably possible to beat on the code hard enough to fix this,
> >
> > Yeah it will be a matter of finding the affected column entries and
> invoking
> > the removal of the not null entry from their corresponding pg_attribute
> > rows.
>
> Actually it's more complicated than that. You'd need to remember
> whether or not the NOT NULL was added when the primary key was added,
> or whether it was there before, and only drop it if it wasn't there
> before.
>

Hmm, and maybe that is the reason why this is not clubbed with the removal
of the primary constraint. Otherwise it seems to be a matter of decompiling
the conkey and generating AT_DropNotNull nodes for the involved columns
within ATPrepCmd and the rest should happen automatically. So I guess we can
let it be.

Regards,
Nikhils
--
http://www.enterprisedb.com

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Actually it's more complicated than that. You'd need to remember
> whether or not the NOT NULL was added when the primary key was added,
> or whether it was there before, and only drop it if it wasn't there
> before.

We've discussed before the idea that NOT NULL constraints should be
explicitly represented in pg_constraint, just like general CHECK
constraints (this would allow them to be named, have sane inheritance
behavior, etc). If we had that, then pg_attribute.attnotnull could
indicate the OR of "there is a NOT NULL on this column" and "there is
a pkey constraint on this column", and you'd just have to recompute it
properly after dropping either kind of constraint.

Not happening for 8.4, but maybe someday someone will get around to it.

regards, tom lane

Hi,

> We've discussed before the idea that NOT NULL constraints should be
> explicitly represented in pg_constraint, just like general CHECK
> constraints (this would allow them to be named, have sane inheritance
> behavior, etc). If we had that, then pg_attribute.attnotnull could
> indicate the OR of "there is a NOT NULL on this column" and "there is
> a pkey constraint on this column", and you'd just have to recompute it
> properly after dropping either kind of constraint.
>
> Not happening for 8.4, but maybe someday someone will get around to it.
>

Warrants an entry in the TODO items list:

* make NOT NULL constraints have pg_constraint entries, just like CHECK
constraints

Regards,
Nikhils
--
http://www.enterprisedb.com

Nikhil Sontakke wrote:
> Hi,
>
>
> > We've discussed before the idea that NOT NULL constraints should be
> > explicitly represented in pg_constraint, just like general CHECK
> > constraints (this would allow them to be named, have sane inheritance
> > behavior, etc). If we had that, then pg_attribute.attnotnull could
> > indicate the OR of "there is a NOT NULL on this column" and "there is
> > a pkey constraint on this column", and you'd just have to recompute it
> > properly after dropping either kind of constraint.
> >
> > Not happening for 8.4, but maybe someday someone will get around to it.
> >
>
> Warrants an entry in the TODO items list:
>
> * make NOT NULL constraints have pg_constraint entries, just like CHECK
> constraints

This is now a TODO item (I just updated the description):

Store the constraint names of NOT NULL constraints

Currently NOT NULL constraints are stored in pg_attribute without
any designation of their origins, e.g. primary keys. One manifest
problem is that dropping a PRIMARY KEY constraint does not remove the
NOT NULL constraint designation.

* http://archives.postgresql.org/message-id/19768.1238680878@sss.pgh.pa.us

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Nikhil Sontakke wrote:
>> Warrants an entry in the TODO items list:
>>
>> * make NOT NULL constraints have pg_constraint entries, just like CHECK
>> constraints

> This is now a TODO item (I just updated the description):

> Store the constraint names of NOT NULL constraints

I was intending to do that yesterday, but lost interest after
discovering how many duplicate, obsolete, and/or mutually contradictory
TODO entries there are related to constraints. That needs to be cleaned
up and consolidated sometime.

Also, what is wrong with the precise statement of the TODO item that
Nikhil gave? The one you gave would encourage someone to waste time on
a 100% wrong implementation (like adding a constraint name column to
pg_attribute).

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Nikhil Sontakke wrote:
> >> Warrants an entry in the TODO items list:
> >>
> >> * make NOT NULL constraints have pg_constraint entries, just like CHECK
> >> constraints
>
> > This is now a TODO item (I just updated the description):
>
> > Store the constraint names of NOT NULL constraints
>
> I was intending to do that yesterday, but lost interest after
> discovering how many duplicate, obsolete, and/or mutually contradictory
> TODO entries there are related to constraints. That needs to be cleaned
> up and consolidated sometime.
>
> Also, what is wrong with the precise statement of the TODO item that
> Nikhil gave? The one you gave would encourage someone to waste time on
> a 100% wrong implementation (like adding a constraint name column to
> pg_attribute).

I felt the original wording would be unclear in explaining the problem
behavior; you are right the original wording was clearer for correcting
the problem. This updated wording works for both audiences, I think:

Move NOT NULL constraint information to pg_constraint

Currently NOT NULL constraints are stored in pg_attribute without
any designation of their origins, e.g. primary keys. One manifest
problem is that dropping a PRIMARY KEY constraint does not remove the
NOT NULL constraint designation.

*
http://archives.postgresql.org/message-id/19768.1238680878@sss.pgh.pa.us

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +