PG :: ERROR: another command is already running

Question

I have an importer who takes a list of letters and saves them in the postgres database. The following is a code snippet in an importer class without tables:

query_temporary_table = "CREATE TEMPORARY TABLE subscriber_imports (email CHARACTER VARYING(255)) ON COMMIT DROP;" query_copy = "COPY subscriber_imports(email) FROM STDIN WITH CSV;" query_delete = "DELETE FROM subscriber_imports WHERE email IN (SELECT email FROM subscribers WHERE suppressed_at IS NOT NULL OR list_id = #{list.id}) RETURNING email;" query_insert = "INSERT INTO subscribers(email, list_id, created_at, updated_at) SELECT email, #{list.id}, NOW(), NOW() FROM subscriber_imports RETURNING id;" conn = ActiveRecord::Base.connection_pool.checkout conn.transaction do raw = conn.raw_connection raw.exec(query_temporary_table) raw.exec(query_copy) CSV.read(csv.path, headers: true).each do |row| raw.put_copy_data row['email']+"\n" unless row.nil? end raw.put_copy_end while res = raw.get_result do; end # very important to do this after a copy result_delete = raw.exec(query_delete) result_insert = raw.exec(query_insert) ActiveRecord::Base.connection_pool.checkin(conn) { deleted: result_delete.count, inserted: result_insert.count, updated: 0 } end 

The problem I am facing is that when I try to download, I get an exception:

 PG::ERROR: another command is already in progress: ROLLBACK 

This is all done in one step, the only other queries I make are user validation, and I have a DB mutex that prevents import overlapping. This request worked until the end of my last click, including updating my pg gem to 0.14.1 from 0.13.2 (along with other "unrelated" code).

Initially, the error started on our intermediate server, but then I was able to reproduce it locally and was absent.

If I need to be more clear with my question, let me know.

thanks

Answer 1

I found my own answer, and it can be useful if someone finds the same problem when importing data downloads using "COPY"

An exception occurs in the CSV.read () block, and I will catch it, but I did not finish the process correctly.

  begin CSV.read(csv.path, headers: true).each do |row| raw.put_copy_data row['email']+"\n" unless row.nil? end ensure raw.put_copy_end while res = raw.get_result do; end # very important to do this after a copy end 

This block ensures that the COPY command is completed. I also added this at the end to free the connection back to the pool without interrupting the flow in case of successful import:

 rescue ActiveRecord::Base.connection_pool.checkin(conn)