How can I run two database commands at once with Rails ActiveRecord?

Question

I'm trying to do a direct load to a PostgreSQL table in Rails. My connection is established like this:

  rc = connection.raw_connection
  rc.exec("COPY research FROM STDIN WITH CSV HEADER")

Here is a portion of the code:

    for key,values in valuehash
      entry = standard.model_constant::ATTRIBUTE_HASH[key.to_sym]
      puts "Loading #{entry[:display]}..."
      values.each do |value|
        id = ActiveRecord::Base.connection.execute("select nextval('research_id_seq'::regclass)").first['nextval']

        line = [id, value, entry[:display], standard.id, now, now]
        until rc.put_copy_data( line.to_csv )
          ErrorPrinter.print "  waiting for connection to be writable..."
          sleep 0.1
        end
      end
    end

The line that begins id = fails. It says:

ActiveRecord::StatementInvalid: PG::UnableToSend: another command is already in progress

I don't want to write all these values to a CSV file, then read in that file. I just want to send the data from memory right to the database. I can't think of a valid reason why I couldn't run two connections to the same database against the same table, so I figured I must be doing something wrong. I've tried two raw connections, but it always keeps giving me the same connection from the pool.

It seems odd for you to have to ask the sequence for the ID yourself. If you insert a record and do not include the id field, the sequence should fill it automatically. — Don Pflaster
– Don Pflaster, Commented Oct 11, 2014 at 21:40
For the id line why aren't you using the rc connection to get the id? Alternatively, why don't you create a model for the table so that you can use standard ActiveRecord to insert into the database? If you are worried about speed you can use github.com/zdennis/activerecord-import — johnsorrentino
– johnsorrentino, Commented Oct 11, 2014 at 23:46
Don - On a direct insert you have to supply the sequence, there is no autoincrement. John, I tried that first. It terminates the COPY command when you do that. I'll check out activerecord-import though, thanks. — RubyRedGrapefruit
– RubyRedGrapefruit, Commented Oct 12, 2014 at 1:14

harmic · Accepted Answer · 2014-10-12 03:10:26Z

The best way to handle this is to have the database insert the sequence numbers for you, rather than you trying to fetch and add them.

Assuming that your table is defined something like:

CREATE TABLE research (
    id SERIAL,
    value TEXT, 
    ...
)

then the table is defined such that if you insert a row, and you do not specify a value for id, then it will default to a value from the sequence automatically. This applies equally to COPY as any other form of inserting rows.

The trick is to make sure you are not supplying a value. To do this, structure your COPY command so that you do not include the id column:

COPY research(value,....) FROM STDIN WITH CSV HEADER

ie. you have to specify the list of fields you will supply data for, and the order in which you will supply it.

Note that even if you have a header, it is not used on import to determine the columns present or their order. The header is just discarded.

Assuming you changed the COPY statement, then you would just drop the id from the array of values you are building to send to the database.

Collectives™ on Stack Overflow

How can I run two database commands at once with Rails ActiveRecord?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related