By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

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

P: 17
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.
Apr 16 '10 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 700
Did you vacuum that table. Are there any indexes on it if so recreate them.
Apr 16 '10 #2

P: 17
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
Apr 16 '10 #3

Expert 100+
P: 700
What about disk, did you check it for bad blocks?
Apr 16 '10 #4

P: 17
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
Apr 16 '10 #5

Expert 100+
P: 700
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?
Apr 16 '10 #6

P: 17
It doesn't return any rows
Apr 16 '10 #7

Expert 100+
P: 700
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;
Apr 16 '10 #8

P: 17
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
Apr 16 '10 #9

Expert 100+
P: 700
did you meet any problems with that database or OS lately?
Apr 16 '10 #10

P: 17
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.
Apr 16 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.