Re: pg_dump of regression db?

Philip Warner <pjw@rhyme.com.au> writes:
> I thought I would test the latest changes to pg_dump on the regression db,
> and got the following output for an aggregate:

>     CREATE AGGREGATE newcnt (BASETYPE = opaque, SFUNC = int4inc, STYPE =
> int4, INITCOND = '0' );

> Unfortunately, the backend produces the following error when this statement
> is executed:

>     ERROR:  AggregateCreate: Type 'opaque' undefined

The command needs to read "basetype = any".  I guess you'll have to
special-case this in pg_dump (or more accurately, change the special
case that's probably there now for aggbasetype = 0).  I think I changed
the aggregate regression test to exercise basetype = any not long ago.
It didn't before, which is why you didn't see the failure before.


> I vaguely recall seeing something about pg_dump not working of the
> regression db, but would be interested to know if this is the known
> problem,

No, the known problem is that ALTER TABLE on a inheritance hierarchy
screws up the column ordering of the child tables:

* create parent table w/columns a,b,c
* create child table adding columns d,e to parent
* alter parent* add column f

At this point the parent has columns a,b,c,f and the child has
a,b,c,d,e,f --- in that order.

pg_dump will now produce a script that creates parent with a,b,c,f
and then creates child adding d,e, so that the child table has
columns a,b,c,f,d,e --- in that order.  Unfortunately the COPY output
for the child has the columns in order a,b,c,d,e,f, so the data reload
fails.

IMHO this is not pg_dump's fault, it's a bug in ALTER TABLE.  See the
archives for prior discussions of how ALTER TABLE might be fixed so that
the child has the "correct" column order a,b,c,f,d,e right off the bat.

In the meantime, it's possible to work around this if you use pg_dump's
most verbose form of data dumping, where the data is reloaded by INSERT
commands with called-out column names (I forget what the option name
is).  You should find that pg_dump will work on the regression database
if you use that option.
        regards, tom lane