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: kadrmasconcepts.com/blog/2013/12/15/… — Pavel Mikhailyuk
– Pavel Mikhailyuk, Commented Jun 29, 2017 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
– nishant, Commented Jun 30, 2017 at 7:42
You had to start your question with that info. stackoverflow.com/questions/36476192/… — Pavel Mikhailyuk
– Pavel Mikhailyuk, Commented Jun 30, 2017 at 8:14
I have edited my question to add that info. However my main motive is to speed up insertions in the Postgres table. — nishant
– nishant, Commented Jun 30, 2017 at 9: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
– Pavel Mikhailyuk, Commented Jun 30, 2017 at 9:21

Divya Sharma · Accepted Answer · 2017-07-14 11:39:11Z

0

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

also this bulk_insert

answered Jul 14, 2017 at 11:39

Divya Sharma

5565 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nishant Over a year ago

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.

Collectives™ on Stack Overflow

Error while bulk inserting in Rails

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related