ALTER TABLE...ALTER COLUMN vs inheritance

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: ALTER TABLE...ALTER COLUMN vs inheritance
Date: 2009-11-04 14:39:56
Message-ID: CACA0E642A0267EDA387AF2B@[172.26.14.62]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


I just run across an issue with ALTER TABLE and inheritance (i don't know
wether this is of the same kind of issue KaiGai reported today, so i keep
it on a separate thread).

Consider the following workflow:

CREATE TABLE foo(id integer NOT NULL, val text NOT NULL);
CREATE TABLE foo2(another_id integer NOT NULL) INHERITS(foo);

Now someone decides he doesn't want the NOT NULL constraint on the
inherited column "val" anymore:

ALTER TABLE foo2 ALTER COLUMN val DROP NOT NULL;

This breaks at least pg_dump, which will produce unrestorable dumps at
least when using NULL-values as intended on foo2.

I havent thought about that very deep, but we already force ALTER TABLE
... INHERIT that columns have to be NOT NULL when the new parent already
has a constraint on it, so it seems to me that the best way to repair this
deficiency is to introduce the same rules in ALTER TABLE...ALTER COLUMN,
too.

The described workflow is currently used in OpenERP, which employs such an
inheritance structure on some of its tables (however, making ALTER TABLE
here more strict will surely break OpenERP, but that is another story).

Opinions?

--
Thanks

Bernd

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-11-04 14:41:31 Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns
Previous Message Peter Eisentraut 2009-11-04 14:02:08 PL/Python array support