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(dot)bitmead(at)bigfoot(dot)com