Re: [Fwd: Weird backup file]

"G. Anthony Reina" <reina(at)nsi(dot)edu> writes:
> I backed up my database from Postgres 6.5.3 and migrated to 7.0.2
> several a few months ago. For some reason, data was lost in the
> transition. I've finally pinned it down to the attached file (abridged
> to point out the problem).

> It looks like two things happened in the backup. First, when I move from
> 'G' to 'F' in the names column, I seem to lose the column called
> 'dsp_chan'. Second, the double quotes around the float_4 array called
> 'spike_hist' aren't included.

It looks like some float4[] array values got processed as text and
inserted into the text column dsp_chan --- note that the broken rows
include a \N (null) indicator for the last column where spike_hist ought
to be. Not quite clear how you got to that state. Possibly these are
rows from the un-rearranged table?

> I'm not sure if the double quotes are necessary, but the missing column
> is probably a problem. I added this column after the database was
> created by using 'alter table ellipse_cell_proc add column dsp_chan' and
> then put it in the correct position by using:

> SELECT name, arm, rep, cycle, hemisphere, area, cell, dsp_chan,
> spike_hist INTO xxx FROM ellipse_cell_proc;
> DROP TABLE ellipse_cell_proc;
> ALTER TABLE xxx RENAME TO ellipse_cell_proc;

> Can anyone explain what went wrong with the backup or where I erred
> adding the column?

Your procedure was fine, but ALTER TABLE RENAME was mighty flaky in
pre-7.0 releases. Even in 7.0, doing it inside a transaction block is
asking for trouble (that's finally fixed for 7.1, thank goodness).
I suspect you got bit by an ALTER bug. I'm not sure about the exact
mechanism, but I have a suspicion: it looks a lot like some blocks
of the original ellipse_cell_proc table got written into the new table.
I know 6.5 failed to clear old shared disk buffers during a table
rename. I can't recall if it was sloppy about that during a table drop
as well, but it would've taken both bugs to cause this result if I'm
guessing right that that was the failure path.

There are good reasons why we've been urging people to update to 7.0.*
ASAP ... I'm afraid you got bit by one :-(. Sorry about that.

regards, tom lane