Re: pg_dump ordering problem

Quoting Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk> writes:
> > (I tried v7.4 and v8.0.4 pg_dump and got the same thing). Looking at the
> > output file db.out I can see that the domain and function are used near
> > the beginning of the file, but defined at the end.
>
> I don't think you tried the 8.0 pg_dump. (7.4 did not have any
> dependency ordering logic and so was quite prone to this sort of thing,
> but 8.0 ought to get it right.) If you're certain you did use 8.0,
> could we see those parts of the schema?
>
> > As an aside, why does pg_dump output altered tables as CREATE TABLE...
> > ALTER TABLE instead of just updating the CREATE TABLE statement to
> > assimilate the ALTER TABLE information?
>
> This is one of the techniques used to break circular dependencies.
> Also, indexes and foreign key constraints are deliberately added after
> loading data, for speed reasons.
>
> regards, tom lane
>

Thanks for that, I've gone back and done another data dump on the original
database with both 7.4.8 and 8.0.4 and you're right, the ordering problem goes
away with the 8.0.4 version of pg_dump. I guess I got confused as I had so many
errors...

However, I've now found a different issue....

I *did* have to edit the 8.0.4 file to reload it as it contained unicode
chars that were not read back in:

ERROR: Unicode characters greater than or equal to 0x10000 are not
supported
ERROR: invalid byte sequence for encoding "UNICODE": 0xeb7265
etc.

(This was the same for the 7.4.8 pg_dump). This error broke some foreign keys as
well, so I took the unicode out of the file temporarily. Now, I've found
comments about unicode not being supported, and I assumed it had been fixed in
my local version of 7.4.8 and not my (new) installation of version 8. However,
while I was checking the database dumps just now I noticed that pg_dumpall (both
versions) does not cause this problem; I can read the unicode back in again from
this output. The lines in the file which actually contain the unicode look
identical, though admittedly on my screen the char is just an empty box, but
maybe the problem is elsewhere in the file? Any thoughts? Is there an option to
pg_dump that would enable me to read the unicode back correctly? Or another way
of dumping out a single database?

Sue