rspec - PG::UnableToSend:server closed the connection unexpectedly This probably means the server terminated abnormally

Question

On my ruby on Rails app, when creating a model Deal, I use an after_create to create prizes on the DealPrize table.

Deal and DealPrize have a belong to/has_many relations: a Deal has many Deal prizes and a Dealprize belongs to a Deal.

It works like this: on my admin panel (using activeadmin), inside a Deal, I have a column 'prize-number' and I use an after_create so that every time the admin creates a new deal, the app takes this prize_number column, and create this volume of prizes (inserting as many rows as necessary) inside the DealPrize table.

My tests using rspec and FactoryGirl are failing. It might be due to the fact rspec/factory girl do not play well with prepared_statements. I am not sure.

Here is my code

models/deal.rb

has_many :deal_prizes, dependent: :delete_all

after_create :create_dealprizes

# Constants
TIME_SET = Time.zone.now
CONNECTION = ActiveRecord::Base.connection.raw_connection

def create_dealprizes
  begin 
    CONNECTION.describe_prepared('create_deal_prizes')
  rescue PG::InvalidSqlStatementName
    CONNECTION.prepare('create_deal_prizes', 'INSERT INTO deal_prizes (deal_id,created_at,updated_at,admin_user_id,prize_id) values ($1, $2, $3, $4, $5)') 
  end

  Deal.transaction do  
    self.prizes_number.times do |i| 
      CONNECTION.exec_prepared('create_deal_prizes',  [
        { value: self.id},
        { value: TIME_SET },
        { value: TIME_SET },
        { value: self.admin_user_id },
        { value: 5 }
      ])
    end
  end
end

Here is my test: I would like to be sure when a deal is created that if the deal's prizes_number is 340 then 340 rows are added on the table Dealprizes as it should be.

require 'spec_helper'

describe DealPrize do
  describe "the right number of rows are created inside DealPrize table when a Deal is created" do

    before do 
      @initial_prize_count = DealPrize.count
      @deal_prize = FactoryGirl.create(:deal_prize) 
      @deal       = FactoryGirl.create(:deal_prizes => [@deal_prize], :prizes_number => 277)
    end

    it "does create right nb of rows" do
      expect(DealPrize.count).to eq( @initial_prize_count + 277 )
    end
  end
end

I use a factory for Deals:

FactoryGirl.define do
  factory :deal do
    country       "France"
    title         "Neque porro quisquam est qui dolorem"
    description   "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum"

    factory :deal_skips_validate do
      to_create {|instance| instance.save(validate: false) }
    end    

  end
end  

and here is the Factory for DealPrizes:

FactoryGirl.define do
  factory :deal_prize do
  end
end

Here is the error I am getting:

PG::UnableToSend:
       server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

If needed, here is how I deal with transaction in my spec_helper.rb

config.use_transactional_fixtures = false

config.before(:suite) do
  DatabaseCleaner.clean_with(:truncation, :except => %w(roles)) 
end

config.before(:each) do
  DatabaseCleaner.strategy = :transaction
end

config.before(:each, js: true) do
  DatabaseCleaner.strategy = :truncation
end

config.before(:each) do
  DatabaseCleaner.start
end

config.after(:each) do
  DatabaseCleaner.clean
end

Answer 1

You'll need to grab a new connection from the pool each time your method runs (and make sure you return it to the pool when you're done). Take a look at with_connection.

Something like this should work.

class Deal < ActiveRecord::Base
  has_many :deal_prizes, dependent: :delete_all

  after_create :create_dealprizes

  # Constants
  TIME_SET = Time.zone.now

  def create_dealprizes
    self.class.connection_pool.with_connection do |connection|
      begin 
        connection.describe_prepared('create_deal_prizes')
      rescue PG::InvalidSqlStatementName
        connection.prepare('create_deal_prizes', 'INSERT INTO deal_prizes (deal_id,created_at,updated_at,admin_user_id,prize_id) values ($1, $2, $3, $4, $5)') 
      end

      connection.execute("START TRANSACTION")
      prizes_number.times do |i| 
        connection.exec_prepared('create_deal_prizes',  [
          { value: self.id},
          { value: TIME_SET },
          { value: TIME_SET },
          { value: self.admin_user_id },
          { value: 5 }
        ])
      end
      connection.execute("COMMIT")
    end
  end
end

This should work, but in my opinion (for what it's worth) it's not very Ruby-ish. It sounds like you're concerned with performance, so I would add the data with a single INSERT statement. You could build that manually, but the activerecord-import gem makes it very easy.

Using that gem might look something like:

class Deal < ActiveRecord::Base
  has_many :deal_prizes, dependent: :delete_all

  after_create :create_dealprizes

  def create_dealprizes
    prizes = prizes_number.times.map do
               deal_prizes.build(created_by: admin_user, prize_id: 5)
             end
    DealPrize.import(prizes)
  end
end

Here's a sample app that uses ActiveRecord import.

Answer 2

You only retrieve CONNECTION from ActiveRecord::Base.connection.raw_connection once, as the class is being loaded. So the framework is free to close that connection and create a new one. You should retrieve it at the time you actually need it, then I imagine it might work.

Update: afaik the PostgreSQL adapter will create prepared statements, too and the database should be able to re-use them, so the performance gain from doing these things manually shouldn't be that big (especially since you're "only" talking about a few hundred objects). You lose compatibility to other adapters as well as maintainability due to non-idiomatic Rails code when you could just create DealPrize objects instead.

Answer 3

Can I suggest the following?

def create_deal_prizes
  batch_size = 1000
  deal_prizes = []
  time = Time.zone.now
  prize_id = 5 # wasn't sure where you got this id from

  Deal.transaction do
    deal_prizes = []
    prizes_number.times do
      deal_prizes << "(#{self.id}, #{self.admin_user_id}, #{prize_id}, '#{time}', '#{time}')"

      if deal_prizes.size >= batch_size
        sql = "INSERT INTO deal_prizes (deal_id, admin_user_id, prize_id, created_at, updated_at) VALUES #{deal_prizes.join(", ")}"
        ActiveRecord::Base.connection.execute sql
        deal_prizes = []
      end
    end
    if deal_prizes.any?
      sql = "INSERT INTO deal_prizes (deal_id, admin_user_id, prize_id, created_at, updated_at) VALUES #{deal_prizes.join(", ")}"
      ActiveRecord::Base.connection.execute sql
    end
  end
end

This is more rails-esque, efficient, and won't have to be re-written when you switch to [INSERT DB HERE]. Feel free to play with the batch_size as well.

Another note, almost positive if you set the time to a constant it will not change, was that your intention? Instead I moved it to the method, so it will lock to the time it was invoked and not the time the class was initialized (boot).