From john.lister-ps@kickstone.com Mon Feb  9 17:20:27 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 89CD56323FA
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:20:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 92389-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:19:55 -0400 (AST)
X-Greylist: delayed 00:42:40.859634 by SQLgrey-1.7.6
Received: from mail.kickstone.com (smtp.kickstone.com [87.194.183.70])
	by mail.postgresql.org (Postfix) with ESMTP id D62C5632D80
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 13:19:54 -0400 (AST)
Received: from madras2.square-pi.com ([192.168.2.131] helo=MADRAS2)
	by mail.kickstone.com with esmtpa (Exim 4.69)
	(envelope-from <john.lister-ps@kickstone.com>) id 1LWZ8B-0002YA-Bd
	for pgsql-admin@postgresql.org; Mon, 09 Feb 2009 16:37:11 +0000
Message-ID: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: <pgsql-admin@postgresql.org>
Subject: database corruption help
Date: Mon, 9 Feb 2009 16:37:11 -0000
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0010_01C98AD4.A8017F20"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200902/59
X-Sequence-Number: 31280

This is a multi-part message in MIME format.

------=_NextPart_000_0010_01C98AD4.A8017F20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi, my wal archiving broke and postgresql filled up the local disk with =
transaction logs, which i foolishly deleted in a moment of madness, =
after resetting the transaction log a few of my tables are damaged but =
repairable. However the system tables also seemed to have suffered. My =
main problem is duplicate rows which violated the primary key and if i =
do a reindex system i get similar errors, for example:

ERROR:  could not create unique index "pg_class_oid_index"

a quick inspection of the pg_class table doesn't show any duplicates, is =
there anyway i can find out which row(s) are duplicated and remove them =
without a full db restore?

also doing something like this doesn't return anything which worked for =
my other tables

select oid from pg_class where oid in(select oid from pg_class group by =
oid having count(oid)>1 )



Many thanks
--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

------=_NextPart_000_0010_01C98AD4.A8017F20
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16674" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi, my wal archiving broke and =
postgresql filled up=20
the local disk with transaction logs, which i foolishly deleted in a =
moment of=20
madness, after resetting the transaction log a few of my tables are =
damaged but=20
repairable. However the system tables also seemed to have suffered. My =
main=20
problem is duplicate rows which violated the primary key and if i do a=20
</FONT><FONT face=3DArial size=3D2>reindex system i get similar errors, =
for=20
example:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>ERROR:&nbsp; could not create unique =
index=20
"pg_class_oid_index"<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>a quick inspection of the pg_class =
table doesn't=20
show any duplicates, is there anyway i can find out which row(s) are =
duplicated=20
and remove them without a full db restore?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>also doing something like this doesn't =
return=20
anything which worked for my other tables</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select oid from pg_class where oid =
in(select oid=20
from pg_class group by oid having count(oid)&gt;1 )<BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Many thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>--</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Got needs? Get Goblin'! - <A=20
href=3D"http://www.pricegoblin.co.uk/">http://www.pricegoblin.co.uk/</A><=
BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0010_01C98AD4.A8017F20--




From tgl@sss.pgh.pa.us Mon Feb  9 17:32:42 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 69DCF632DF9
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:32:42 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 05593-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:31:48 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 2F198632DAD
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 13:31:45 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n19HVXR1017206;
	Mon, 9 Feb 2009 12:31:33 -0500 (EST)
To: "John Lister" <john.lister-ps@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: database corruption help 
In-reply-to: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
Comments: In-reply-to "John Lister" <john.lister-ps@kickstone.com>
	message dated "Mon, 09 Feb 2009 16:37:11 +0000"
Date: Mon, 09 Feb 2009 12:31:33 -0500
Message-ID: <17205.1234200693@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/60
X-Sequence-Number: 31281

"John Lister" <john.lister-ps@kickstone.com> writes:
> ERROR:  could not create unique index "pg_class_oid_index"

> a quick inspection of the pg_class table doesn't show any duplicates, is there anyway i can find out which row(s) are duplicated and remove them without a full db restore?

> also doing something like this doesn't return anything which worked for my other tables

> select oid from pg_class where oid in(select oid from pg_class group by oid having count(oid)>1 )

Try doing it with enable_indexscan = off and enable_bitmapscan = off.
The system might be relying on the old, bogus index to do the group by
oid.

			regards, tom lane



From john.lister-ps@kickstone.com Mon Feb  9 19:25:44 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id ACA7763305D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 15:25:44 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 31908-05-3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 15:25:33 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.kickstone.com (smtp.kickstone.com [87.194.183.70])
	by mail.postgresql.org (Postfix) with ESMTP id CC4DE633060
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 15:20:43 -0400 (AST)
Received: from madras2.square-pi.com ([192.168.2.131] helo=MADRAS2)
	by mail.kickstone.com with esmtpa (Exim 4.69)
	(envelope-from <john.lister-ps@kickstone.com>)
	id 1LWbgO-0002tX-AM; Mon, 09 Feb 2009 19:20:40 +0000
Message-ID: <AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-admin@postgresql.org>
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
Subject: Re: database corruption help 
Date: Mon, 9 Feb 2009 19:20:40 -0000
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=STOX_REPLY_TYPE=0.001
X-Spam-Level: 
X-Archive-Number: 200902/62
X-Sequence-Number: 31283

Cheers tom, that did it - i've removed the duplicates and seeing what else 
is broken.

.


> "John Lister" <john.lister-ps@kickstone.com> writes:
>> ERROR:  could not create unique index "pg_class_oid_index"
>
>> a quick inspection of the pg_class table doesn't show any duplicates, is 
>> there anyway i can find out which row(s) are duplicated and remove them 
>> without a full db restore?
>
>> also doing something like this doesn't return anything which worked for 
>> my other tables
>
>> select oid from pg_class where oid in(select oid from pg_class group by 
>> oid having count(oid)>1 )
>
> Try doing it with enable_indexscan = off and enable_bitmapscan = off.
> The system might be relying on the old, bogus index to do the group by
> oid.
>
> regards, tom lane
> 




From tgl@sss.pgh.pa.us Mon Feb  9 19:34:15 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3C6D3632983
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 15:34:15 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 41356-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 15:34:11 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id C97A863284C
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 15:34:11 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n19JY4wp020152;
	Mon, 9 Feb 2009 14:34:04 -0500 (EST)
To: John Lister <john.lister@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: database corruption help 
In-reply-to: <4990840C.4090900@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com>
Comments: In-reply-to John Lister <john.lister@kickstone.com>
	message dated "Mon, 09 Feb 2009 19:29:16 +0000"
Date: Mon, 09 Feb 2009 14:34:04 -0500
Message-ID: <20151.1234208044@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/63
X-Sequence-Number: 31284

John Lister <john.lister@kickstone.com> writes:
> Although saying that..
> reindex now works, but doing a vacuum verbose complains that the index 
> is out of step with the table and i should reindex..
> would i be better shutting the db down, restarting in standalone mode 
> (and also using  the -P option) before reindexing?

Yeah, if you have reason not to trust the system indexes then -P is
a good idea until they are fixed.  Standalone mode per se isn't that
important --- you could do this from a regular session with -P specified
via PGOPTIONS.

			regards, tom lane



From john.lister-ps@kickstone.com Mon Feb  9 20:18:54 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E75E0632D80
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 16:18:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 88012-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 16:18:50 -0400 (AST)
X-Greylist: delayed 00:09:40.076462 by SQLgrey-1.7.6
Received: from mk-filter-4-a-1.mail.uk.tiscali.com
	(mk-filter-4-a-1.mail.uk.tiscali.com [212.74.100.55])
	by mail.postgresql.org (Postfix) with ESMTP id 9E1DC62FBA6
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 16:18:50 -0400 (AST)
X-Trace: 
 142339716/mk-filter-4.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.1.137/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.1.137
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AloBADUckElQ4QGJ/2dsb2JhbAAI0UOEGgY
X-IronPort-AV: E=Sophos;i="4.37,406,1231113600"; d="scan'208";a="142339716"
Received: from 80-225-1-137.dynamic.dial.as9105.com (HELO [192.168.6.11])
	([80.225.1.137])
	by smtp.tiscali.co.uk with ESMTP; 09 Feb 2009 20:09:08 +0000
Message-ID: <49908D72.7040904@kickstone.com>
Date: Mon, 09 Feb 2009 20:09:22 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-admin@postgresql.org
Subject: Re: database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
In-Reply-To: <20151.1234208044@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/64
X-Sequence-Number: 31285


 >Yeah, if you have reason not to trust the system indexes then -P is
 >a good idea until they are fixed. Standalone mode per se isn't that
 >important --- you could do this from a regular session with -P specified
 >via PGOPTIONS.

still getting the same problem.


 > PGOPTIONS="-P"
 > psql backend
 >> reindex system backend;
 >> NOTICE:  table "pg_class" was reindexed
 >> NOTICE:  table "pg_statistic" was reindexed
 >> NOTICE:  table "pg_ts_config" was reindexed ...

 >> vacuum full verbose pg_class

 >> INFO:  vacuuming "pg_catalog.pg_class"
 >> INFO:  "pg_class": found 7 removable, 1839 nonremovable row versions 
in 18812 pages
 >> DETAIL:  27 dead row versions cannot be removed yet.
 >> Nonremovable row versions range from 160 to 229 bytes long.
 >> There were 929452 unused item pointers.
 >> Total free space (including removable row versions) is 149628172 bytes.
 >> 18749 pages are or will become empty, including 0 at the end of the 
table.
 >> 0 pages containing 0 free bytes are potential move destinations.
 >> CPU 0.01s/0.07u sec elapsed 0.17 sec.
 >> INFO:  index "pg_class_oid_index" now contains 1812 row versions in 
7 pages
 >> DETAIL:  0 index row versions were removed.
 >> 0 index pages have been deleted, 0 are currently reusable.
 >> CPU 0.00s/0.01u sec elapsed 0.09 sec.
 >> WARNING:  index "pg_class_oid_index" contains 1812 row versions, but 
table contains 1807 row versions
 >> HINT:  Rebuild the index with REINDEX.
 >> INFO:  index "pg_class_relname_nsp_index" now contains 1812 row 
versions in 23 pages
 >> DETAIL:  0 index row versions were removed.
 >> 0 index pages have been deleted, 0 are currently reusable.
 >> CPU 0.00s/0.01u sec elapsed 0.09 sec.
 >> WARNING:  index "pg_class_relname_nsp_index" contains 1812 row 
versions, but table contains 1807 row versions
 >> HINT:  Rebuild the index with REINDEX.


Just  noticed the 7 dead rows above, are they likely to be causing a 
problem?
BTW this is postgresql 8.3.5 if that makes a difference...

I'm guessing it is fairly important to get rid of these warnings....

Thanks




From tgl@sss.pgh.pa.us Tue Feb  10 00:02:13 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 69101632E44
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 20:02:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 29431-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 20:02:06 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 32D45632E46
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 20:01:38 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n1A01U9r025754;
	Mon, 9 Feb 2009 19:01:31 -0500 (EST)
To: John Lister <john.lister-ps@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: database corruption help 
In-reply-to: <49908D72.7040904@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
Comments: In-reply-to John Lister <john.lister-ps@kickstone.com>
	message dated "Mon, 09 Feb 2009 20:09:22 +0000"
Date: Mon, 09 Feb 2009 19:01:30 -0500
Message-ID: <25753.1234224090@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/68
X-Sequence-Number: 31289

John Lister <john.lister-ps@kickstone.com> writes:
> still getting the same problem.

>>> PGOPTIONS="-P"
>>> psql backend

I think you need export PGOPTIONS="-P" to make that work.  Whether
it's related to your problem isn't clear though.

			regards, tom lane



From john.lister-ps@kickstone.com Tue Feb  10 07:57:54 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CB126632CCD
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 10 Feb 2009 03:57:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 23252-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 10 Feb 2009 03:57:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-4-a-1.mail.uk.tiscali.com
	(mk-filter-4-a-1.mail.uk.tiscali.com [212.74.100.55])
	by mail.postgresql.org (Postfix) with ESMTP id 74A37632C72
	for <pgsql-admin@postgresql.org>; Tue, 10 Feb 2009 03:57:51 -0400 (AST)
X-Trace: 
 142580349/mk-filter-4.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.14.181/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.14.181
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AkwBAJPCkElQ4Q61/2dsb2JhbAAI0hWEGgY
X-IronPort-AV: E=Sophos;i="4.38,185,1233532800"; d="scan'208";a="142580349"
Received: from 80-225-14-181.dynamic.dial.as9105.com (HELO [192.168.6.11])
	([80.225.14.181])
	by smtp.tiscali.co.uk with ESMTP; 10 Feb 2009 07:57:49 +0000
Message-ID: <4991338B.704@kickstone.com>
Date: Tue, 10 Feb 2009 07:58:03 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-admin@postgresql.org
Subject: Re: database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com> <25753.1234224090@sss.pgh.pa.us>
In-Reply-To: <25753.1234224090@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/69
X-Sequence-Number: 31290

Sorry, had exported it - bad cut/pasting...
I even tried it in single-user mode passing -P directly but got the same 
result
Also confused/concerned by the 7 dead rows in pg_class. as i've 
restarted the server all transaction should have finished so i'd like to 
reclaim these - especially as vacuum reports the table is using 80Mb+

Thanks


>> still getting the same problem.
>>     
>
>   
>>>> PGOPTIONS="-P"
>>>> psql backend
>>>>         
>
> I think you need export PGOPTIONS="-P" to make that work.  Whether
> it's related to your problem isn't clear though.
>
> 			regards, tom lane
>   



From john.lister-ps@kickstone.com Thu Feb  12 21:11:13 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 38D5163225E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:11:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 34406-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:11:08 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-3-a-1.mail.uk.tiscali.com
	(mk-filter-3-a-1.mail.uk.tiscali.com [212.74.100.54])
	by mail.postgresql.org (Postfix) with ESMTP id E0855633066
	for <pgsql-admin@postgresql.org>; Thu, 12 Feb 2009 17:11:05 -0400 (AST)
X-Trace: 
 145033584/mk-filter-3.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.116.207/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.116.207
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AnYBAG8flElQ4XTP/2dsb2JhbAAIgxbRFIQYBg
X-IronPort-AV: E=Sophos;i="4.38,198,1233532800"; d="scan'208";a="145033584"
Received: from ppp-3-207.bris-a-2.access.uk.tiscali.com (HELO [192.168.6.11])
	([80.225.116.207])
	by smtp.tiscali.co.uk with ESMTP; 12 Feb 2009 21:11:02 +0000
Message-ID: <4994906B.6090408@kickstone.com>
Date: Thu, 12 Feb 2009 21:11:07 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-admin@postgresql.org
Subject: Re: database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
In-Reply-To: <4991338B.704@kickstone.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/83
X-Sequence-Number: 31304

I seem to have more dead rows now..

doing a vacuum full on pg_class gives me

INFO:  vacuuming "pg_catalog.pg_class"INFO:  "pg_class": found 37 
removable, 1845 nonremovable row versions in 18905 pages
DETAIL:  27 dead row versions cannot be removed yet.
Nonremovable row versions range from 160 to 229 bytes long.
There were 933834 unused item pointers.
Total free space (including removable row versions) is 150368692 bytes.
18839 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO:  index "pg_class_oid_index" 
now contains 1813 row versions in 7 pages
DETAIL:  56 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index 
"pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages
DETAIL:  63 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING:  index 
"pg_class_relname_nsp_index" contains 1818 row versions, but table 
contains 1813 row versions
HINT:  Rebuild the index with REINDEX.INFO:  analyzing 
"pg_catalog.pg_class"INFO:  "pg_class": scanned 3000 of 18905 pages, 
containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 
estimated total rows
Total query runtime: 4469 ms.


I've restarted the server which should have got rid of any 
outstanding/long running transactions  - which it though was the major 
cause of lingering dead rows.

Any ideas how to fix this as i'd like to reduce this from 150+Mb which 
can't be good for performance....

Thanks





From tgl@sss.pgh.pa.us Thu Feb  12 21:39:56 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id AAC63633066
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:39:56 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 62275-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:39:53 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 22EF6633056
	for <pgsql-admin@postgresql.org>; Thu, 12 Feb 2009 17:39:52 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n1CLdiNS017205;
	Thu, 12 Feb 2009 16:39:44 -0500 (EST)
To: John Lister <john.lister-ps@kickstone.com>
cc: pgsql-admin@postgresql.org
Subject: Re: database corruption help 
In-reply-to: <4994906B.6090408@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com>
Comments: In-reply-to John Lister <john.lister-ps@kickstone.com>
	message dated "Thu, 12 Feb 2009 21:11:07 +0000"
Date: Thu, 12 Feb 2009 16:39:44 -0500
Message-ID: <17204.1234474784@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/84
X-Sequence-Number: 31305

John Lister <john.lister-ps@kickstone.com> writes:
> I seem to have more dead rows now..
> doing a vacuum full on pg_class gives me

> INFO:  vacuuming "pg_catalog.pg_class"INFO:  "pg_class": found 37 
> removable, 1845 nonremovable row versions in 18905 pages
> DETAIL:  27 dead row versions cannot be removed yet.
> Nonremovable row versions range from 160 to 229 bytes long.
> There were 933834 unused item pointers.
> Total free space (including removable row versions) is 150368692 bytes.
> 18839 pages are or will become empty, including 0 at the end of the table.
> 0 pages containing 0 free bytes are potential move destinations.

Hmm, that last seems to indicate that do_shrinking has been reset.
It looks like the cases where that happens are reported with elog(LOG)
which means they probably only went to the postmaster log (maybe we
should fix things so that vacuum verbose reports those).  What have
you got in the postmaster log?

			regards, tom lane



From john.lister-ps@kickstone.com Thu Feb  12 21:45:52 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D709A633066
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:45:52 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 68528-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 17:45:34 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-1-a-1.mail.uk.tiscali.com
	(mk-filter-1-a-1.mail.uk.tiscali.com [212.74.100.52])
	by mail.postgresql.org (Postfix) with ESMTP id E0021633056
	for <pgsql-admin@postgresql.org>; Thu, 12 Feb 2009 17:45:32 -0400 (AST)
X-Trace: 
 148573620/mk-filter-1.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.116.207/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.116.207
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AjsBALMmlElQ4XTP/2dsb2JhbAAI1DqEGAY
X-IronPort-AV: E=Sophos;i="4.38,198,1233532800"; d="scan'208";a="148573620"
Received: from ppp-3-207.bris-a-2.access.uk.tiscali.com (HELO [192.168.6.11])
	([80.225.116.207])
	by smtp.tiscali.co.uk with ESMTP; 12 Feb 2009 21:45:30 +0000
Message-ID: <4994987F.1030204@kickstone.com>
Date: Thu, 12 Feb 2009 21:45:35 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-admin@postgresql.org
Subject: Re: database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
In-Reply-To: <17204.1234474784@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/85
X-Sequence-Number: 31306

I'm running ubuntu and can see references to only one log file which i'm 
assuming is the postmaster log..

Anyway the only relevant bits are:

GMT LOG:  relation "pg_class" TID 15538/4: dead HOT-updated tuple --- 
cannot shrink relation
2009-02-12 21:06:40 GMT STATEMENT:  VACUUM FULL VERBOSE ANALYZE pg_class
2009-02-12 21:06:40 GMT WARNING:  index "pg_class_relname_nsp_index" 
contains 1818 row versions, but table contains 1813 row versions

If that helps...

Thanks
> John Lister <john.lister-ps@kickstone.com> writes:
>   
>> I seem to have more dead rows now..
>> doing a vacuum full on pg_class gives me
>>     
>
>   
>> INFO:  vacuuming "pg_catalog.pg_class"INFO:  "pg_class": found 37 
>> removable, 1845 nonremovable row versions in 18905 pages
>> DETAIL:  27 dead row versions cannot be removed yet.
>> Nonremovable row versions range from 160 to 229 bytes long.
>> There were 933834 unused item pointers.
>> Total free space (including removable row versions) is 150368692 bytes.
>> 18839 pages are or will become empty, including 0 at the end of the table.
>> 0 pages containing 0 free bytes are potential move destinations.
>>     
>
> Hmm, that last seems to indicate that do_shrinking has been reset.
> It looks like the cases where that happens are reported with elog(LOG)
> which means they probably only went to the postmaster log (maybe we
> should fix things so that vacuum verbose reports those).  What have
> you got in the postmaster log?
>
> 			regards, tom lane
>   



From tgl@sss.pgh.pa.us Thu Feb  12 22:13:25 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A1520633082
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 18:13:25 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 00285-02
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Thu, 12 Feb 2009 18:13:21 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 07366633083
	for <pgsql-hackers@postgreSQL.org>;
	Thu, 12 Feb 2009 18:13:21 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n1CMD3qW017765;
	Thu, 12 Feb 2009 17:13:03 -0500 (EST)
To: John Lister <john.lister-ps@kickstone.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [ADMIN] database corruption help 
In-reply-to: <4994987F.1030204@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com>
Comments: In-reply-to John Lister <john.lister-ps@kickstone.com>
	message dated "Thu, 12 Feb 2009 21:45:35 +0000"
Date: Thu, 12 Feb 2009 17:13:03 -0500
Message-ID: <17764.1234476783@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/714
X-Sequence-Number: 134004

John Lister <john.lister-ps@kickstone.com> writes:
> GMT LOG:  relation "pg_class" TID 15538/4: dead HOT-updated tuple --- 
> cannot shrink relation

Hmm.  The comments in vacuum.c about this case suppose that it could
only be a transient condition, ie the tuple became dead while we were
looking at the page.  Evidently it's persistent for you, which means
that for some reason heap_page_prune() is failing to remove an
already-dead tuple.  I suspect this implies a corrupt HOT chain, but
maybe the data is okay and what you've got is really a bug in
heap_page_prune.

Could you send a dump of page 15538 of pg_class, using pg_filedump?
The source code for it is here:
http://sources.redhat.com/rhdb/utilities.html
Best would be -i -f format, ie
	pg_filedump -i -f -R 15538 $PGDATA/base/something/1259

			regards, tom lane



From john.lister-ps@kickstone.com Fri Feb  13 08:20:27 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 821BB632ECD
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 04:20:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 32712-05
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 04:20:16 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-1-a-1.mail.uk.tiscali.com
	(mk-filter-1-a-1.mail.uk.tiscali.com [212.74.100.52])
	by mail.postgresql.org (Postfix) with ESMTP id E41DF632E8A
	for <pgsql-hackers@postgresql.org>;
	Fri, 13 Feb 2009 04:20:01 -0400 (AST)
X-Trace: 
 148853387/mk-filter-1.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.2.74/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.2.74
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AhwCAMO7lElQ4QJK/2dsb2JhbAAIgxbPQoQYBg
X-IronPort-AV: E=Sophos;i="4.38,201,1233532800";
	d="txt'?scan'208";a="148853387"
Received: from 80-225-2-74.dynamic.dial.as9105.com (HELO [192.168.6.11])
	([80.225.2.74])
	by smtp.tiscali.co.uk with ESMTP; 13 Feb 2009 08:19:57 +0000
Message-ID: <49952D33.3000707@kickstone.com>
Date: Fri, 13 Feb 2009 08:20:03 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com>
In-Reply-To: <4994A45B.4030707@kickstone.com>
Content-Type: multipart/mixed; boundary="------------020802020009040507030008"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/726
X-Sequence-Number: 134016

This is a multi-part message in MIME format.
--------------020802020009040507030008
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Originally in psql-admin, but copied here at the request of Tom to..

Story so far, transaction log archiving went wrong causing the 
transaction log disk to fill up. Foolishly i deleted the unarchived 
transaction logs (early monday morning) which required a pg_resetxlog to 
get the db up and running again. Since then we've had some minor db 
corruption which has been fixed (mainly duplicate primary keys) except 
for the pg_class table. 

If i do a vacuum full on pg_class i get something like this:

INFO:  vacuuming "pg_catalog.pg_class"INFO:  "pg_class": found 37 
removable, 1845 nonremovable row versions in 18905 pages
DETAIL:  27 dead row versions cannot be removed yet.
Nonremovable row versions range from 160 to 229 bytes long.
There were 933834 unused item pointers.
Total free space (including removable row versions) is 150368692 bytes.
18839 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO:  index "pg_class_oid_index" 
now contains 1813 row versions in 7 pages
DETAIL:  56 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index 
"pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages
DETAIL:  63 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING:  index 
"pg_class_relname_nsp_index" contains 1818 row versions, but table 
contains 1813 row versions
HINT:  Rebuild the index with REINDEX.INFO:  analyzing 
"pg_catalog.pg_class"INFO:  "pg_class": scanned 3000 of 18905 pages, 
containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 
estimated total rows
Total query runtime: 4469 ms.

As you can see there are non-removable dead rows (which slowly grows) 
and the table size is also increasing in size..  A reindex on the 
indexes mentions also succeeds but another vacuum reports the same thing...

In the log files the relevant bits are:

GMT LOG:  relation "pg_class" TID 15538/4: dead HOT-updated tuple --- 
cannot shrink relation
2009-02-12 21:06:40 GMT STATEMENT:  VACUUM FULL VERBOSE ANALYZE pg_class
2009-02-12 21:06:40 GMT WARNING:  index "pg_class_relname_nsp_index" 
contains 1818 row versions, but table contains 1813 row versions


and finally the last message in the psql-admin thread suggested dumping 
the above page, which is attached before.

>Hmm.  The comments in vacuum.c about this case suppose that it could
>only be a transient condition, ie the tuple became dead while we were
>looking at the page.  Evidently it's persistent for you, which means
>that for some reason heap_page_prune() is failing to remove an
>already-dead tuple.  I suspect this implies a corrupt HOT chain, but
>maybe the data is okay and what you've got is really a bug in
>heap_page_prune.

>Could you send a dump of page 15538 of pg_class, using pg_filedump?
>The source code for it is here:
>http://sources.redhat.com/rhdb/utilities.html
>Best would be -i -f format, ie
>	pg_filedump -i -f -R 15538 $PGDATA/base/something/1259
>
>			regards, tom lane



Any help would be appreciated as the pg_class table is constantly 
growing which i'm guessing is going to start to affect performance 
fairly soon. I'd like to avoid a full restore from backup if possible.

Thanks

JOHN

--------------020802020009040507030008
Content-Type: text/plain;
 name="dump.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="dump.txt"


*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0
*
* File: /mnt/data/postgresql/8.3/base/16392/1259
* Options used: -i -f -R 15538
*
* Dump created on: Thu Feb 12 22:30:43 2009
*******************************************************************

Block 15538 ********************************************************
<Header> -----
 Block Offset: 0x07964000         Offsets: Lower     220 (0x00dc)
 Block: Size 8192  Version    4            Upper    5632 (0x1600)
 LSN:  logid    428 recoff 0xbce5c3a0      Special  8192 (0x2000)
 Items:   49                      Free Space: 5412
 TLI: 0x0001  Prune XID: 0x00000000  Flags: 0x0001 (HAS_FREE_LINES)
 Length (including item array): 220

  0000: ac010000 a0c3e5bc 01000100 dc000016  ................
  0010: 00200420 00000000 00000000 00000000  . . ............
  0020: 00000000 609f4001 c09e4001 209e4001  ....`.@...@. .@.
  0030: 809d4001 e09c4001 00000000 00000000  ..@...@.........
  0040: 409c4001 a09b4001 009b4001 609a4001  @.@...@...@.`.@.
  0050: c0994001 20994001 80984001 00000000  ..@. .@...@.....
  0060: 00000000 e0974001 40974001 a0964001  ......@.@.@...@.
  0070: 00964001 00000000 00000000 00000000  ..@.............
  0080: 00000000 00000000 00000000 00000000  ................
  0090: 00000000 00000000 00000000 00000000  ................
  00a0: 00000000 00000000 00000000 00000000  ................
  00b0: 00000000 00000000 00000000 00000000  ................
  00c0: 00000000 00000000 00000000 00000000  ................
  00d0: 00000000 00000000 00000000           ............

<Data> ------
 Item   1 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   2 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   3 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   4 -- Length:  160  Offset: 8032 (0x1f60)  Flags: NORMAL
  XMIN: 471107346  XMAX: 471107808  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 5   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1f60: 1287141c e088141c 00000000 0000b23c  ...............<
  1f70: 05001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1f80: 74656d70 5f717565 75650000 00000000  temp_queue......
  1f90: 00000000 00000000 00000000 00000000  ................
  1fa0: 00000000 00000000 00000000 00000000  ................
  1fb0: 00000000 00000000 00000000 00000000  ................
  1fc0: 15400000 80db0200 00400000 00000000  .@.......@......
  1fd0: 34974300 00000000 00000000 00000000  4.C.............
  1fe0: 00000000 00000000 01007200 03000000  ..........r.....
  1ff0: 00000000 00000000 00010000 1287141c  ................

 Item   5 -- Length:  160  Offset: 7872 (0x1ec0)  Flags: NORMAL
  XMIN: 471107808  XMAX: 471108256  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 6   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1ec0: e088141c a08a141c 00000000 0000b23c  ...............<
  1ed0: 06001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1ee0: 74656d70 5f717565 75650000 00000000  temp_queue......
  1ef0: 00000000 00000000 00000000 00000000  ................
  1f00: 00000000 00000000 00000000 00000000  ................
  1f10: 00000000 00000000 00000000 00000000  ................
  1f20: 15400000 80db0200 00400000 00000000  .@.......@......
  1f30: 39974300 00000000 00000000 00000000  9.C.............
  1f40: 00000000 00000000 01007200 03000000  ..........r.....
  1f50: 00000000 00000000 00010000 de88141c  ................

 Item   6 -- Length:  160  Offset: 7712 (0x1e20)  Flags: NORMAL
  XMIN: 471108256  XMAX: 471108887  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 7   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1e20: a08a141c 178d141c 00000000 0000b23c  ...............<
  1e30: 07001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1e40: 74656d70 5f717565 75650000 00000000  temp_queue......
  1e50: 00000000 00000000 00000000 00000000  ................
  1e60: 00000000 00000000 00000000 00000000  ................
  1e70: 00000000 00000000 00000000 00000000  ................
  1e80: 15400000 80db0200 00400000 00000000  .@.......@......
  1e90: 3e974300 00000000 00000000 00000000  >.C.............
  1ea0: 00000000 00000000 01007200 03000000  ..........r.....
  1eb0: 00000000 00000000 00010000 9f8a141c  ................

 Item   7 -- Length:  160  Offset: 7552 (0x1d80)  Flags: NORMAL
  XMIN: 471108887  XMAX: 471109460  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 8   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1d80: 178d141c 548f141c 00000000 0000b23c  ....T..........<
  1d90: 08001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1da0: 74656d70 5f717565 75650000 00000000  temp_queue......
  1db0: 00000000 00000000 00000000 00000000  ................
  1dc0: 00000000 00000000 00000000 00000000  ................
  1dd0: 00000000 00000000 00000000 00000000  ................
  1de0: 15400000 80db0200 00400000 00000000  .@.......@......
  1df0: 43974300 00000000 00000000 00000000  C.C.............
  1e00: 00000000 00000000 01007200 03000000  ..........r.....
  1e10: 00000000 00000000 00010000 148d141c  ................

 Item   8 -- Length:  160  Offset: 7392 (0x1ce0)  Flags: NORMAL
  XMIN: 471109460  XMAX: 471110027  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 9   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1ce0: 548f141c 8b91141c 00000000 0000b23c  T..............<
  1cf0: 09001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1d00: 74656d70 5f717565 75650000 00000000  temp_queue......
  1d10: 00000000 00000000 00000000 00000000  ................
  1d20: 00000000 00000000 00000000 00000000  ................
  1d30: 00000000 00000000 00000000 00000000  ................
  1d40: 15400000 80db0200 00400000 00000000  .@.......@......
  1d50: 48974300 00000000 00000000 00000000  H.C.............
  1d60: 00000000 00000000 01007200 03000000  ..........r.....
  1d70: 00000000 00000000 00010000 548f141c  ............T...

 Item   9 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  10 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  11 -- Length:  160  Offset: 7232 (0x1c40)  Flags: NORMAL
  XMIN: 471111085  XMAX: 471111481  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 12   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1c40: ad95141c 3997141c 00000000 0000b23c  ....9..........<
  1c50: 0c001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1c60: 74656d70 5f717565 75650000 00000000  temp_queue......
  1c70: 00000000 00000000 00000000 00000000  ................
  1c80: 00000000 00000000 00000000 00000000  ................
  1c90: 00000000 00000000 00000000 00000000  ................
  1ca0: 15400000 80db0200 00400000 00000000  .@.......@......
  1cb0: 57974300 00000000 00000000 00000000  W.C.............
  1cc0: 00000000 00000000 01007200 03000000  ..........r.....
  1cd0: 00000000 00000000 00010000 ad95141c  ................

 Item  12 -- Length:  160  Offset: 7072 (0x1ba0)  Flags: NORMAL
  XMIN: 471111481  XMAX: 471112070  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 13   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1ba0: 3997141c 8699141c 00000000 0000b23c  9..............<
  1bb0: 0d001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1bc0: 74656d70 5f717565 75650000 00000000  temp_queue......
  1bd0: 00000000 00000000 00000000 00000000  ................
  1be0: 00000000 00000000 00000000 00000000  ................
  1bf0: 00000000 00000000 00000000 00000000  ................
  1c00: 15400000 80db0200 00400000 00000000  .@.......@......
  1c10: 5c974300 00000000 00000000 00000000  \.C.............
  1c20: 00000000 00000000 01007200 03000000  ..........r.....
  1c30: 00000000 00000000 00010000 3897141c  ............8...

 Item  13 -- Length:  160  Offset: 6912 (0x1b00)  Flags: NORMAL
  XMIN: 471112070  XMAX: 471112509  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 14   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1b00: 8699141c 3d9b141c 00000000 0000b23c  ....=..........<
  1b10: 0e001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1b20: 74656d70 5f717565 75650000 00000000  temp_queue......
  1b30: 00000000 00000000 00000000 00000000  ................
  1b40: 00000000 00000000 00000000 00000000  ................
  1b50: 00000000 00000000 00000000 00000000  ................
  1b60: 15400000 80db0200 00400000 00000000  .@.......@......
  1b70: 61974300 00000000 00000000 00000000  a.C.............
  1b80: 00000000 00000000 01007200 03000000  ..........r.....
  1b90: 00000000 00000000 00010000 7e99141c  ............~...

 Item  14 -- Length:  160  Offset: 6752 (0x1a60)  Flags: NORMAL
  XMIN: 471112509  XMAX: 471113050  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 15   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1a60: 3d9b141c 5a9d141c 00000000 0000b23c  =...Z..........<
  1a70: 0f001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1a80: 74656d70 5f717565 75650000 00000000  temp_queue......
  1a90: 00000000 00000000 00000000 00000000  ................
  1aa0: 00000000 00000000 00000000 00000000  ................
  1ab0: 00000000 00000000 00000000 00000000  ................
  1ac0: 15400000 80db0200 00400000 00000000  .@.......@......
  1ad0: 66974300 00000000 00000000 00000000  f.C.............
  1ae0: 00000000 00000000 01007200 03000000  ..........r.....
  1af0: 00000000 00000000 00010000 1b9b141c  ................

 Item  15 -- Length:  160  Offset: 6592 (0x19c0)  Flags: NORMAL
  XMIN: 471113050  XMAX: 471113635  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 16   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  19c0: 5a9d141c a39f141c 00000000 0000b23c  Z..............<
  19d0: 10001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  19e0: 74656d70 5f717565 75650000 00000000  temp_queue......
  19f0: 00000000 00000000 00000000 00000000  ................
  1a00: 00000000 00000000 00000000 00000000  ................
  1a10: 00000000 00000000 00000000 00000000  ................
  1a20: 15400000 80db0200 00400000 00000000  .@.......@......
  1a30: 6b974300 00000000 00000000 00000000  k.C.............
  1a40: 00000000 00000000 01007200 03000000  ..........r.....
  1a50: 00000000 00000000 00010000 5a9d141c  ............Z...

 Item  16 -- Length:  160  Offset: 6432 (0x1920)  Flags: NORMAL
  XMIN: 471113635  XMAX: 471114151  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 17   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1920: a39f141c a7a1141c 00000000 0000b23c  ...............<
  1930: 11001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1940: 74656d70 5f717565 75650000 00000000  temp_queue......
  1950: 00000000 00000000 00000000 00000000  ................
  1960: 00000000 00000000 00000000 00000000  ................
  1970: 00000000 00000000 00000000 00000000  ................
  1980: 15400000 80db0200 00400000 00000000  .@.......@......
  1990: 70974300 00000000 00000000 00000000  p.C.............
  19a0: 00000000 00000000 01007200 03000000  ..........r.....
  19b0: 00000000 00000000 00010000 9f9f141c  ................

 Item  17 -- Length:  160  Offset: 6272 (0x1880)  Flags: NORMAL
  XMIN: 471114151  XMAX: 471114646  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 18   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1880: a7a1141c 96a3141c 00000000 0000b23c  ...............<
  1890: 12001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  18a0: 74656d70 5f717565 75650000 00000000  temp_queue......
  18b0: 00000000 00000000 00000000 00000000  ................
  18c0: 00000000 00000000 00000000 00000000  ................
  18d0: 00000000 00000000 00000000 00000000  ................
  18e0: 15400000 80db0200 00400000 00000000  .@.......@......
  18f0: 75974300 00000000 00000000 00000000  u.C.............
  1900: 00000000 00000000 01007200 03000000  ..........r.....
  1910: 00000000 00000000 00010000 a7a1141c  ................

 Item  18 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  19 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  20 -- Length:  160  Offset: 6112 (0x17e0)  Flags: NORMAL
  XMIN: 471115662  XMAX: 471116262  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 21   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  17e0: 8ea7141c e6a9141c 00000000 0000b23c  ...............<
  17f0: 15001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1800: 74656d70 5f717565 75650000 00000000  temp_queue......
  1810: 00000000 00000000 00000000 00000000  ................
  1820: 00000000 00000000 00000000 00000000  ................
  1830: 00000000 00000000 00000000 00000000  ................
  1840: 15400000 80db0200 00400000 00000000  .@.......@......
  1850: 84974300 00000000 00000000 00000000  ..C.............
  1860: 00000000 00000000 01007200 03000000  ..........r.....
  1870: 00000000 00000000 00010000 8ea7141c  ................

 Item  21 -- Length:  160  Offset: 5952 (0x1740)  Flags: NORMAL
  XMIN: 471116262  XMAX: 471116790  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 22   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1740: e6a9141c f6ab141c 00000000 0000b23c  ...............<
  1750: 16001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  1760: 74656d70 5f717565 75650000 00000000  temp_queue......
  1770: 00000000 00000000 00000000 00000000  ................
  1780: 00000000 00000000 00000000 00000000  ................
  1790: 00000000 00000000 00000000 00000000  ................
  17a0: 15400000 80db0200 00400000 00000000  .@.......@......
  17b0: 89974300 00000000 00000000 00000000  ..C.............
  17c0: 00000000 00000000 01007200 03000000  ..........r.....
  17d0: 00000000 00000000 00010000 e6a9141c  ................

 Item  22 -- Length:  160  Offset: 5792 (0x16a0)  Flags: NORMAL
  XMIN: 471116790  XMAX: 471117318  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 23   Attributes: 27   Size: 32
  infomask: 0x2509 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_COMMITTED|UPDATED|HOT_UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  16a0: f6ab141c 06ae141c 00000000 0000b23c  ...............<
  16b0: 17001bc0 092520ff ffff0100 7edb0200  .....% .....~...
  16c0: 74656d70 5f717565 75650000 00000000  temp_queue......
  16d0: 00000000 00000000 00000000 00000000  ................
  16e0: 00000000 00000000 00000000 00000000  ................
  16f0: 00000000 00000000 00000000 00000000  ................
  1700: 15400000 80db0200 00400000 00000000  .@.......@......
  1710: 8e974300 00000000 00000000 00000000  ..C.............
  1720: 00000000 00000000 01007200 03000000  ..........r.....
  1730: 00000000 00000000 00010000 f6ab141c  ................

 Item  23 -- Length:  160  Offset: 5632 (0x1600)  Flags: NORMAL
  XMIN: 471117318  XMAX: 0  CID|XVAC: 0  OID: 187262
  Block Id: 15538  linp Index: 23   Attributes: 27   Size: 32
  infomask: 0x2909 (HASNULL|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED|HEAP_ONLY)
  t_bits: [0]: 0xff [1]: 0xff [2]: 0xff [3]: 0x01

  1600: 06ae141c 00000000 00000000 0000b23c  ...............<
  1610: 17001b80 092920ff ffff0100 7edb0200  .....) .....~...
  1620: 74656d70 5f717565 75650000 00000000  temp_queue......
  1630: 00000000 00000000 00000000 00000000  ................
  1640: 00000000 00000000 00000000 00000000  ................
  1650: 00000000 00000000 00000000 00000000  ................
  1660: 15400000 80db0200 00400000 00000000  .@.......@......
  1670: 93974300 00000000 00000000 00000000  ..C.............
  1680: 00000000 00000000 01007200 03000000  ..........r.....
  1690: 00000000 00000000 00010000 06ae141c  ................

 Item  24 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  25 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  26 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  27 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  28 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  29 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  30 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  31 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  32 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  33 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  34 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  35 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  36 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  37 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  38 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  39 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  40 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  41 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  42 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  43 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  44 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  45 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  46 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  47 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  48 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item  49 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED


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

--------------020802020009040507030008--



From john.lister-ps@kickstone.com Fri Feb  13 09:17:49 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id DCA7F633103
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 05:17:49 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 94897-01
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 05:17:46 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-3-a-1.mail.uk.tiscali.com
	(mk-filter-3-a-1.mail.uk.tiscali.com [212.74.100.54])
	by mail.postgresql.org (Postfix) with ESMTP id D42DB63309A
	for <pgsql-hackers@postgresql.org>;
	Fri, 13 Feb 2009 05:17:36 -0400 (AST)
X-Trace: 
 145348625/mk-filter-3.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.2.74/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.2.74
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AhwCAFvJlElQ4QJK/2dsb2JhbAAIgxbPP4QYBg
X-IronPort-AV: E=Sophos;i="4.38,201,1233532800"; d="scan'208";a="145348625"
Received: from 80-225-2-74.dynamic.dial.as9105.com (HELO [192.168.6.11])
	([80.225.2.74])
	by smtp.tiscali.co.uk with ESMTP; 13 Feb 2009 09:17:33 +0000
Message-ID: <49953AB3.50304@kickstone.com>
Date: Fri, 13 Feb 2009 09:17:39 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: Database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
In-Reply-To: <49952D33.3000707@kickstone.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/736
X-Sequence-Number: 134026

 >Please send to pgsql-hackers --- I'd like to get more eyeballs on this.
 >There's no personally identifiable information here except that you've
 >got a table named temp_queue that you've repeatedly TRUNCATEd or
 >CLUSTERed or some such (likely the former since the reltuples counts
 >are all zero). It'd be useful to know exactly what you were doing
 >with it, though.
 >
 > regards, tom lane

More info as requested....

TEMP_QUEUE is used continously throughout the day (for some reason which 
i chose not to use a normal temp table) data is grouped and inserted 
from other tables, before being processed, and you are right the table 
is then truncated before repeating - this happens at least once a minute 
throughout the day..

After restarted following the pg_resetxlog, i noticed that the 
temp_queue table was missing (had been created a day ago). I couldn't 
select from it and had problems trying to recreate it (bits seemed to 
exist already). I couldn't see it in pg_class or the other catalogs, but 
after selecting to ignore the presumably corrupt indexes it appeared so 
i manually removed the references (I've since learned about the -P 
option to the server which may have helped here). The corruption in 
pg_class manifested itself as multiple rows (for this and another table) 
with the same OIDs

So not sure at what point what if anything got corrupted and what my 
subsequent actions did to compound this..

Thanks


John Lister wrote:
> Originally in psql-admin, but copied here at the request of Tom to..
>
> Story so far, transaction log archiving went wrong causing the 
> transaction log disk to fill up. Foolishly i deleted the unarchived 
> transaction logs (early monday morning) which required a pg_resetxlog 
> to get the db up and running again. Since then we've had some minor db 
> corruption which has been fixed (mainly duplicate primary keys) except 
> for the pg_class table.
> If i do a vacuum full on pg_class i get something like this:
>
> INFO:  vacuuming "pg_catalog.pg_class"INFO:  "pg_class": found 37 
> removable, 1845 nonremovable row versions in 18905 pages
> DETAIL:  27 dead row versions cannot be removed yet.
> Nonremovable row versions range from 160 to 229 bytes long.
> There were 933834 unused item pointers.
> Total free space (including removable row versions) is 150368692 bytes.
> 18839 pages are or will become empty, including 0 at the end of the 
> table.
> 0 pages containing 0 free bytes are potential move destinations.
> CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO:  index "pg_class_oid_index" 
> now contains 1813 row versions in 7 pages
> DETAIL:  56 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO:  index 
> "pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages
> DETAIL:  63 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING:  index 
> "pg_class_relname_nsp_index" contains 1818 row versions, but table 
> contains 1813 row versions
> HINT:  Rebuild the index with REINDEX.INFO:  analyzing 
> "pg_catalog.pg_class"INFO:  "pg_class": scanned 3000 of 18905 pages, 
> containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 
> estimated total rows
> Total query runtime: 4469 ms.
>
> As you can see there are non-removable dead rows (which slowly grows) 
> and the table size is also increasing in size..  A reindex on the 
> indexes mentions also succeeds but another vacuum reports the same 
> thing...
>
> In the log files the relevant bits are:
>
> GMT LOG:  relation "pg_class" TID 15538/4: dead HOT-updated tuple --- 
> cannot shrink relation
> 2009-02-12 21:06:40 GMT STATEMENT:  VACUUM FULL VERBOSE ANALYZE pg_class
> 2009-02-12 21:06:40 GMT WARNING:  index "pg_class_relname_nsp_index" 
> contains 1818 row versions, but table contains 1813 row versions
>
>
> and finally the last message in the psql-admin thread suggested 
> dumping the above page, which is attached before.
>
>> Hmm.  The comments in vacuum.c about this case suppose that it could
>> only be a transient condition, ie the tuple became dead while we were
>> looking at the page.  Evidently it's persistent for you, which means
>> that for some reason heap_page_prune() is failing to remove an
>> already-dead tuple.  I suspect this implies a corrupt HOT chain, but
>> maybe the data is okay and what you've got is really a bug in
>> heap_page_prune.
>
>> Could you send a dump of page 15538 of pg_class, using pg_filedump?
>> The source code for it is here:
>> http://sources.redhat.com/rhdb/utilities.html
>> Best would be -i -f format, ie
>>     pg_filedump -i -f -R 15538 $PGDATA/base/something/1259
>>
>>             regards, tom lane
>
>
>
> Any help would be appreciated as the pg_class table is constantly 
> growing which i'm guessing is going to start to affect performance 
> fairly soon. I'd like to avoid a full restore from backup if possible.
>
> Thanks
>
> JOHN
> ------------------------------------------------------------------------
>
>
>   



From tgl@sss.pgh.pa.us Fri Feb  13 16:19:11 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0C43E63225E
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 12:19:11 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 40774-03
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 12:19:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id C984F632ECD
	for <pgsql-hackers@postgresql.org>;
	Fri, 13 Feb 2009 12:19:07 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n1DGJ1fr003162;
	Fri, 13 Feb 2009 11:19:01 -0500 (EST)
To: John Lister <john.lister-ps@kickstone.com>
cc: pgsql-hackers@postgresql.org
Subject: Re: Database corruption help 
In-reply-to: <49952D33.3000707@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
Comments: In-reply-to John Lister <john.lister-ps@kickstone.com>
	message dated "Fri, 13 Feb 2009 08:20:03 +0000"
Date: Fri, 13 Feb 2009 11:19:00 -0500
Message-ID: <3161.1234541940@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/763
X-Sequence-Number: 134053

John Lister <john.lister-ps@kickstone.com> writes:
> Originally in psql-admin, but copied here at the request of Tom to..

Thanks for forwarding this.  The reason I wanted to call it to the
attention of pgsql-hackers is that the page contents seem a bit odd,
and I'm not sure that we should just write it off as "pilot error".
What we've got here is a page full of transient states of the pg_class
row for temp_queue, which as you explained is constantly being
re-TRUNCATEd by your application.  So the data contents of each state
of the row vary only in relfilenode, as expected.  One would also expect
that all the copies on a particular page of pg_class would form a single
HOT chain (the database is 8.3.5).  What we've actually got is a chain
that is broken in two places and lacks a root tuple(!).

How could it have got that way?  John's ill-advised removal of the
transaction logs should have resulted only in the page being a lot older
than it should be, not in a logically corrupt page.

The only other corruption mechanism I can think of is that pg_clog might
contain commit bits for some logically inconsistent set of transaction
numbers, due to some pages of pg_clog having made it to disk and others
not.  That could result in some of the intermediate tuples in the chain
not being seen as dead --- but that's not what we see here either.

Aside from the "how did this happen" puzzle, the real point of any
investigation of course ought to be whether we can make heap_page_prune
more robust.  At the very least it's undesirable to be leaving the page
in a state where VACUUM FULL will decide it can't shrink.

Ideas anyone?

			regards, tom lane



From tgl@sss.pgh.pa.us Fri Feb  13 16:28:38 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 505E7632ECD
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 12:28:38 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 49121-06
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 12:28:35 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 475E563225E
	for <pgsql-hackers@postgresql.org>;
	Fri, 13 Feb 2009 12:28:35 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n1DGSSTH003305;
	Fri, 13 Feb 2009 11:28:28 -0500 (EST)
To: John Lister <john.lister-ps@kickstone.com>
cc: pgsql-hackers@postgresql.org
Subject: Re: Database corruption help 
In-reply-to: <49952D33.3000707@kickstone.com> 
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
Comments: In-reply-to John Lister <john.lister-ps@kickstone.com>
	message dated "Fri, 13 Feb 2009 08:20:03 +0000"
Date: Fri, 13 Feb 2009 11:28:28 -0500
Message-ID: <3304.1234542508@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/765
X-Sequence-Number: 134055

John Lister <john.lister-ps@kickstone.com> writes:
> Any help would be appreciated as the pg_class table is constantly 
> growing which i'm guessing is going to start to affect performance 
> fairly soon. I'd like to avoid a full restore from backup if possible.

BTW, what I would recommend as a recovery action is to zero out that
page of pg_class while the postmaster is stopped.  We know that none
of those rows are useful to you, and there shouldn't be any index
entries pointing at them (since they're all HOT tuples), so at least
in theory that won't cause any damage.  Then you can try another
VACUUM FULL and see if there are any more pages with, er, issues.

If you're on a machine that has /dev/zero then something like this
should work:

dd bs=8k count=1 seek=15538 conv=notrunc if=/dev/zero of=$PGDATA/base/16392/1259

but it'd be a good idea to save a copy of the target file so you can try
again if you mess up.

Also, it'd really be prudent to do a dump, initdb, reload once you
get to a point where pg_dump succeeds without complaints.  We don't
have any good way to know what other corruption might be lurking
undetected.

			regards, tom lane



From john.lister-ps@kickstone.com Fri Feb  13 17:57:10 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5807263309E
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 13:57:10 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 42954-08
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 13 Feb 2009 13:57:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mk-filter-3-a-1.mail.uk.tiscali.com
	(mk-filter-3-a-1.mail.uk.tiscali.com [212.74.100.54])
	by mail.postgresql.org (Postfix) with ESMTP id A680163225E
	for <pgsql-hackers@postgresql.org>;
	Fri, 13 Feb 2009 13:57:07 -0400 (AST)
X-Trace: 
 145699559/mk-filter-3.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.46.2/None/john.lister-ps@kickstone.com
X-SBRS: None
X-RemoteIP: 80.225.46.2
X-IP-MAIL-FROM: john.lister-ps@kickstone.com
X-MUA: Thunderbird 2.0.0.19 (Windows/20081209)
X-IP-BHB: Once
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: AsoBADxDlUlQ4S4C/2dsb2JhbAAI0kKEHAY
X-IronPort-AV: E=Sophos;i="4.38,203,1233532800"; d="scan'208";a="145699559"
Received: from 80-225-46-2.dynamic.dial.as9105.com (HELO [192.168.6.11])
	([80.225.46.2])
	by smtp.tiscali.co.uk with ESMTP; 13 Feb 2009 17:57:04 +0000
Message-ID: <4995B477.7090805@kickstone.com>
Date: Fri, 13 Feb 2009 17:57:11 +0000
From: John Lister <john.lister-ps@kickstone.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-hackers@postgresql.org
Subject: Re: Database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
	<3304.1234542508@sss.pgh.pa.us>
In-Reply-To: <3304.1234542508@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/768
X-Sequence-Number: 134058

Cheers, i'll give it ago. I'm probably going to do a full restore over 
the weekend while i can shut things down without too many complaints...

I can save any of the files if you are interested in them later on...

JOHN

Tom Lane wrote:
> John Lister <john.lister-ps@kickstone.com> writes:
>   
>> Any help would be appreciated as the pg_class table is constantly 
>> growing which i'm guessing is going to start to affect performance 
>> fairly soon. I'd like to avoid a full restore from backup if possible.
>>     
>
> BTW, what I would recommend as a recovery action is to zero out that
> page of pg_class while the postmaster is stopped.  We know that none
> of those rows are useful to you, and there shouldn't be any index
> entries pointing at them (since they're all HOT tuples), so at least
> in theory that won't cause any damage.  Then you can try another
> VACUUM FULL and see if there are any more pages with, er, issues.
>
> If you're on a machine that has /dev/zero then something like this
> should work:
>
> dd bs=8k count=1 seek=15538 conv=notrunc if=/dev/zero of=$PGDATA/base/16392/1259
>
> but it'd be a good idea to save a copy of the target file so you can try
> again if you mess up.
>
> Also, it'd really be prudent to do a dump, initdb, reload once you
> get to a point where pg_dump succeeds without complaints.  We don't
> have any good way to know what other corruption might be lurking
> undetected.
>
> 			regards, tom lane
>   



From heikki.linnakangas@enterprisedb.com Sat Feb  14 08:26:22 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2C299632543
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sat, 14 Feb 2009 04:26:22 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 50039-06
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sat, 14 Feb 2009 04:26:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og106.obsmtp.com (exprod7og106.obsmtp.com [64.18.2.165])
	by mail.postgresql.org (Postfix) with SMTP id B7D14632552
	for <pgsql-hackers@postgresql.org>;
	Sat, 14 Feb 2009 04:26:15 -0400 (AST)
Received: from source ([66.249.92.174]) by exprod7ob106.postini.com
	([64.18.6.12]) with SMTP
	ID DSNKSZaAJiJTtCYUG5S5OHsSTBSBNragyofy@postini.com;
	Sat, 14 Feb 2009 00:26:18 PST
Received: by ug-out-1314.google.com with SMTP id z34so98234ugc.28
	for <pgsql-hackers@postgresql.org>;
	Sat, 14 Feb 2009 00:26:13 -0800 (PST)
Received: by 10.210.90.20 with SMTP id n20mr2371726ebb.72.1234599973385;
	Sat, 14 Feb 2009 00:26:13 -0800 (PST)
Received: from ?80.223.212.7? (dsl-hkibrasgw2-fed4df00-7.dhcp.inet.fi
	[80.223.212.7])
	by mx.google.com with ESMTPS id c9sm1614441nfi.34.2009.02.14.00.26.12
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Sat, 14 Feb 2009 00:26:12 -0800 (PST)
Message-ID: <49968023.8030404@enterprisedb.com>
Date: Sat, 14 Feb 2009 10:26:11 +0200
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.19 (X11/20090103)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: John Lister <john.lister-ps@kickstone.com>, pgsql-hackers@postgresql.org
Subject: Re: Database corruption help
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
	<3161.1234541940@sss.pgh.pa.us>
In-Reply-To: <3161.1234541940@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200902/799
X-Sequence-Number: 134089

Tom Lane wrote:
> Aside from the "how did this happen" puzzle, the real point of any
> investigation of course ought to be whether we can make heap_page_prune
> more robust.  At the very least it's undesirable to be leaving the page
> in a state where VACUUM FULL will decide it can't shrink.

I'm as puzzled as you are on how it happened.

The fundamental problem here is that we have HOT-updated tuples that are 
missing the root tuple. heap_prune_chain doesn't know how to remove the 
dead tuples from such chains, and neither does vacuum. What's worse is 
that there's no index pointer to the live tuple in the chain, and even 
VACUUM FULL doesn't fix that.

Could we modify heap_page_prune so that it detects such orphaned HOT 
tuples, and clears the HOT_UPDATED flag for them? That would at least 
let you recover. I'm pretty wary of trying to "fix" things after 
corruption in general since you can't be sure what's correct and what's 
not, but clearing the HOT_UPDATED flag seems safe and more likely to 
help than hurt. Clearing the flag would let the tuple become indexed 
again in a VACUUM FULL or REINDEX, so it would fix the inconsistency 
that otherwise a sequential scan can see the tuple but an index scan can 
not. It would also allow the next heap_page_prune operation to remove 
the remaining dead tuples in the chain.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com



From simon@2ndQuadrant.com Sun Feb  15 10:35:34 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C284D633163
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sun, 15 Feb 2009 06:35:34 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 33579-01
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Sun, 15 Feb 2009 06:35:28 -0400 (AST)
Received: from mx1.hub.org (mx1.hub.org [200.46.208.106])
	by mail.postgresql.org (Postfix) with ESMTP id 2BBA7633148
	for <pgsql-hackers@postgresql.org>;
	Sun, 15 Feb 2009 06:17:50 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail128148.authsmtp.net (outmail128148.authsmtp.net
	[62.13.128.148]) by mx1.hub.org (Postfix) with ESMTP id 9336D1259CD2
	for <pgsql-hackers@postgresql.org>;
	Sun, 15 Feb 2009 06:17:48 -0400 (AST)
Received: from mail-c193.authsmtp.com (mail-c193.authsmtp.com [62.13.128.118])
	by punt5.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id
	n1FAF5xL004058; Sun, 15 Feb 2009 10:15:05 GMT
Received: from [192.168.0.3] (85-211-101-162.dyn.gotadsl.co.uk
	[85.211.101.162]) (authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id n1FAF4mS051098;
	Sun, 15 Feb 2009 10:15:04 GMT
Subject: Re: Database corruption help
From: Simon Riggs <simon@2ndQuadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John Lister <john.lister-ps@kickstone.com>, pgsql-hackers@postgresql.org
In-Reply-To: <3161.1234541940@sss.pgh.pa.us>
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<17205.1234200693@sss.pgh.pa.us>
	<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
	<4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us>
	<49908D72.7040904@kickstone.com> <25753.1234224090@sss.pgh.pa.us>
	<4991338B.704@kickstone.com> <4994906B.6090408@kickstone.com>
	<17204.1234474784@sss.pgh.pa.us> <4994987F.1030204@kickstone.com>
	<17764.1234476783@sss.pgh.pa.us> <4994A45B.4030707@kickstone.com>
	<49952D33.3000707@kickstone.com>  <3161.1234541940@sss.pgh.pa.us>
Content-Type: text/plain
Date: Sun, 15 Feb 2009 10:18:10 +0000
Message-Id: <1234693090.4500.1142.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
X-Mailer: Evolution 2.12.0 
Content-Transfer-Encoding: 7bit
X-Server-Quench: 83ab4aed-fb49-11dd-8a51-002264978518
X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ
	NksadRtFaQBba1xT HGQLW11EUV17WGZ/ aw8fZQBDYEtPQQRq
	UVZIRFBNFgBqBAMB SEAfJGgEc1gleHtx ZU9qEHFdVEc0fUUs
	EEoFQDkbMzVkYX0e URQMagsFdQJXfh9H aFZ7XXQFZGQPKBg1
	TUcAHxkaHhhlExEd bggRJFgbWktDFTkm XQ0aATQoGwUFSiEr RyAn
X-Authentic-SMTP: 61633235383639.pelican.dmpriest.net.uk:1378/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/813
X-Sequence-Number: 134103


On Fri, 2009-02-13 at 11:19 -0500, Tom Lane wrote:

> Aside from the "how did this happen" puzzle, the real point of any
> investigation of course ought to be whether we can make heap_page_prune
> more robust.  At the very least it's undesirable to be leaving the page
> in a state where VACUUM FULL will decide it can't shrink.

I think it would be useful to have a function that can scan a table to
see if this issue exists. ISTM if it has happened once it can have
happened many times. It would be useful to have some more trouble
reports so we can assess the size and scope of this problem.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support




From pavan.deolasee@gmail.com Wed Feb  25 17:42:37 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 88A4A6332D3
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Wed, 25 Feb 2009 13:42:37 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 04161-06
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Wed, 25 Feb 2009 13:42:32 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.29])
	by mail.postgresql.org (Postfix) with ESMTP id 74FFB6332CF
	for <pgsql-hackers@postgresql.org>;
	Wed, 25 Feb 2009 13:42:32 -0400 (AST)
Received: by yw-out-2324.google.com with SMTP id 5so87709ywh.73
	for <pgsql-hackers@postgresql.org>;
	Wed, 25 Feb 2009 09:42:31 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=dC5/ifB0Rbx/1ysQmthfxkDV6qiRpLZW61LzUspdt+g=;
	b=mKSCTgLtkm1RVTeCp8Mh/tn0Atw9N/GHTGg1s7TgzD4t3unBu8UUoXs0gx5f3WAPht
	zTJGIPKR/JGFNLa0j7r3HUK5lfrVxNhvEPpa4xfpZV64t8lz7t0P3xzPQa3KK70pHWeQ
	2i31IhoRqTkmVcsy6ZKex4QVg5mxLn9m5qlBk=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=tr+Q3scd4oAfP8zB6T/Yg2+M3yEQkqSF1Q1rZOaDqXrxpGxsksU0aJ8x7eJAXj3UKX
	t1I5jb9StZIW5fXMLsNzPYHJtsGZ1VO3z2xrF2ZmlNMsraPxUWQ2EIe934FyJWp/n44V
	/NcY5glQoV7rE7zSQ8JsfaaXhyMG65oRTS1R0=
MIME-Version: 1.0
Received: by 10.143.42.7 with SMTP id u7mr157491wfj.99.1235583751299; Wed, 25
	Feb 2009 09:42:31 -0800 (PST)
In-Reply-To: <3161.1234541940@sss.pgh.pa.us>
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
	<25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com>
	<4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us>
	<4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us>
	<4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com>
	<3161.1234541940@sss.pgh.pa.us>
Date: Wed, 25 Feb 2009 23:12:31 +0530
Message-ID: <2e78013d0902250942u7d6103fev96e3b6d4eb20e5b8@mail.gmail.com>
Subject: Re: Database corruption help
From: Pavan Deolasee <pavan.deolasee@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John Lister <john.lister-ps@kickstone.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/1224
X-Sequence-Number: 134514

On Fri, Feb 13, 2009 at 9:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> The only other corruption mechanism I can think of is that pg_clog might
> contain commit bits for some logically inconsistent set of transaction
> numbers, due to some pages of pg_clog having made it to disk and others
> not. =A0That could result in some of the intermediate tuples in the chain
> not being seen as dead --- but that's not what we see here either.
>

Or can it be otherwise where some transactions which in fact
committed, are marked as aborted because of clog corruption ? In that
case, some of the intermediate tuples in the HOT chain may get removed
(because we handle aborted heap-only tuples separately) and break the
HOT chain.

I am also looking at the pruning logic to see if I can spot something unusu=
al.

Thanks,
Pavan

--=20
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com