On 7/9/05 6:12 pm, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote: > I wrote >> Adam Witney <awitney@xxxxxxxxxx> 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.
