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

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "Jon Erdman (aka StuckMojo)" <postgresql(at)thewickedtribe(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5604: Setting NOT NULL on inherited column turns to real column in dump
Date: 2010-08-11 13:32:59
Message-ID: AANLkTindzFv+TUHb=FmB6MZLcpun3xei-HwJcC48s2Ab@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Brian CeccarelliDate: 2010-08-11 15:01:57
Subject: Re: BUG #5611: SQL Function STABLE promoting to VOLATILE
Previous:From: Ramachandran SDate: 2010-08-11 09:36:31
Subject: BUG #5612: Database Integrity

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