Re: pgcheck - data integrity check

On Fri, Aug 10, 2007 at 05:48:08AM +0200, Rober Mach wrote:
> . In near future, I would like to extend the functionality also with a
> recovery tool which would be able to repair broken pages. Should the
> function also repair the corrupted data on a page, the AccessShare lock
> on a relation would not be sufficient. But on the other hand
> AccessExclusive lock on the entire relation could significantly
> influence the performance of a database.

I don't think data repairs are the best thing to be doing on a running
system. At the very least, I'd lock the relation for now. If you're
certain you can do page level locking safely that might be an option,
but I'd consider that a bonus.

Also, be careful not to introduce deadlocks by upgrading locks. A
separate repair function might be a good idea (perhaps the check
function can output a recordset that can be loaded into a table, and
then the repair function is run against that...)

> Furhter, I would like to know your opinions on what should be checked
> next in order to check the integrity of data in database. I am thinking
> of checking:
> -in case of variable-length data, compare the formal and actual size of data

How would you do that? The only way to tell the length is via the
varlena header. The best I think you could do would be to calculate the
length of a row and compare that to the item pointers.

> -check whether constrains applied on items are fulfilled

I don't know that there's much value in this...

> -compare data in indexes with indexed tables, whether they are correct

I think this should probably be top-priority, since the index code is
fairly complex this has a much higher likelyhood of bugs/problems.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)