Обсуждение: Inherited tables and pg_dump

Ahoy there,

I'm the database admin of the Chemnitz Student's Network. Today we
noticed a bug in pg_dump that makes it impossible for us to use it to
create valid backups of our database. We're currently running pgsql
7.1.3, but I was able to reproduce the bug with the latest version,
7.1.2.
We've got a table that inherits another one, and we had to add a new
column to the mother table (and thus to the son, too). Now, if we dump
the database, the columns of the INSERT resp. COPY commands for the data
of the son table are in wrong order. A short sample to reproduce the
behaviour (excuse the unimaginative identifiers):

CREATE TABLE mother (i1 integer, c1 char);
CREATE TABLE son (c2 char) INHERITS (test1);
ALTER TABLE mother ADD i2 integer;

INSERT INTO son (i1, c1, c2, i2) VALUES (1, 'a', 'b', 2);
INSERT INTO son (i1, c1, c2, i2) VALUES (3, 'c', 'd', 4);


A (shortened) dump of this database looks like this:

CREATE TABLE "mother" (
    "i1" integer,
    "c1" character(1),
    "i2" integer
);

CREATE TABLE "son" (
    "c2" character(1)
)
INHERITS ("mother");

COPY "son" FROM stdin;
1    a    b    2
3    c    d    4
\.


As you can see, the columns aren't dropped in the order they are
expected to be, so you can't restore that dump using psql.

    Cheers,
        Ingo

Ingo van Lil <inguin@gmx.de> writes:
> We've got a table that inherits another one, and we had to add a new
> column to the mother table (and thus to the son, too). Now, if we dump
> the database, the columns of the INSERT resp. COPY commands for the data
> of the son table are in wrong order.

Right at the moment you have to use pg_dump's -D option to deal with
this situation.  See discussion earlier today...

            regards, tom lane