Re: BUG #5604: Setting NOT NULL on inherited column turns to real column in dump

On Fri, Aug 6, 2010 at 12:31 AM, Jon Erdman (aka StuckMojo)
<postgresql(at)thewickedtribe(dot)net> wrote:
> Description:        Setting NOT NULL on inherited column turns to real
> column in dump
>
> I think the fix here is relatively simple: make NOT NULL on an inherited
> column dump as an ALTER TABLE.
>
> If you set NOT NULL on an inherited column in a child table, then drop the
> column from the parent, it's gone from both and all is well.
>
> However, if you dump and restore the db, then drop the parent column, the
> inherited column remains in the child table. This is a result of the NOT
> NULL dumping as a column create in the child, which I assume then shadows
> the inherited column after restore.

That sucks. I have a feeling it's going to be hard to fix properly
without this patch:

https://commitfest.postgresql.org/action/patch_view?id=312

I don't think your proposed fix will work because the NOT NULL-ness
could be either inherited or not inherited. The column could even be
inherited from multiple parents, some of which have a NOT NULL
constraint and others of which do not. Consider:

create table top1 (a int not null);
create table top2 (a int);
create table bottom () inherits (top1, top2);
alter table bottom no inherit <something>;

If <something> = top1, then bottom.a should now allow nulls, but if
<something> = top2, then it should still be not null. Unfortunately,
we don't do enough bookkeeping right now to distinguish those cases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company