20180812173710_copy_status_stats.rb 1.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
  1. class CopyStatusStats < 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. Status.unscoped.select('id').find_in_batches(batch_size: 5_000) do |statuses|
  23. execute <<-SQL.squish
  24. INSERT INTO status_stats (status_id, reblogs_count, favourites_count, created_at, updated_at)
  25. SELECT id, reblogs_count, favourites_count, created_at, updated_at
  26. FROM statuses
  27. WHERE id IN (#{statuses.map(&:id).join(', ')})
  28. ON CONFLICT (status_id) DO UPDATE
  29. SET reblogs_count = EXCLUDED.reblogs_count, favourites_count = EXCLUDED.favourites_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. Status.unscoped.select('id, reblogs_count, favourites_count, created_at, updated_at').find_each do |status|
  38. begin
  39. params = [[nil, status.id], [nil, status.reblogs_count], [nil, status.favourites_count], [nil, status.created_at], [nil, status.updated_at]]
  40. exec_insert('INSERT INTO status_stats (status_id, reblogs_count, favourites_count, created_at, updated_at) VALUES ($1, $2, $3, $4, $5)', nil, params)
  41. rescue ActiveRecord::RecordNotUnique
  42. next
  43. end
  44. end
  45. end
  46. end