From 42034630be35f103c903e6b591a8977d9c1afeb7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 24 Apr 2008 20:18:23 +0000 Subject: [PATCH] Fix ALTER TABLE ADD COLUMN ... PRIMARY KEY so that the new column is correctly checked to see if it's been initialized to all non-nulls. The implicit NOT NULL constraint was not being checked during the ALTER (in fact, not even if there was an explicit NOT NULL too), because ATExecAddColumn neglected to set the flag needed to make the test happen. This has been broken since the capability was first added, in 8.0. Brendan Jurd, per a report from Kaloyan Iliev. --- src/backend/commands/tablecmds.c | 5 +++++ src/test/regress/expected/alter_table.out | 20 ++++++++++++++++---- src/test/regress/sql/alter_table.sql | 18 ++++++++++++++---- 3 files changed, 35 insertions(+), 8 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index a70a0d7983..dd716dddb4 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -3135,6 +3135,11 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel, tab->newvals = lappend(tab->newvals, newval); } + /* + * If the new column is NOT NULL, tell Phase 3 it needs to test that. + */ + tab->new_notnull |= colDef->is_not_null; + /* * Add needed dependency entries for the new column. */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 787340ddb5..859a440627 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -318,7 +318,7 @@ drop table atacc3; drop table atacc2; drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" @@ -378,7 +378,7 @@ insert into atacc1 (test2, test) values (2, 3); ERROR: duplicate key violates unique constraint "atacc1_test_key" drop table atacc1; -- test primary key constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" @@ -429,6 +429,18 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 primary key (test1); ERROR: column "test1" named in key does not exist drop table atacc1; +-- adding a new column as primary key to a non-empty table. +-- should fail unless the column has a non-null default value. +create table atacc1 ( test int ); +insert into atacc1 (test) values (0); +-- add a primary key column without a default (fails). +alter table atacc1 add column test2 int primary key; +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +ERROR: column "test2" contains null values +-- now add a primary key column with a default (succeeds). +alter table atacc1 add column test2 int default 0 primary key; +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a primary key constraint @@ -476,7 +488,7 @@ alter table non_existent alter column bar drop not null; ERROR: relation "non_existent" does not exist -- test setting columns to null and not null and vice versa -- test checking for null values and primary key -create table atacc1 (test int not null); +create table atacc1 (test int not null) with oids; alter table atacc1 add constraint "atacc1_pkey" primary key (test); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" alter table atacc1 alter column test drop not null; @@ -598,7 +610,7 @@ ERROR: permission denied: "pg_class" is a system catalog alter table nosuchtable drop column bar; ERROR: relation "nosuchtable" does not exist -- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; insert into atacc1 values (1, 2, 3, 4); alter table atacc1 drop a; alter table atacc1 drop a; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 6b94e14ec8..311ff01fd8 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -348,7 +348,7 @@ drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -- insert first value @@ -402,7 +402,7 @@ drop table atacc1; -- test primary key constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); -- insert first value @@ -447,6 +447,16 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 primary key (test1); drop table atacc1; +-- adding a new column as primary key to a non-empty table. +-- should fail unless the column has a non-null default value. +create table atacc1 ( test int ); +insert into atacc1 (test) values (0); +-- add a primary key column without a default (fails). +alter table atacc1 add column test2 int primary key; +-- now add a primary key column with a default (succeeds). +alter table atacc1 add column test2 int default 0 primary key; +drop table atacc1; + -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a primary key constraint @@ -485,7 +495,7 @@ alter table non_existent alter column bar drop not null; -- test setting columns to null and not null and vice versa -- test checking for null values and primary key -create table atacc1 (test int not null); +create table atacc1 (test int not null) with oids; alter table atacc1 add constraint "atacc1_pkey" primary key (test); alter table atacc1 alter column test drop not null; alter table atacc1 drop constraint "atacc1_pkey"; @@ -582,7 +592,7 @@ alter table pg_class drop column relname; alter table nosuchtable drop column bar; -- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; insert into atacc1 values (1, 2, 3, 4); alter table atacc1 drop a; alter table atacc1 drop a; -- 2.39.5