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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • gkhangelani
    New Member
    • Mar 2010
    • 17

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

    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
    Recognized Expert Contributor
    • Dec 2006
    • 700

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

    Comment

    • gkhangelani
      New Member
      • Mar 2010
      • 17

      #3
      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

      Comment

      • rski
        Recognized Expert Contributor
        • Dec 2006
        • 700

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

        Comment

        • gkhangelani
          New Member
          • Mar 2010
          • 17

          #5
          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.'||rel name||';' 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

          Comment

          • rski
            Recognized Expert Contributor
            • Dec 2006
            • 700

            #6
            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.'||rel name||';' from pg_class where relname like '%pg_toast%' and relkind ='t';
            returns any rows?

            Comment

            • gkhangelani
              New Member
              • Mar 2010
              • 17

              #7
              It doesn't return any rows

              Comment

              • rski
                Recognized Expert Contributor
                • Dec 2006
                • 700

                #8
                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;

                Comment

                • gkhangelani
                  New Member
                  • Mar 2010
                  • 17

                  #9
                  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_hpdo c_id | CREATE INDEX idx_hptran_hpdo c_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_typ e)
                  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_hpdo c_id;
                  DROP INDEX
                  db0303=# CREATE INDEX idx_hptran_hpdo c_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

                  Comment

                  • rski
                    Recognized Expert Contributor
                    • Dec 2006
                    • 700

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

                    Comment

                    • gkhangelani
                      New Member
                      • Mar 2010
                      • 17

                      #11
                      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.

                      Comment

                      Working...