pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I find a broken row in a table
3+ messages / 3 participants
[nested] [flat]
* How can I find a broken row in a table
@ 2010-04-16 08:22 Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
0 siblings, 2 replies; 3+ messages in thread
From: Khangelani Gama @ 2010-04-16 08:22 UTC (permalink / raw)
To: pgsql-admin
Please help me, I am using PostgreSQL 7.3.4 running on Redhat5
there is a table that has a broken row, but now I don't know which one is broken. the table has about 20974 pages. is there a command to find this? because I used select commands like: select * from table order by column desc limit X ; select * from table order by column asc limit X; but as soon as I say select * from table; it throws out an error, saying can't read block.
Thanks in advance
Khangelani
Confidentiality Notice:http://ucs.co.za/conf.html
________________________________
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can I find a broken row in a table
@ 2010-04-16 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2010-04-16 14:23 UTC (permalink / raw)
To: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>; +Cc: pgsql-admin
Khangelani Gama <Khangelani.Gama@ucs-software.co.za> writes:
> Please help me, I am using PostgreSQL 7.3.4 running on Redhat5
> there is a table that has a broken row, but now I don't know which one
is broken. the table has about 20974 pages. is there a command to find
this?
You have to use divide-and-conquer. Try
select ctid from table limit N;
and vary N until you find the largest value that *doesn't* produce an
error. The block number in the last ctid from that query is the block
before the bad one, or possibly a small number of blocks before the bad
one if there are some totally-empty blocks.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can I find a broken row in a table
@ 2010-04-16 14:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2010-04-16 14:26 UTC (permalink / raw)
To: pgsql-admin; Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
Khangelani Gama <Khangelani.Gama@ucs-software.co.za> wrote:
> there is a table that has a broken row, but now I don't know which
> one is broken. the table has about 20974 pages.
If there are any indexes on the table which haven't been corrupted,
you might try selecting ranges of rows using one of them, capturing
the undamaged data into a new table. By subdividing damaged ranges
you can do a sort of "binary search" for the bad rows while
recovering the good ones.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-04-16 14:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-16 08:22 How can I find a broken row in a table Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
2010-04-16 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-16 14:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox