未定义的方法'ppared exec_ $ P $'on Rails的4 PostgreSQL的查询 [英] undefined method 'exec_prepared' on Rails 4 postgresql query

问题描述

每一次，我提出应该创建一个协议，并使用事务和原材料的PostgreSQL发送奖品非常高的NB（> 200K）的奖金表的一种形式，我第一次错误未定义的方法exec_ prepared那么如果我重装的形式，然后我得到一个新的错误错误：ppared $ P $声明xixie'已经存在

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'.

我用这个问题<一href="http://stackoverflow.com/questions/32406552/wrong-number-of-arguments-1-for-2-3-for-active-record-postgresql-query-rails">wrong对于活动记录，PostgreSQL的查询（轨道4 / PostgreSQL的9.4）和<一（1 2..3）数目的参href="http://stackoverflow.com/questions/13805627/$p$ppared-statement-on-postgresql-in-rails">$p$ppared Rails中在PostgreSQL上语句来创建以下PostgreSQL的查询：

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:

型号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

我在本地（未生产）这个问题，我没有使用任何PUMA /麒麟。我用宙斯和保护。

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

时用Rails4 / PostgreSQL的prepared_statements不可能同时插入多行？

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 ?

另外，作为Rails的给了我'错误：prepared声明xixie'已经存在，我不得不改变prepared_statements的多次的名称，但将他们活下去吗？我怎么能杀出来后，我做的所有论文的迭代试图找到适当的查询。

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.

修改

更新了code一些建议答案后：

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

（增加了2009-01-23二十点21分13秒的要求created_at和的updated_at出于某种原因Rails的）。

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

我得到这个错误：

ERROR: prepared statement "mimiku" already exists

即使我从'mimiku'到'somethingelse改名字，我仍然得到这种类型的错误。

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

推荐答案

在 prepare 方法根据文档返回结果： http://deveiate.org/code / PG / PG / Connection.html＃方法-1- prepare

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

也许尝试连接对象上调用 exec_ prepared

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

更新：我修改了code以上，首先检查是否存在prepared声明。如果它不存在，创建它。对不起，我没有意识到它摆在首位，但你并不需要prepare一份声明中不止一次。这是这样一个语句的实际效益，因为它必须仅解析一次，就可以比用不同的值，然后将其比常规查询快得多执行。 由于prepared语句持续的AR连接的时间，你只需要prepare一次。

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.

这篇关于未定义的方法'ppared exec_ $ P $'on Rails的4 PostgreSQL的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！