pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Witney <awitney@sgul.ac.uk>
To: pgsql-admin <pgsql-admin@postgresql.org>
To: Michael Fuhr <mike@fuhr.org>
Subject: Re: pg_dump error... Follow up
Date: Wed, 07 Sep 2005 13:12:20 -0400
Message-ID: <28274.1126113140@sss.pgh.pa.us> (raw)
In-Reply-To: <28205.1126112786@sss.pgh.pa.us>
References: <BF44BC99.4C70E%awitney@sgul.ac.uk>
	<28205.1126112786@sss.pgh.pa.us>

I wrote
> Adam Witney <awitney@sgul.ac.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.

			regards, tom lane



view thread (13+ messages)  latest in thread

Message-ID: <28274.1126113140@sss.pgh.pa.us>
Permalink:  ../28274.1126113140@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28274.1126113140@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, awitney@sgul.ac.uk, mike@fuhr.org
  Subject: Re: pg_dump error... Follow up
  In-Reply-To: <28274.1126113140@sss.pgh.pa.us>
  References: <BF44BC99.4C70E%awitney@sgul.ac.uk> <28205.1126112786@sss.pgh.pa.us> <28274.1126113140@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox