How to select a row from a table that has a lot of rows?

[gkhangelani]

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 cann't read block.

[rski]

Did you vacuum that table. Are there any indexes on it if so recreate them.

[gkhangelani]

I tried to vacuum and reindex the table but it kept saying cannot read block. there are indexes in the table.....Many thanks I will try an recreate indexes

[rski]

What about disk, did you check it for bad blocks?

[gkhangelani]

No there are no errors in the disc,

Is there another way to recreat indexes except the following, where pg_toast is the affected table for an example, because I just tried but there was nothing index.out file?


\o /tmp/index.out
select 'reindex table pg_toast.'||relname||';' from pg_class where relname like '%pg_toast%' and relkind ='t';

delete first two and last line of file
go into db
\i /tmp/index.out

[rski]

I was thinking about recreating (drop and create) not reindexing, but you can try this also earlier.
Are you sure that query

select 'reindex table pg_toast.'||relname||';' from pg_class where relname like '%pg_toast%' and relkind ='t';

returns any rows?

[gkhangelani]

It doesn't return any rows

[rski]

So you have an answer why the file is empty.
I didn't use 7.3.4, is there pg_indexes view, if so you can use it instead pg_class;

[gkhangelani]

I tried dropping and recreating indexes for the affected table but still no luck.

db0303# SELECT * from pg_indexes where tablename = 'hp_tran';
schemaname | tablename | indexname | indexdef
------------+-----------+-------------------------+--------------------------------------------------------------------------------------------
public | hp_tran | idx_hptran_hpdoc_id | CREATE INDEX idx_hptran_hpdoc_id ON hp_tran USING btree (hpdoc_id)
public | hp_tran | idx_hp_tran_hp_sub_acno | CREATE INDEX idx_hp_tran_hp_sub_acno ON hp_tran USING btree (hp_sub_acno)
public | hp_tran | idx_hp_tran_cus_acno | CREATE INDEX idx_hp_tran_cus_acno ON hp_tran USING btree (cus_acno)
public | hp_tran | pk_hptran_acc | CREATE UNIQUE INDEX pk_hptran_acc ON hp_tran USING btree (cus_acno, hp_sub_acno, hpdoc_id)
public | hp_tran | idx_hptran_sub_type | CREATE INDEX idx_hptran_sub_type ON hp_tran USING btree (hptran_sub_type)
public | hp_tran | idx_hptran_glc_code | CREATE INDEX idx_hptran_glc_code ON hp_tran USING btree (glc_code)
public | hp_tran | idx_hptran_acno | CREATE INDEX idx_hptran_acno ON hp_tran USING btree (cus_acno, hp_sub_acno)
(7 rows)


Tried dropping the 1st index:

db0303_old=# BEGIN ;
BEGIN
db0303# DROP INDEX idx_hptran_hpdoc_id;
DROP INDEX
db0303=# CREATE INDEX idx_hptran_hpdoc_id ON hp_tran USING btree (hpdoc_id);
ERROR: cannot read block 15157 of hp_tran: Success
db0303=# ROLLBACK ;
ROLLBACK


Tried the 2nd index:

db0303=# BEGIN ;
BEGIN
db0303=# DROP INDEX idx_hp_tran_hp_sub_acno;
DROP INDEX
db0303=# CREATE INDEX idx_hp_tran_hp_sub_acno ON hp_tran USING btree (hp_sub_acno);
ERROR: cannot read block 15157 of hp_tran: Success
db0303=# ROLLBACK ;
ROLLBACK


If there is another work around please advise, but I think we are going to go with the restore from a back up server if there is no other option

[rski]

did you meet any problems with that database or OS lately?

[gkhangelani]

Maybe there were OS issues but I wouldn't know because other departments like ICT(technical department) takes care of O/S and the Hardware sides of the system.....I suggested that they replace the hardware and then I am going restore the database from the backup server.


But I really appreciate your help, I learned a lot from the ideas you gave me and i will be able to apply them in future.