Problems with postgresql COPY command with Rails on different server

Question

I have a rails app that has been working successfully for months. In a few places I call directly to the database via ActiveRecord::Base.connection.execute( sql_code )

With a recent need to scale, I just added a second server for data processing. I want to run the same app but connect over the network to the other database server. That is the only difference here. All other areas of the app work--it can connect to the remote database.

Where it is breaking, is where I have rails issue a psql COPY command to import a csv file.

   result = ActiveRecord::Base.connection.execute( @PGSQL_COPY_COMMAND )     # perform the copy command

This fails and says that the csv file can not be found. I have verified it is there and is readable to both the user running the rails app and the postgres user.

Am I missing something?

I suppose it is because the COPY command gets issued against the remote server who then looks for said csv file in its directory structure and doesn't find it. Bummer. I need to find a different way. — NJ.
– NJ., Commented Jul 21, 2011 at 17:32

Philip Hallstrom · Accepted Answer · 2013-09-05 20:52:56Z

23

You can use COPY FROM STDIN to get around this... like so:

conn = ActiveRecord::Base.connection_pool.checkout
raw  = conn.raw_connection
raw.exec("COPY tablename (col1, col2, col3) FROM STDIN")
# open up your CSV file looping through line by line and getting the line into a format suitable for pg's COPY...
raw.put_copy_data line
# once all done...
raw.put_copy_end
while res = raw.get_result do; end # very important to do this after a copy
ActiveRecord::Base.connection_pool.checkin(conn)

I believe there are some options to COPY that will let you specify you're passing in CSV data which would make it even easier...

edited Sep 5, 2013 at 20:52

answered Jul 21, 2011 at 18:29

Philip Hallstrom

19.9k2 gold badges44 silver badges47 bronze badges

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

1 Comment

Tom Lubitz Over a year ago

It would be cool to mention how to consume and handle errors here, using e.g. res.result_status and res.error_message

hybernaut · Accepted Answer · 2013-12-18 19:17:40Z

18

In pg-0.17.1 (Rails 4) there's an improved PG::Connection::copy_data interface to Postgres COPY.

  def load_file(filename)

    dbconn = ActiveRecord::Base.connection_pool.checkout
    raw  = dbconn.raw_connection
    count = nil

    result = raw.copy_data "COPY my_table FROM STDIN" do

      File.open(filename, 'r').each do |line|
        raw.put_copy_data line
      end

    end

    count = dbconn.select_value("select count(*) from #{ttable}").to_i

    ActiveRecord::Base.connection_pool.checkin(dbconn)

    count
  end

You can even pass an entire file buffer to put_copy_data if you aren't worried about memory usage:

      result = raw.copy_data "COPY my_table FROM STDIN" do
        raw.put_copy_data File.read(filename)
      end

answered Dec 18, 2013 at 19:17

hybernaut

6364 silver badges7 bronze badges

4 Comments

novemberkilo Over a year ago

If you use this and see it working in development but not in test, this is likely because tests run in a transaction and the checked out connection is therefore blind to the transaction. So, try dbconn = ActiveRecord::Base.connectioninstead

hybernaut Over a year ago

Thanks, I did not fully grok the connection_pool.checkout and you're right--it was causing me trouble in transactional tests.

Jared Beck Over a year ago

The pg gem docs recommend this solution over put_copy_data alone, saying: "This method [copy_data] ensures, that the copy process is properly terminated in case of client side or server side failures. Therefore, in case of blocking mode of operation, #copy_data is preferred to raw calls of #put_copy_data, #get_copy_data and #put_copy_end." (github.com/ged/ruby-pg/blob/…)

cschroed Over a year ago

Another reason test failures can occur is that copy_data doesn't tell the Rails QueryCache that an update has been made. If you reuse queries to validate the results, you may need to call clear_query_cache on the connection after using copy_data.

user589473 · Accepted Answer · 2013-03-12 14:41:45Z

1

You can also try this, and execute the command with psql:

config = YourApp::Application.config.database_configuration[::Rails.env]
dbhost, dbuser, dbname = config['host'], config['username'], config['database']

copy_command = "\\copy theTable (col1, col2, col3) from '/a/path/to/csv' csv header;"
sql_command = "psql -U #{dbuser} -h #{dbhost} #{dbname} -c \"#{copy_command}\""

`#{sql_command}`

answered Mar 12, 2013 at 14:41

user589473

973 bronze badges

Collectives™ on Stack Overflow

Problems with postgresql COPY command with Rails on different server

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related