Undetected corruption of table files

From: "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Undetected corruption of table files
Date: 2007-08-23 13:46:07
Message-ID: D960CB61B694CF459DCFB4B0128514C218D95B@exadv11.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I am slightly worried that corruption of data files may
remain undetected in PostgreSQL.

As an experiment, I created a simple table with a primary key
index and inserted a couple of rows. The corresponding data file
is 1 page = 8K long.

Now when I stop the server, zero out the data file with
dd if=/dev/zero of=45810 bs=8192 count=1
and start the server again, the table is empty when I SELECT
from it and no errors are reported.

Only a VACUUM gives me the
WARNING: relation "test" page 0 is uninitialized --- fixing
and the file is truncated to length zero.

The next thing I tried is to randomly scribble into the 8K data
file with a hex editor at different locations.

Some of these actions provoked error messages ranging from
ERROR: invalid page header in block 0 of relation "test"
over
ERROR: could not access status of transaction 1954047348
to
LOG: server process (PID 28149) was terminated by signal 11

Frequently, though, the result was that some of the rows were
"missing", i.e. there was no error message when I SELECTed
from the table, but some of the rows were gone.

I got no errors or warnings from VACUUM either.

As far as I know there is no tool to verify the integrity of
a PostgreSQL table.

- Shouldn't there be an error, some kind of 'missing magic
number' or similar, when a table file consists of only
zeros?

- Wouldn't it be desirable to have some means to verify the
integrity of a table file or a whole database?

Yours,
Laurenz Albe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurent ROCHE 2007-08-23 13:53:54 Re : reporting tools
Previous Message Ned Lilly 2007-08-23 13:44:35 Re: reporting tools

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-08-23 14:15:20 Re: Owner's grant options not visible
Previous Message Teodor Sigaev 2007-08-23 13:08:54 Re: ispell file format