Rails seed sql file that contains "COPY" statments

Question

I am trying to seed sql file that looks like that:

COPY courses (id, name, "courseID", school_id, created_at, updated_at) FROM stdin;
1 Fundamentals of Programming CSCI150 1 2016-04-27 14:04:07.460825  2016-04-27 14:04:07.460825

I try this code:

connection = ActiveRecord::Base.connection
# migrate all courses
sql = File.read('db/nurate.sql')
statements = sql.split(/;$/)
#statements.pop
ActiveRecord::Base.transaction do
  statements.each do |statement|
    connection.execute(statement)
  end
end

Previously I used this code for INSERT etc statements. But here I have error as follows:

PG::UnableToSend: another command is already in progress

So Believe this has something to do with the fact that my sql dump file contains "COPY" statements. What can I do with this? I don't want to change the whole file to use INSERT statements. Or is it the only solution?

Answer 1

I've got same issue and it costed me couple of days.

Firstly I guess that you've used the sql from dump export feature of postgres. So because of that when ever you call

connection.execute(statement)

It will cause error because it's never been a legit call for query in postgresql with provide input data. I've done some check over internet. It seem this thread provide some helpful information.

Okei so in order to keep using buck copy combine with the sql file export by pg_dump, I have come up with quite dirty code (IMO)

unless Rails.env.production?
  connection = ActiveRecord::Base.connection
  connection.tables.each do |table|
    connection.execute("TRUNCATE #{table}") unless table == "schema_migrations"
  end

  # Create a sql file with purely COPY data and ignoring schema_migrations data
  cmd = %x[awk -v RS='' '/COPY/{if (!match($0,/schema/)) print $0}' #{Rails.root}/db/dump.sql> #{Rails.root}/db/temp.sql]
  puts cmd

  # Restoring sql data to postgres data. Putting password to env variable is never been smart
  cmd = %x[PGPASSWORD=#{ActiveRecord::Base.connection_config[:password]} psql --host #{ActiveRecord::Base.connection_config[:host]} -U #{ActiveRecord::Base.connection_config[:username]} --dbname #{ActiveRecord::Base.connection_config[:database]} -1 -f #{Rails.root}/db/temp.sql]
  puts cmd

  # remove the deed
  cmd = %x[rm #{Rails.root}/db/temp.sql]
  puts cmd

end