Re: missing pg_clog files ?

On Mon, Sep 22, 2003 at 10:50:22AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.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:  60Length (including item array): 180
 

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


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


Cheers,

Patrick