I noticed this behavior on master and it seems like a bug to me:
postgres=# CREATE TABLE test (a float check (a > 10.2));
postgres=# CREATE TABLE test_child() INHERITS(test);
postgres=# ALTER TABLE test ALTER COLUMN a SET DATA TYPE numeric;
ERROR: constraint must be added to child
The error message seems unintended. Sure, we are changing the data type of
a column which has a constraint on it. The ALTER TABLE mechanism would drop
and recreate that constraint after changing the data type of the column.
The ATAddCheckConstraint() function checks if the "recurse" flag is passed
(basically check against ONLY clause). If the flag is not passed and the
table has children, it raises the above mentioned exception. This is right
for a normal ADD CONSTRAINT operation because we don't want to allow
constraint addition ONLY on the parent table unless there are no child
tables currently on the parent. But when constraint is being re-added as a
side-effect of another ALTER TABLE command, we shouldn't really be raising
an exception because ATPrepCmd() would have expanded to child tables and
there would appropriate commands in the work queue to recreate constraints
on all the child tables as well.
So I think we need to teach ATAddCheckConstraint() to not do this check if
its being called from AT_PASS_OLD_INDEX of ALTER TABLE.
I can work up a patch if we are in agreement that this indeed is a bug and
the approach that I mentioned for fixing this is also right. Comments ?
pgsql-hackers by date
|Next:||From: Pavan Deolasee||Date: 2012-11-02 12:02:16|
|Subject: Re: Problem Observed in behavior of Create Index
Concurrently and Hot Update|
|Previous:||From: Magnus Hagander||Date: 2012-11-02 11:17:40|
|Subject: Re: Proposal for Allow postgresql.conf values to be changed