corrupted tuple (header?), pg_filedump output

Eric Parusel <lists@globalrelay.net> writes:

./pg_filedump -i -f -R 28393 /data1/pgsql/data/base/17760/18004
--snip--
Item 2 -- Length: 1728 Offset: 6464 (0x1940) Flags: USED
XMIN: 12 CMIN: 196608 XMAX: 122552335 CMAX|XVAC: 177664675
Block Id: 0 linp Index: 47241 Attributes: 3692 Size: 0
infomask: 0x0900 (XMIN_COMMITTED|XMAX_INVALID)
Error: Computed header length not equal to header size.
Computed <28> Header: <0>
--snip--

None of those fields seem very sensible. I suspect what's actually
corrupt is the ItemId in the page header --- ie, it's pointing at
something that's not a tuple header. You might eyeball the ItemId
array and see if any pattern of corruption is visible.

(Oh btw: are you certain you have the right version of pg_filedump?
If it's older than your postmaster it might be giving you wrong
answers.)

What do I do now? Can this be repaired?
This tuple is not needed, I can "get rid of it" or somehow dereference
it if need be.

If you can do without the whole page, the easiest way is to zero out the
entire page with 'dd'. If you just want to kill that one tuple you can
reset the LP_USED bit in its ItemId. Either way the postmaster needs to
be stopped to be sure that it's not got the page buffered while you
change it.

regards, tom lane

Tom Lane wrote:

Eric Parusel <lists@globalrelay.net> writes:

./pg_filedump -i -f -R 28393 /data1/pgsql/data/base/17760/18004
--snip--
Item 2 -- Length: 1728 Offset: 6464 (0x1940) Flags: USED
XMIN: 12 CMIN: 196608 XMAX: 122552335 CMAX|XVAC: 177664675
Block Id: 0 linp Index: 47241 Attributes: 3692 Size: 0
infomask: 0x0900 (XMIN_COMMITTED|XMAX_INVALID)
Error: Computed header length not equal to header size.
Computed <28> Header: <0>
--snip--

None of those fields seem very sensible. I suspect what's actually
corrupt is the ItemId in the page header --- ie, it's pointing at
something that's not a tuple header. You might eyeball the ItemId
array and see if any pattern of corruption is visible.

Ok, the other items in the dump seemed sensible to me.

Is this what you're speaking of?

Block 28393 ********************************************************
<Header> -----
Block Offset: 0x0ddd2000 Offsets: Lower 56 (0x0038)
Block: Size 8192 Version 2 Upper 1064 (0x0428)
LSN: logid 242 recoff 0x9387bd78 Special 8192 (0x2000)
Items: 9 Free Space: 1008
Length (including item array): 60

0000: f2000000 78bd8793 01000000 38002804 ....x.......8.(.
0010: 00200220 808cb80d 4099800d a485b80d . . ....@.......
0020: ec847001 28848401 9c984801 b8060000 ..p.(.....H.....
0030: 5c93800a 28040000 00000000 \...(.......

Or, this?:

<Data> ------
Item 1 -- Length: 1756 Offset: 3200 (0x0c80) Flags: USED
Item 2 -- Length: 1728 Offset: 6464 (0x1940) Flags: USED
Item 3 -- Length: 1756 Offset: 1444 (0x05a4) Flags: USED
Item 4 -- Length: 184 Offset: 1260 (0x04ec) Flags: USED
Item 5 -- Length: 194 Offset: 1064 (0x0428) Flags: USED
Item 6 -- Length: 164 Offset: 6300 (0x189c) Flags: USED
Item 7 -- Length: 0 Offset: 1720 (0x06b8) Flags: 0x00
Item 8 -- Length: 1344 Offset: 4956 (0x135c) Flags: USED
Item 9 -- Length: 0 Offset: 1064 (0x0428) Flags: 0x00

(Oh btw: are you certain you have the right version of pg_filedump?
If it's older than your postmaster it might be giving you wrong
answers.)

Yup, I do believe so, I've got the PGDG 8.0.1 rpms installed and I just
downloaded the PGDG 8.0.1 contrib srpm, and compiled pg_filedump
(downloaded from
http://sources.redhat.com/rhdb/tools/pg_filedump-4.0.tar)...

If you can do without the whole page, the easiest way is to zero out the
entire page with 'dd'. If you just want to kill that one tuple you can
reset the LP_USED bit in its ItemId. Either way the postmaster needs to
be stopped to be sure that it's not got the page buffered while you
change it.

Ok, how do I go about resetting the LP_USED bit? I'll make sure the
postmaster stopped for such an operation...

*******
*******
Oh crap, I just noticed that there is more than just that item that's
inconsistent...

Item 6 -- Length: 164 Offset: 6300 (0x189c) Flags: USED
XMIN: 1852400896 CMIN: 1970348133 XMAX: 543424609 CMAX|XVAC:
1869112179
Block Id: 1819244288 linp Index: 29295 Attributes: 2667 Size: 101
infomask: 0x7270
(HASOID|XMIN_INVALID|MARKED_FOR_UPDATE|UPDATED|MOVED_OFF)
Error: Computed header length not equal to header size.
Computed <32> Header: <101>

Item 8 -- Length: 1344 Offset: 4956 (0x135c) Flags: USED
XMIN: 2100285 CMIN: 1644759593 XMAX: 1634148473 CMAX|XVAC: 2003137536
Block Id: 2036411953 linp Index: 103 Attributes: 28524 Size: 108
infomask: 0x6162 (HASVARWIDTH|XMIN_COMMITTED|UPDATED|MOVED_OFF)
Error: Computed header length not equal to header size.
Computed <28> Header: <108>
*******
*******

Thanks again,
Eric

Eric Parusel <lists@globalrelay.net> writes:

Is this what you're speaking of?

<Data> ------
Item 1 -- Length: 1756 Offset: 3200 (0x0c80) Flags: USED
Item 2 -- Length: 1728 Offset: 6464 (0x1940) Flags: USED
Item 3 -- Length: 1756 Offset: 1444 (0x05a4) Flags: USED
Item 4 -- Length: 184 Offset: 1260 (0x04ec) Flags: USED
Item 5 -- Length: 194 Offset: 1064 (0x0428) Flags: USED
Item 6 -- Length: 164 Offset: 6300 (0x189c) Flags: USED
Item 7 -- Length: 0 Offset: 1720 (0x06b8) Flags: 0x00
Item 8 -- Length: 1344 Offset: 4956 (0x135c) Flags: USED
Item 9 -- Length: 0 Offset: 1064 (0x0428) Flags: 0x00

Well, scratch that theory: those offsets and sizes all seem perfectly
consistent (eg, one ends where the next begins, modulo some alignment
padding in one case). So it's hard to credit that the itemid got
bombed; the corruption has to be in the tuple header itself. What
does the hex dump of the tuple header and vicinity look like?

Oh crap, I just noticed that there is more than just that item that's
inconsistent...

Item 6 -- Length: 164 Offset: 6300 (0x189c) Flags: USED
XMIN: 1852400896 CMIN: 1970348133 XMAX: 543424609 CMAX|XVAC:
1869112179
Block Id: 1819244288 linp Index: 29295 Attributes: 2667 Size: 101
infomask: 0x7270
(HASOID|XMIN_INVALID|MARKED_FOR_UPDATE|UPDATED|MOVED_OFF)
Error: Computed header length not equal to header size.
Computed <32> Header: <101>

Item 8 -- Length: 1344 Offset: 4956 (0x135c) Flags: USED
XMIN: 2100285 CMIN: 1644759593 XMAX: 1634148473 CMAX|XVAC: 2003137536
Block Id: 2036411953 linp Index: 103 Attributes: 28524 Size: 108
infomask: 0x6162 (HASVARWIDTH|XMIN_COMMITTED|UPDATED|MOVED_OFF)
Error: Computed header length not equal to header size.
Computed <28> Header: <108>

Those look kinda clobbered too. Maybe you should just send along the
whole -i -f page dump?

regards, tom lane

I've brought this back on-list, probably best that way..?

Eric Parusel wrote:

Tom Lane wrote:

What it kinda looks like from here is that you suffered a "page tear":
the itemid pointers at the front of the page may be self-consistent, but
they don't quite match the state of the rest of the page. For instance
the claimed item-2 header is obviously bogus but it looks like there is
a valid header starting a few bytes after where the itemid points.
I suspect that the itemid pointers are one generation earlier or later
than the remainder of the page. Since disks typically write in 512-byte
sectors and there is nothing else in the first 512 bytes except the
itemids, we could imagine that that sector got written and then the rest
of the page did not. Postgres is supposed to protect against this sort
of thing in case of a system crash, but I wouldn't want to swear that
the protections are completely bulletproof. Have you had any power
failures or system crashes lately? What sort of hardware and OS is this
on?

Hmm...
Here is some system information:

Dell PE1750, 2GB ECC ram, 2x73GB 10K scsi attached to Perc4/di
(raid-on-motherboard, LSI megaraid chipset, battery-backed cache,
write-back cache enabled), firmware/drivers is up to date as of a month
ago.

The OS is RHEL3, kept up to date with the newest kernel for it.

PgSQL 8.0.1 installed from RPMs on postgresql.org, it had 8.0.0
installed from DGPG RPMs initially until 8.0.1 came out.

No power failures or crashes since it's been up...

It's been up and running with moderate to heavy load for about 2 months
now.

I don't think there have been any pgsql backend (if that's the word for
them) processes crashing or anything of that sort...

Pretty heavy write load on the box, it will be getting a 14 disk raid10
array plugged into it soon to speed things up.

I can't remember and I couldn't find it, but is there a consistency
checking tool (pg_fsck or something?) for pgsql? Or I suppose a dump of
the whole database (which I do nightly) ensures all the data is readable...

If there's anything else I can do to help figure this out, let me know..

Thanks,
Eric

How would I go about double checking I don't have this problem on other
pages? As above, a successful db dump would verify everything's fine?
I suppose a dump and reload after that point would verify that my
indexes and anything else in base/ is fine?

How would I figure out where and how much to overwrite with dd if I was
to clear this page? Or how would I set the invalid item's itemid to empty?

Obviously, stuff like this tends not to be in the documentation :D

Thanks for the help,
Eric