Re: pg_dump error... Follow up

On 7/9/05 6:12 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote
>> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>>> I think I have found the offending row in measured_bioassay_base... The
>>> entry in its toast table looks like this
>
>>> bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
>>> pg_toast.pg_toast_134401982 where chunk_id = 144391872;
>>> chunk_id | chunk_seq | length
>>> -----------+-----------+--------
>>> 144391872 | 0 | 1998
>>> 144391872 | 1 | 1998
>>> 144391872 | 2 | 1998
>>> 144391872 | 3 | 1998
>>> 144391872 | 4 | 1998
>>> 7625296 | 3292 | 24
>>> 7625297 | 3292 | 24
>>> 7625298 | 3292 | 24
>>> 7625299 | 3292 | 24
>>> 7625308 | 3292 | 19
>
>> This is pretty wacko, because as far as I can see there is nothing wrong
>> with the index at all --- in particular, no gap in the chunk id/seq
>> series here, according to pg_filedump.
>
> Actually, it's also possible that the index is fine and the problem is
> in the underlying toast table ... which would be unfortunate, because
> it'd mean that there's no easy way out like a REINDEX or database
> restart.
>
> We can check this by seeing if the data looks the same using the CTIDs
> that we can see in the index. Please try the same query as above, ie,
> select chunk_id, chunk_seq, length(chunk_data) from
> pg_toast.pg_toast_134401982 where ...
> using these WHERE conditions:
>
> ctid = '(165390,4)'
> ctid = '(165391,1)'
> ctid = '(165391,2)'
> ctid = '(165391,3)'
> ctid = '(165391,4)'
> ctid = '(165392,1)'
> ctid = '(165392,2)'
> ctid = '(165392,3)'
> ctid = '(165392,4)'
>
> It'd also be interesting to look at pg_filedump data for pages 165390
> through 165392 of the toast table itself (not the index) --- see -R
> option of pg_filedump to limit the range of pages.

A REINDEX on the toast table did not fix the problem. Here is the output
from your queries above:

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165390,4)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 0 | 1998
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,1)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 1 | 1998
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,2)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 2 | 1998
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,3)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 3 | 1998
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,4)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 4 | 1998
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,1)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625296 | 3292 | 24
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,2)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625297 | 3292 | 24
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,3)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625298 | 3292 | 24
(1 row)

bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,4)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625299 | 3292 | 24
(1 row)

Unfortunately the pg_filedump you requested gives an error:

pg_filedump -R 165390 165392 134401986

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 3.0
*
* File: 134401986.1
* Options used: -R 165390 165392
*
* Dump created on: Thu Sep 8 10:33:47 2005
*******************************************************************
Error: Premature end of file encountered.

(there is a .1 file also for this table, do I have to stick the two together
or something?)

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.