CODEWITHSUNDEEP
ruby-on-railspostgresqlmigration
Kasper Grubbe
Kasper Grubbe

Reputation: 63

Getting a deadlock when altering table

I am running the following migrations in my Rails app on a table that contains a lot of rows:

rake db:migrate
*** [] rake aborted!
*** [] An error has occurred, this and all later migrations canceled:
*** [] 
*** [] PG::Error: ERROR:  deadlock detected
*** [] DETAIL:  Process 33319 waits for AccessExclusiveLock on relation 18486 of database 16948; blocked by process 29772.
*** [] Process 29772 waits for ShareLock on transaction 8652; blocked by process 33319.
*** [] HINT:  See server log for query details.
*** [] : ALTER TABLE "topics" DROP "most_recent_post_id"
*** [] 
*** [] Tasks: TOP => db:migrate
*** [] (See full trace by running task with --trace)
 ** [] ==  RemoveMostRecentPostsColumnsOnTopics: migrating 
 ** [] Updated 56875150 rows out of 568715 tries
 ** [] -- remove_column(:topics, :most_recent_post_id)

The code running is this:

def self.up
  rows_updated = 0
  rows_tried = 0

  Topic.find(:all).each do |topic|
    rows_tried += 1
    rows_updated += 1 if topic.update_attribute :updated_at, topic.most_recent_post_created_at
  end

  puts "Updated #{rows_updated} rows out of #{rows_tried} tries"

  remove_column :topics, :most_recent_post_id
  remove_column :topics, :most_recent_post_created_at
end

I then tried to do it as a explicit lock, but when searching of info about the problems I realized that ALTER TABLE already is locking the table with an ACCESS EXCLUSIVE lock, according to this: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

Is there something that I can do to get my changes done?

Upvotes: 1

Views: 5791

Answers (1)

fearless_fool
fearless_fool

Reputation: 35189

You have two processes trying to gain exclusive access:

Process 33319 waits for AccessExclusiveLock on relation 18486 of database 16948; blocked by process 29772.
Process 29772 waits for ShareLock on transaction 8652; blocked by process 33319.

One of them is the your migration task. I'm assuming the other is your server. I suggest:

  • If you're running a development environment, quit your server, run the migration and restart your server.
  • If you're running a production environment and need to run migrations without shutting down the server, you can add a #migrate method to your server app so it runs in the same process.

(To be honest, I'm just starting to dig into PostgreSQL in a multi processing environment -- if I learn more I'll post a better answer.)

Upvotes: 6

Related Questions