I sent this e-mail last week but hadn't received any response. Given
Thomas' last message about seeing responses to threads he never recalled
seeing in the first place, I'm wondering whether the original message
made it to the server.


-Tony

p.s. I still can't seem to get the "DIGEST" to work on HACKERS. Seems to
be some problems with the majordomo.


Here's my original message:

-------- Original Message --------
Subject: Weird backup file
Date: Fri, 17 Nov 2000 11:27:32 -0800
From: "G. Anthony Reina" <reina@nsi.edu>
Organization: The Neurosciences Institute
To: "pgsql-hackers@postgreSQL.org"
<pgsql-hackers@postgreSQL.org>,reina@nsi.edu

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.

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?

Thanks.
-Tony
Mon, 20 Nov 2000 10:18:41 -0800
"G. Anthony Reina" <reina@nsi.edu>
[Fwd: Weird backup file]
"G. Anthony Reina" <reina@nsi.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

Mon, 20 Nov 2000 19:41:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [Fwd: Weird backup file] 
Tom Lane wrote:

> 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.
>
>

Okay. At least the problem has been solved. It seems though that the last 2
times I've done a backup (in order to upgrade to the latest Postgres version)
I've had data lost because of some error. I'm getting a little concerned
about the quality of the Postgres backups.

-Tony


>


Mon, 20 Nov 2000 17:04:29 -0800
"G. Anthony Reina" <reina@nsi.edu>
Re: [Fwd: Weird backup file]