“PG ::错误 - 数字字段溢出”在Heroku上 [英] "PG::Error - numeric field overflow" on Heroku

问题描述

我已经构建了一个应用程序，用于查询Google Analytics过去7天的数据。一切工作在当地。在Heroku上，这个过程运行得很顺利，直到它试图获取当天的数据。然后我得到以下错误：

  2012-10-29T02：32：02 + 00：00 app [web.1] ：ActiveRecord :: StatementInvalid（PG ::错误：错误：数值字段溢出
 2012-10-29T02：32：02 + 00：00应用程序[web.1]：详细信息：字段的精度为8，标度为2必须舍入到小于10 ^ 6的绝对值
  

我试图找出它是哪个变量不喜欢，但我现在不知道，我假设它与日期或时间有关。

任何想法或想法都会很棒：）

>

- update ---

  ActiveRecord :: Schema.define（：版本=> 20121014153338）do 
 create_tableanalytics，：force => true do | t | 
 t.stringsite
 t.integervisits
 t.datestart_date
 t.dateend_date
 t.decimalrevenue_per_transaction ，：precision => 8，：scale => 2 
 t.integertransactions
 t.decimalitem_quantity，：precision => 8，：scale => 2 
 t.integergoal_starts
 t.integergoal_completes
 t.decimalgoal_conversion，：precision => 8，：scale => 2 
 t.datetimecreated_at，：null => false 
 t.datetimeupdated_at，：null => false 
 t.decimalgoal_abandon，：precision => 8，：scale => 2 
 t.decimal收入，：精度=> 8，：scale => 2 
 t.stringsource
 end 
 
 end 
  

解决方案

您有一个数字< $ c>，并且你试图存储一个大于 999999.99 的值。请参阅 PostgreSQL手册> NUMERIC ，以获取有关数字刻度和精度的信息，这些是在括号内显示的限定符。

这个先前的问题似乎涵盖了Rails的相同问题，显示了Rails模型以及如何分配比例和精度。

NUMERIC 不是日期/时间字段，而是数字字段。

问题的演示：

  regress => SELECT NUMERIC（8,2）'999999.99'; 
数字
 ----------- 
 999999.99 
（1行）
 
 regress => SELECT NUMERIC（8,2）'1000000.00'; 
错误：数值字段溢出
详细信息：精度为8，比例为2的字段必须舍入到小于10 ^ 6的绝对值。 
  

遗憾的是，Pg没有告诉你这是什么字段。但是，这样做很困难，因为它通常不知道在解析字符串文本时哪个值将进入哪个字段。在 postgresql.conf中启用 log_statement ='all' ， ALTER USER ... SET ， ALTER DATABASE ... SET 或每个会话与 SET log_statement ='all'然后重新测试并检查查询日志。





另请参阅 \dt 中的表定义 psql 查看可能有什么类型 numeric（8,2）并且可能导致问题。

至于它为什么在本地工作：本地数据库PostgreSQL？一些Rails用户在本地使用SQLite和Heroku上的PostgreSQL时似乎有一个非常奇怪的设置。这是混乱和部署问题的秘诀。在开发和测试中使用相同的数据库。如果它是在本地PostgreSQL，它是相同的版本吗？

I have built an app that queries Google Analytics for the last 7 days of data. Everything works locally. On Heroku, the process runs smoothly until it tries to get data for today's date. I then get the following error:

2012-10-29T02:32:02+00:00 app[web.1]: ActiveRecord::StatementInvalid (PG::Error: ERROR:   numeric field overflow
2012-10-29T02:32:02+00:00 app[web.1]: DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

I have tried to figure out which variable it's not happy with but I don't know right now. I am assuming it's something related to date or time.

Any thoughts or ideas would be great :)

-- update ---

ActiveRecord::Schema.define(:version => 20121014153338) do
  create_table "analytics", :force => true do |t|
    t.string   "site"
    t.integer  "visits"
    t.date     "start_date"
    t.date     "end_date"
    t.decimal  "revenue_per_transaction", :precision => 8, :scale => 2
    t.integer  "transactions"
    t.decimal  "item_quantity",           :precision => 8, :scale => 2
    t.integer  "goal_starts"
    t.integer  "goal_completes"
    t.decimal  "goal_conversion",         :precision => 8, :scale => 2
    t.datetime "created_at",                                            :null => false
    t.datetime "updated_at",                                            :null => false
    t.decimal  "goal_abandon",            :precision => 8, :scale => 2
    t.decimal  "revenue",                 :precision => 8, :scale => 2
    t.string   "source"
  end

end

解决方案

You have a numeric field with typmod numeric(8,2) and you're trying to store a value greater than 999999.99 in it. See the PostgreSQL manual on NUMERIC for information on numeric scale and precision, which are the qualifiers shown after the type in parentheses.

This earlier question appears to cover the same issue with Rails, showing the Rails model and how the scale and precision are assigned.

NUMERIC isn't a date/time field, it's a number field.

Demo of the issue:

regress=> SELECT  NUMERIC(8,2) '999999.99';
  numeric  
-----------
 999999.99
(1 row)

regress=> SELECT  NUMERIC(8,2) '1000000.00';
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

It's a pity that Pg doesn't tell you what field this is when it is a field. It's difficult for it to do so, though, because it doesn't usually know which value is going to go into which field when it's parsing string literals. Enable log_statement = 'all' in postgresql.conf, ALTER USER ... SET, ALTER DATABASE ... SET, or per-session with SET log_statement = 'all' then re-test and examine the query logs.

Also look at the table definitions with \dt in psql to see what might have the type numeric(8,2) and could be causing the problem.

As for why it works locally: Is the local DB PostgreSQL? Some Rails users seem to have a very odd setup where they use SQLite locally, and PostgreSQL on Heroku. This is a recipe for chaos and deployment problems. Use the same database in development and testing. If it is PostgreSQL locally, is it the same version?

这篇关于“PG ::错误 - 数字字段溢出”在Heroku上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！