Read my latest article: Six days to complete the Rails hosting survey (posted Thu, 24 Apr 2014 14:36:00 GMT)

Fixing PostgreSQL corruption with Rails?

Posted by Sun, 06 Aug 2006 00:16:00 GMT

3 comments Latest by Robert Treat Tue, 08 Aug 2006 17:26:04 GMT

People have been emailing me to let me know that the search on my blog was broken. Today I finally set out to fix the problem, which looked like a complex issue with my PostgreSQL database. I’m not sure how long ago it started so I am not sure what was the cause at the moment. If you did a search on my blog you’d get an application error and behind the scenes, you would see the following error.

PGError: ERROR:  missing chunk number 0 for toast value 58441

Eek!

So, I tried to REINDEX the table and it didn’t solve the problem… so I started poking around with different types of queries to see what I could do to generate the error again. Didn’t take me long to figure out that it had something to do with the body column in the contents table.


db_name=# SELECT count(id) FROM contents WHERE body ~* 'postgresql' LIMIT 20;
ERROR:  missing chunk number 0 for toast value 58441

db_name_=# SELECT count(id) FROM contents WHERE excerpt ~* 'postgresql' LIMIT 20;
 count 
-------
     0
(1 row)

What am I to do? I did some googling (and go figure… the error being returned was caught on my blog by google)... which was amusing.

In the #postgresql channel on freenode they recommended that I try and find the specific row in the table that was causing this error. I decided to just run a for loop in script/console on the Content object in Typo and print out the name of each record until it gave me an error.


for i in 1..30000
  puts Content.find( i ).title
  i =+ 1
end

This began to print out titles of older blog entries and sure enough… the loop died when it hit the following error. :-)


  PostgreSQL sequences in Rails
  When TSearch2 Met AJAX
  ActiveRecord::StatementInvalid: PGError: ERROR:  missing chunk number 0 for toast value 58441
  : SELECT * FROM contents WHERE (contents.id = 1678)  LIMIT 1
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/abstract_adapter.rb:88:in `log'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/postgresql_adapter.rb:137:in `execute'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/postgresql_adapter.rb:351:in `select'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/postgresql_adapter.rb:118:in `select_all'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:431:in `find_by_sql'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:395:in `find'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:393:in `find'
          from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:409:in `find'
          from (irb):23
          from (irb):22
  >> exit

15.seconds.later I logged into psql and ran DELETE FROM contents WHERE id = 1678... and all is well!

Subscribe to my RSS feed Enjoying the content? Be sure to subscribe to my RSS feed.
comments powered by Disqus
Comments

Leave a response

  1. Avatar
    Bob Sun, 06 Aug 2006 16:14:38 GMT

    I guess I wouldn’t say “all is well” unless I better understood the underlying cause for the corruption. What if that row had been important?

  2. Avatar
    Chris Mon, 07 Aug 2006 15:22:42 GMT

    I agree! Where does that leave all us newbie developers who switched from My to Postgres because you convinced us that it was industrial strength and never experienced database corruption?

  3. Avatar
    Robert Treat Tue, 08 Aug 2006 17:26:04 GMT

    Hopefully it leaves you checking your hardware because problems like this are a sign of disk failures and/or bad ram. (As opposed to my, where it would probably be a sign of a problem with my)