Skip site navigation (1) Skip section navigation (2)

Bug in ALTER COLUMN SET DATA TYPE ?

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Bug in ALTER COLUMN SET DATA TYPE ?
Date: 2012-11-02 11:52:07
Message-ID: CABOikdOgMONK1q8adowZQw3PVVv+m2cbgGRAkBLCbKYfLdXfJw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

I noticed this behavior on master and it seems like a bug to me:

postgres=# CREATE TABLE test (a float check (a > 10.2));
CREATE TABLE
postgres=# CREATE TABLE test_child() INHERITS(test);
CREATE TABLE
postgres=# ALTER TABLE test ALTER COLUMN a SET DATA TYPE numeric;

                             ERROR:  constraint must be added to child
tables too

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 ?

Thanks,
Pavan

Responses

pgsql-hackers by date

Next:From: Pavan DeolaseeDate: 2012-11-02 12:02:16
Subject: Re: Problem Observed in behavior of Create Index Concurrently and Hot Update
Previous:From: Magnus HaganderDate: 2012-11-02 11:17:40
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group