Re: [HACKERS] Re:pg_dump barfs?

Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)com> writes:
> As a follow-up to this, I tried creating a new database from the
> original CREATE TABLE statements, with the additional field added to the
> CREATE TABLE which I had previously used an ALTER TABLE to add.

> I found that the fields came out in a different order when I do a SELECT
> * FROM urllink.

> This re-enforces my theory that postgres is confused about field orders,

I'm actually a tad surprised that ALTER TABLE ADD COLUMN works at all in
an inheritance context (or maybe the true meaning of your report is that
it doesn't work). See, ADD COLUMN always wants to *add* the column, at
the end of the list of columns for your table. What you had was
something like this:

Table Columns

Parent A B C
Child A B C D E

Then you did ALTER Parent ADD COLUMN F:

Parent A B C F
Child A B C D E

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. 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 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. But the system should not accept
a command that makes the parent and child tables inconsistent.

Anyway, to get back to your immediate problem of rebuilding your
database, the trouble is that once you recreate Parent and Child
using correct declarations, they will look like

Parent A B C F
Child A B C F D E

and since the column order of Child is different from before,
a plain COPY won't reload it correctly (neither will an INSERT
without explicit column labels). 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.

regards, tom lane