Error while bulk inserting in Rails

Question

I am trying to do a bulk insert in Rails ActiveRecord from a different MySQL database. My database is Postgres.

Used the following code and the gem bulk_insert:

batch,batch_size = [], 1_000
records.each do |row|
    batch << params
    if batch.size >= batch_size
        TableName.bulk_insert values: batch
        batch = []
    end
end

But I am getting errors while trying to do so. First 1000 records are inserted fine. After which I get the following:

from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:78:in `block in save!'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:78:in `tap'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:78:in `save!'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:40:in `add'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:63:in `block in add_all'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:63:in `each'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert/worker.rb:63:in `add_all'
from /var/lib/gems/2.3.0/gems/bulk_insert-1.5.0/lib/bulk_insert.rb:13:in `block in bulk_insert'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
from /var/lib/gems/2.3.0/gems/activerecord-5.0.3/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
... 22 levels...
from /var/lib/gems/2.3.0/gems/railties-5.0.3/lib/rails/commands/console_helper.rb:9:in `start'
from /var/lib/gems/2.3.0/gems/railties-5.0.3/lib/rails/commands/commands_tasks.rb:78:in `console'
from /var/lib/gems/2.3.0/gems/railties-5.0.3/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
from /var/lib/gems/2.3.0/gems/railties-5.0.3/lib/rails/commands.rb:18:in `<top (required)>'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:293:in `require'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:293:in `block in require'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:259:in `load_dependency'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:293:in `require'
from /home/user/rails-app/bin/rails:9:in `<top (required)>'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:287:in `load'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:287:in `block in load'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:259:in `load_dependency'
from /var/lib/gems/2.3.0/gems/activesupport-5.0.3/lib/active_support/dependencies.rb:287:in `load'
from /usr/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:55:in `require'
from /usr/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:55:in `require'
from -e:1:in `<main>'

The size of records is around 100,000. What could be the best way to insert these number of records in the Rails Table?

Am I doing something wrong?

The best way to bulk insert in Postgresql is to use its `COPY` command - from csv file or even through connection: http://www.kadrmasconcepts.com/blog/2013/12/15/copy-millions-of-rows-to-postgresql-with-rails/ — Pavel Mikhailyuk, Jun 29 '17 at 14:48
I have to fetch data from another database and then insert into Rails Table. What is the best way to achieve this? — nishant, Jun 30 '17 at 07:42
You had to start your question with that info. https://stackoverflow.com/questions/36476192/postgresql-copy-transfer-data-from-one-database-to-another — Pavel Mikhailyuk, Jun 30 '17 at 08:14
I have edited my question to add that info. However my main motive is to speed up insertions in the Postgres table. — nishant, Jun 30 '17 at 09:09
Ok, you can google tools for migrating data from MySQL to PostgreSQL. As for just speed up bulk insertions - COPY is big winner at all. — Pavel Mikhailyuk, Jun 30 '17 at 09:21

score 0 · Answer 1 · answered Jul 14 '17 at 11:39

0

I guess you can make use of ActiveRecord import library Please read this

also this bulk_insert

answered Jul 14 '17 at 11:39

Divya Sharma

536
5
15

Used bulk_insert, but what I need is somewhat find_or_create_by method. Can this be done through bulk_insert? Currently I am running bulk_insert and then deleting duplicate records. This for sure is not the right way to go about. – nishant Jul 14 '17 at 14:29

Error while bulk inserting in Rails

1 Answers1