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.

Answer 1

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.