Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT NULL Fixes

NOT NULL Fixes

From
Rod Taylor
Date:
Enforces NOT NULL constraints to be applied against new PRIMARY KEY
columns in DefineIndex.  So, ALTER TABLE ... PRIMARY KEY will now
automatically add the NOT NULL constraint.  It appeared the alter_table
regression test wanted this to occur, as after the change the regression
test better matched in inline 'fails'/'succeeds' comments.

At first glance, I didn't see any documentation that said this did not
happen.

It's not as efficient as it could be for multi-key primary keys, but
it's certainly better than the old method.  Besides, how often do you
alter in a multi-key primary key on a table with lots of data.


It also corrects the inheritance case:

b=# create table a (aa int4);
CREATE TABLE
b=# create table b (bb int4, primary key(aa, bb)) inherits (a);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey'
for table 'b'
CREATE TABLE
b=# \d b
       Table "public.b"
 Column |  Type   | Modifiers
--------+---------+-----------
 aa     | integer | not null
 bb     | integer | not null
Indexes: b_pkey primary key btree (aa, bb)


The NOT NULL clause will NOT cascade to children at the moment.  Perhaps
this should be the other way around?

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment

Re: NOT NULL Fixes

From
Bruce Momjian
Date:
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Rod Taylor wrote:
-- Start of PGP signed section.
> Enforces NOT NULL constraints to be applied against new PRIMARY KEY
> columns in DefineIndex.  So, ALTER TABLE ... PRIMARY KEY will now
> automatically add the NOT NULL constraint.  It appeared the alter_table
> regression test wanted this to occur, as after the change the regression
> test better matched in inline 'fails'/'succeeds' comments.
>
> At first glance, I didn't see any documentation that said this did not
> happen.
>
> It's not as efficient as it could be for multi-key primary keys, but
> it's certainly better than the old method.  Besides, how often do you
> alter in a multi-key primary key on a table with lots of data.
>
>
> It also corrects the inheritance case:
>
> b=# create table a (aa int4);
> CREATE TABLE
> b=# create table b (bb int4, primary key(aa, bb)) inherits (a);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey'
> for table 'b'
> CREATE TABLE
> b=# \d b
>        Table "public.b"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  aa     | integer | not null
>  bb     | integer | not null
> Indexes: b_pkey primary key btree (aa, bb)
>
>
> The NOT NULL clause will NOT cascade to children at the moment.  Perhaps
> this should be the other way around?
>
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: NOT NULL Fixes

From
Tom Lane
Date:
Rod Taylor <rbt@rbt.ca> writes:
> Enforces NOT NULL constraints to be applied against new PRIMARY KEY
> columns in DefineIndex.  So, ALTER TABLE ... PRIMARY KEY will now
> automatically add the NOT NULL constraint.  It appeared the alter_table
> regression test wanted this to occur, as after the change the regression
> test better matched in inline 'fails'/'succeeds' comments.

Committed with a few editorializations.

> The NOT NULL clause will NOT cascade to children at the moment.  Perhaps
> this should be the other way around?

I left that as-is, but I'm dubious about it too.  Ordinarily you'd
expect a NOT NULL constraint on a parent table to propagate to children
as well.  OTOH, since the PRIMARY KEY constraint itself doesn't
propagate to children, arguably it's more consistent to act this way
(at least until we tackle cross-table indexes).

It's a one-liner code change if we want it to act the other way, so I
figure we can put the main fix in now and argue about this detail later.

            regards, tom lane