Re: missing pg_clog files ?

On Mon, Sep 22, 2003 at 10:50:22AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> > select * from olddata02_03vac offset 2573719 limit 1;
> > ERROR: could not access status of transaction 1664158221
> > DETAIL: open of file "/usr/local/pgsql/data/pg_clog/0633" failed: No such file or directory
>
> > # ls -l pg_clog
> > total 32
> > -rw------- 1 postgres postgres 16384 Sep 22 13:12 0000
>
> What you have here is a corrupted tuple (viz, a silly transaction number).
>
> It would be useful to look at the page containing the tuple to see if
> any pattern can be detected in the corruption. To do this, get the
> ctid of the prior tuple:
> select ctid from olddata02_03vac offset 2573718 limit 1;

(71716,15)

> This will give you a result "(blocknumber,tuplenumber)". The bogus
> tuple is probably on the same page, though possibly further along.
> Next find a dump tool --- I usually use Red Hat's pg_filedump:
> http://sources.redhat.com/rhdb/tools.html
> Dump out the page(s) in question and send them along.

I hope I guessed the right syntax...

% pg_filedump -R 71716 data/base/17148/283342

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 2.0-Alpha
*
* File: data/base/17148/283342
* Options used: -R 71716
*
* Dump created on: Mon Sep 22 16:21:29 2003
*******************************************************************

Block 71716 ********************************************************
<Header> -----
Block Offset: 0x23048000 Offsets: Lower 176 (0x00b0)
Block: Size 8192 Version 1 Upper 236 (0x00ec)
LSN: logid 1 recoff 0xd308022c Special 8192 (0x2000)
Items: 39 Free Space: 60
Length (including item array): 180

<Data> ------
Item 1 -- Length: 204 Offset: 7988 (0x1f34) Flags: USED
Item 2 -- Length: 204 Offset: 7784 (0x1e68) Flags: USED
Item 3 -- Length: 204 Offset: 7580 (0x1d9c) Flags: USED
Item 4 -- Length: 204 Offset: 7376 (0x1cd0) Flags: USED
Item 5 -- Length: 204 Offset: 7172 (0x1c04) Flags: USED
Item 6 -- Length: 204 Offset: 6968 (0x1b38) Flags: USED
Item 7 -- Length: 204 Offset: 6764 (0x1a6c) Flags: USED
Item 8 -- Length: 204 Offset: 6560 (0x19a0) Flags: USED
Item 9 -- Length: 204 Offset: 6356 (0x18d4) Flags: USED
Item 10 -- Length: 204 Offset: 6152 (0x1808) Flags: USED
Item 11 -- Length: 204 Offset: 5948 (0x173c) Flags: USED
Item 12 -- Length: 204 Offset: 5744 (0x1670) Flags: USED
Item 13 -- Length: 204 Offset: 5540 (0x15a4) Flags: USED
Item 14 -- Length: 204 Offset: 5336 (0x14d8) Flags: USED
Item 15 -- Length: 204 Offset: 5132 (0x140c) Flags: USED
Item 16 -- Length: 204 Offset: 4928 (0x1340) Flags: USED
Item 17 -- Length: 204 Offset: 4724 (0x1274) Flags: USED
Item 18 -- Length: 204 Offset: 4520 (0x11a8) Flags: USED
Item 19 -- Length: 204 Offset: 4316 (0x10dc) Flags: USED
Item 20 -- Length: 204 Offset: 4112 (0x1010) Flags: USED
Item 21 -- Length: 204 Offset: 3908 (0x0f44) Flags: USED
Item 22 -- Length: 204 Offset: 3704 (0x0e78) Flags: USED
Item 23 -- Length: 204 Offset: 3500 (0x0dac) Flags: USED
Item 24 -- Length: 204 Offset: 3296 (0x0ce0) Flags: USED
Item 25 -- Length: 204 Offset: 3092 (0x0c14) Flags: USED
Item 26 -- Length: 204 Offset: 2888 (0x0b48) Flags: USED
Item 27 -- Length: 204 Offset: 2684 (0x0a7c) Flags: USED
Item 28 -- Length: 204 Offset: 2480 (0x09b0) Flags: USED
Item 29 -- Length: 204 Offset: 2276 (0x08e4) Flags: USED
Item 30 -- Length: 204 Offset: 2072 (0x0818) Flags: USED
Item 31 -- Length: 204 Offset: 1868 (0x074c) Flags: USED
Item 32 -- Length: 204 Offset: 1664 (0x0680) Flags: USED
Item 33 -- Length: 204 Offset: 1460 (0x05b4) Flags: USED
Item 34 -- Length: 204 Offset: 1256 (0x04e8) Flags: USED
Item 35 -- Length: 204 Offset: 1052 (0x041c) Flags: USED
Item 36 -- Length: 204 Offset: 848 (0x0350) Flags: USED
Item 37 -- Length: 204 Offset: 644 (0x0284) Flags: USED
Item 38 -- Length: 204 Offset: 440 (0x01b8) Flags: USED
Item 39 -- Length: 204 Offset: 236 (0x00ec) Flags: USED

*** End of Requested Range Encountered. Last Block Read: 71716 ***

Cheers,

Patrick