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@thewickedtribe.net> wrote:
> Description: =A0 =A0 =A0 =A0Setting 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=3D312

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> =3D top1, then bottom.a should now allow nulls, but if
<something> =3D top2, then it should still be not null.  Unfortunately,
we don't do enough bookkeeping right now to distinguish those cases.

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