Re-sending without the attachments. Can someone please allow my attachments through from the previous email?
TIA Nikhils ---------- Forwarded message ---------- From: Nikhil Sontakke <nikhil.sonta...@enterprisedb.com> Date: Wed, Mar 9, 2011 at 8:42 PM Subject: index corruption in PG 8.3.13 To: pgsql-hackers@postgresql.org Hi, I am currently looking at a physical file belonging to a corrupt index. This is with PG 8.3.13 on an ext3 filesystem on suse linux with a RAID1 setup. The sequence of operation seems nothing suspicious. Create table, create a bunch of indexes on that table and do insert/update activity. One difference here is that VACUUM FULL might be run in between along with the usual autovacuum mechanism when the inserts/updates are going on. So one of the vacuum calls errored out with a corrupt (completely zeroed out in this case) block with the usual hint to reindex. This is a single machine setup with no slaves. Some analysis using pg_filedump comes up with the following: There are a total of 525 blocks (numbered 0 to 524). The root page is block #3. The corrupt or zeroed out page is block #523. The basic issue is that item2 in root block points to block 523 which is completely zeroed out and this has broken the index chain completely! Other peculiarity in the index file is that we found a lot of zeroed out pages. Blocks from #279 to #518 are all completely zeroed out without any signs of even a page header. Any ideas on how we can get so many zeroed out blocks? Apart from the extend code path, I fail to see any other. And this is an unusually large number of zero pages... Blocks 519 to 521 are DELETED. They do not have the LEAF flag set, meaning they could be internal pages, but that is strange since ROOT page is at level 1. Also importantly their next XID is set FrozenXid, meaning VACUUM FULL was at play. Maybe due to deletes, we reduced the hierarchy level or something? I am attaching the data file, the binary and the 8.3 compliant pg_filedump sources with this mail. Just copy the pg_filedump directory into contrib and use Makefile.contrib to compile. Cannot attach the output of running pg_filedump since my email will cross the attachment limits. Am also attaching my dotty attempts via a png file to visually show the scenario, hopefully that should make things clearer. Feedback, comments, insights appreciated. Appears to be a tough nut to crack :( :), and although blaming the hardware for the zero pages might come to mind, still the deletion activity done by Vacuum/Vacuum Full seems a bit suspicious. Regards, Nikhils -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
