From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: 8.3.5 broken after power fail
Date: Sat, 21 Feb 2009 09:23:32 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.13-ZMI; KDE/4.1.3; x86_64; ; )
References: <de3f3b06f55e4f88b0ced132435bcd96@webmail.zmi.at>
	<0b99ef815980dac4a445e1eb1da0d130@webmail.zmi.at>
	<dcc563d10902181354m5bfe6a14n1570c911ba26764f@mail.gmail.com>
In-Reply-To: <dcc563d10902181354m5bfe6a14n1570c911ba26764f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1330702.DmcfkgyC8B";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200902210923.37082@zmi.at>

--nextPart1330702.DmcfkgyC8B
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Mittwoch 18 Februar 2009 Scott Marlowe wrote:
> Look into zero damaged pages setting.

Thanks for the hint Scott, I tried it and did:

select * into mb from dbmail_messageblks;
WARNING:  invalid page header in block 973075 of relation=20
"pg_toast_1281127"; zeroing out page
ERROR:  missing chunk number 0 for toast value 1623370 in=20
pg_toast_1281127

That missing chunks still stop the query. I found out what messageblks=20
are destroyed, and can get out all the rest. There was something=20
interesting:

As the data in that table is about 10GB and therefore time consuming to=20
read, I tried reading only small parts of that big "messageblk" field of=20
type "bytea", which is the longest field. So I tried reading every row=20
with char_length(messageblk,5) and on success noted that block as good.=20
But upon reading there still could be errors, meaning you must really=20
test to read the full messageblk field. Just if someone else needs to=20
perform such a test.

Also a question: Because I must read all data, the psql client runs out=20
of memory, trying to cache all the 10GB from that table. I circumvented=20
this with selecting only parts of the table all the time. Is there a=20
smart way to do such a select without caching the results in memory? Is=20
that what temporary tables and "select into" are made for? I just want=20
to know the recommended way for doing huge queries.

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4


--nextPart1330702.DmcfkgyC8B
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)

iEYEABECAAYFAkmfugkACgkQzhSR9xwSCbQ/ggCcC540KfFxVx+C808eO4XD1pjO
NvMAoK7b/xRxSJbFFvqZtd/WKZqKod07
=6V34
-----END PGP SIGNATURE-----

--nextPart1330702.DmcfkgyC8B--