This post originated from an RSS feed registered with Ruby Buzz by Robby Russell. Original Post: Fixing PostgreSQL corruption with Rails? Feed Title: Robby on Rails Feed URL: http://feeds.feedburner.com/RobbyOnRails Feed Description: Ruby on Rails development, consulting, and hosting from the trenches... Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Robby Russell Latest Posts From Robby on Rails

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!