-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
The attached patch resolves the bug reported by Kaloyan Iliev [1] on -general.
The problem occurred when executing ALTER TABLE ... ADD COLUMN ...
PRIMARY KEY with no default clause, on a table with rows already
present. The NOT NULL constraint was not enforced. The bug has been
observed on (at least) 8.2, 8.3 and CVS HEAD.
The fix was to force evaluation of the NOT NULL constraint in
ATExecAddColumn in all cases where the parser indicated that the
column ought to be NOT NULL.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org
iD8DBQFIENpl5YBsbHkuyV0RApP6AKDdnZAA0LjSzh9XdiQt7K/cx4YOcQCgydHr
6BYSD2kVX2DP7LYgDHvzNdE=
=Yrmp
-END PGP SIGNATURE-
On Fri, Apr 25, 2008 at 3:46 AM, Tom Lane [EMAIL PROTECTED] wrote:
alter table t1 add column f2 int not null;
transformAlterTableStmt will produce an AT_AddColumn subcommand
containing a ColumnDef with is_not_null = false, followed by an
AT_SetNotNull subcommand. But for
I realised that there's no reason for preparing a separate SetNotNull
subcommand anymore, now that ATExecAddColumn takes care of enforcing
the constraint, so I removed this special case.
I've also added a regression test for ADD COLUMN PRIMARY KEY.
Cheers,
BJ
[1] http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
*** src/backend/commands/tablecmds.c
--- src/backend/commands/tablecmds.c
***
*** ,3338 ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
--- ,3345
}
/*
+* Tell Phase 3 to perform the check for NULL values in the new column
if
+* its attnotnull bit has been set to true.
+*/
+ if (attribute-attnotnull)
+ tab-new_notnull = true;
+
+ /*
* Add needed dependency entries for the new column.
*/
add_column_datatype_dependency(myrelid, i, attribute-atttypid);
*** src/backend/parser/parse_utilcmd.c
--- src/backend/parser/parse_utilcmd.c
***
*** 1726,1753 transformAlterTableStmt(AlterTableStmt *stmt, const char
*queryString)
* If the column has a non-null
default, we can't skip
* validation of foreign keys.
*/
! if (((ColumnDef *)
cmd-def)-raw_default != NULL)
skipValidation = false;
newcmds = lappend(newcmds, cmd);
/*
-* Convert an ADD COLUMN ... NOT NULL
constraint to a
-* separate command
-*/
- if (def-is_not_null)
- {
- /* Remove NOT NULL from
AddColumn */
- def-is_not_null = false;
-
- /* Add as a separate
AlterTableCmd */
- newcmd =
makeNode(AlterTableCmd);
- newcmd-subtype = AT_SetNotNull;
- newcmd-name =
pstrdup(def-colname);
- newcmds = lappend(newcmds,
newcmd);
- }
-
- /*
* All constraints are processed in
other ways. Remove the
* original list
*/
--- 1726,1737
* If the column has a non-null
default, we can't skip
* validation of foreign keys.
*/
! if (def-raw_default != NULL)
skipValidation = false;
newcmds = lappend(newcmds, cmd);
/*
* All constraints are processed in
other ways. Remove the
* original list
*/
*** src/test/regress/expected/alter_table.out
--- src/test/regress/expected/alter_table.out
***
*** 505,510 create table atacc1 ( test int );
--- 505,522
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 default value.
+ create table atacc1 ( test int );
+ insert into atacc1
