undefined method 'exec_prepared' on Rails 4 postgresql query

Question

Every time, I submit a form supposed to create a Deal and sending a very high nb of Prizes (>200K) to the Prize table using a transaction and raw postgresql, I have first the error 'undefined method exec_prepared' then if I reload the form then I get a new error 'ERROR: prepared statement 'xixie' already exists'.

I used this question wrong number of arguments (1 for 2..3) for Active Record postgresql query (Rails 4/postgresql 9.4) and Prepared Statement on Postgresql in Rails to create the following Postgresql query:

models deals.rb

CONNEXION = ActiveRecord::Base.connection.raw_connection

def create_prizes
      Deal.transaction do  
        self.prize_number.times do |i| 
         st = CONNEXION.prepare('xixie', 'INSERT INTO prizes (deal_id) values ($1)')

          values = [ { value: self.id} ]
          st.exec_prepared('xixie', values )
          st.close()
        end
      end
    end

I have this problem in Local (not production) and I am not using any puma/unicorn. I do use Zeus and Guard.

Is it impossible with Rails4/postgresql prepared_statements to insert multiple rows at a time ?

How can I change the query to make it work ?

Also as Rails gives me ' ERROR: prepared statement 'xixie' already exists', I had to change multiple times the name of the prepared_statements but will they "live" forever? how can I "kill" them after I do all theses iterations trying to find the appropriate query.

EDIT

Updated the code after some proposed answer:

CONNECTION = ActiveRecord::Base.connection.raw_connection

def create_prizes
      Deal.transaction do  
        self.prize_number.times do |i| 
          CONNECTION.prepare('mimiku', 'INSERT INTO deal_prizes (deal_id, created_at, updated_at) values ($1, $2, $3)')
          CONNECTION.exec_prepared('mimiku',  [ { value: self.id}, { value: '2009-01-23 20:21:13' }, { value: '2009-01-23 20:21:13' }  ] )

        end
        # CONNECTION.close()
      end
    end

(added '2009-01-23 20:21:13' as Rails required created_at and updated_at for some reason).

I get this error:

ERROR: prepared statement "mimiku" already exists

Even if I change the name from 'mimiku' to 'somethingelse', I still get this type of error.

smallbutton · Accepted Answer · 2015-09-12 14:09:27Z

1

+50

The prepare method returns a result according to the docs: http://deveiate.org/code/pg/PG/Connection.html#method-i-prepare

Maybe try call exec_prepared on the connection object

connection = ActiveRecord::Base.connection.raw_connection

def create_prizes

      begin 
        connection.describe_prepared('xixie')
      rescue PG::InvalidSqlStatementName
        connection.prepare('xixie', 'INSERT INTO prizes (deal_id) values ($1)')
      end

      Deal.transaction do  
        self.prize_number.times do |i| 
          connection.exec_prepared('xixie',  [ { value: self.id} ] )
        end
      end
end

UPDATE: I reworked the code above to first check if a prepared statement exists. If it doesn't exist it creates it. Sorry I haven't realized it in the first place but you don't need to prepare a statement more than once. This is the actual benefit of such a statement, since it has to be only parsed once and can than be executed with different values, which is then much faster than a regular query. As prepared statements last for the duration of the AR connection you only need to prepare it once.

edited Sep 12, 2015 at 14:09

answered Sep 10, 2015 at 10:13

smallbutton

3,46718 silver badges28 bronze badges

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

4 Comments

Mathieu Over a year ago

will try tonight but I was already using exec_prepared, was I not?

smallbutton Over a year ago

yes but on the result object not on the connection. This eexplains the undefined method error.

smallbutton Over a year ago

Sorry for that. Only briefly looked into your answer and didn't realize how prepared statments were used. Updated the answer

Mathieu Over a year ago

have now a related question in order to delete fast those 300K created associated objects stackoverflow.com/questions/32553064/…

Collectives™ on Stack Overflow

undefined method 'exec_prepared' on Rails 4 postgresql query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related