Fixing PostgreSQL corruption with Rails?
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
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.
#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!