20181116173541_copy_account_stats.rb 1.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
  1. class CopyAccountStats < ActiveRecord::Migration[5.2]
  2. disable_ddl_transaction!
  3. def up
  4. safety_assured do
  5. if supports_upsert?
  6. up_fast
  7. else
  8. up_slow
  9. end
  10. end
  11. end
  12. def down
  13. # Nothing
  14. end
  15. private
  16. def supports_upsert?
  17. version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i
  18. version >= 90500
  19. end
  20. def up_fast
  21. say 'Upsert is available, importing counters using the fast method'
  22. Account.unscoped.select('id').find_in_batches(batch_size: 5_000) do |accounts|
  23. execute <<-SQL.squish
  24. INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at)
  25. SELECT id, statuses_count, following_count, followers_count, created_at, updated_at
  26. FROM accounts
  27. WHERE id IN (#{accounts.map(&:id).join(', ')})
  28. ON CONFLICT (account_id) DO UPDATE
  29. SET statuses_count = EXCLUDED.statuses_count, following_count = EXCLUDED.following_count, followers_count = EXCLUDED.followers_count
  30. SQL
  31. end
  32. end
  33. def up_slow
  34. say 'Upsert is not available in PostgreSQL below 9.5, falling back to slow import of counters'
  35. # We cannot use bulk INSERT or overarching transactions here because of possible
  36. # uniqueness violations that we need to skip over
  37. Account.unscoped.select('id, statuses_count, following_count, followers_count, created_at, updated_at').find_each do |account|
  38. begin
  39. params = [[nil, account.id], [nil, account[:statuses_count]], [nil, account[:following_count]], [nil, account[:followers_count]], [nil, account.created_at], [nil, account.updated_at]]
  40. exec_insert('INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6)', nil, params)
  41. rescue ActiveRecord::RecordNotUnique
  42. next
  43. end
  44. end
  45. end
  46. end