Re: [HACKERS] Re:pg_dump barfs?

Tom Lane wrote:

> Ooops, you should have done ALTER Parent*, so you tried to recover by
> altering the child separately with ALTER Child ADD COLUMN F:
> 
>         Parent          A B C F
>         Child           A B C D E F
> 
> Do you see the problem here?  Column F is not correctly inherited,
> because it is not in the same position in parent and child.  If you
> do something like "SELECT F FROM Parent*" you will get D data out of
> the child table (or possibly even a coredump, if F and D are of
> different datatypes) because the inheritance code presumes that F's
> definition in Parent applies to all its children as well.  

Well, in my brief testing, it appears as if what I did actually works as
far as having a working database is concerned. It seemed as if SELECT F
FROM Parent* actually did the right thing. Sort-of anyway. If I didn't
add F to the child, then F seemed to be some random number on a SELECT.

> And the
> column's position is part of its definition.
> 
> I'd say it is a bug that ALTER TABLE allowed you to do an ADD COLUMN
> (or any other mod for that matter) on Parent without also changing its
> children to match. 

I tend to agree. I'd say that you should say table* if table has
children.

> I am not sure whether ADD COLUMN is capable of
> really working right in an inheritance scenario; it'd have to put the
> new column in the middle of the existing columns for child tables,
> and I don't know how hard that is.  

I'm pretty sure it does the right thing already, but I havn't done much
testing.

> What I'd suggest doing is
> dumping the old DB with pg_dump -o and then using a sed script
> or a quick little perl program to reorder the fields in the 
> COPY data before you reload.

Ok, I tried that and it worked.

Any thoughts on the other error mesg I had that seemed to be about
views? I doesn't seem to have caused any problem.

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com