Overflow issue in postgres

[fluffyx]

I'm now getting this error when trying to rate any object of a specific class. No code changes occurred; seems something is messed up in the database.

Is there a way to rebuild the cache or something?

ActiveRecord::RangeError (PG::NumericValueOutOfRange)
ERROR: numeric field overflow
DETAIL: A field with precision 17, scale 14 must round to an absolute value less than 10^3.

D, [2020-02-19T18:40:05.800251 #4] DEBUG -- :    (1.2ms)  BEGIN
D, [2020-02-19T18:40:05.810753 #4] DEBUG -- :   Rating::Rate Exists? (1.5ms)  SELECT 1 AS one FROM "rating_rates" WHERE "rating_rates"."author_id" = $1 AND "rating_rates"."author_type" = $2 AND "rating_rates"."resource_id" = $3 AND "rating_rates"."resource_type" = $4 AND "rating_rates"."scopeable_id" IS NULL AND "rating_rates"."scopeable_type" IS NULL LIMIT $5  [["author_id", 1], ["author_type", "User"], ["resource_id", 465], ["resource_type", "Response"], ["LIMIT", 1]]
D, [2020-02-19T18:40:05.816499 #4] DEBUG -- :   Rating::Rate Create (1.8ms)  INSERT INTO "rating_rates" ("value", "author_type", "author_id", "resource_type", "resource_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"  [["value", "5.0"], ["author_type", "User"], ["author_id", 1], ["resource_type", "Response"], ["resource_id", 465], ["created_at", "2020-02-19 18:40:05.812490"], ["updated_at", "2020-02-19 18:40:05.812490"]]
D, [2020-02-19T18:40:05.823025 #4] DEBUG -- :   Rating::Rating Load (4.0ms)  SELECT "rating_ratings".* FROM "rating_ratings" WHERE "rating_ratings"."resource_type" = $1 AND "rating_ratings"."resource_id" = $2 AND "rating_ratings"."scopeable_id" IS NULL LIMIT $3  [["resource_type", "Response"], ["resource_id", 465], ["LIMIT", 1]]
D, [2020-02-19T18:40:05.830376 #4] DEBUG -- :   Rating::Rate Load (3.0ms)  SELECT (CAST(( SELECT GREATEST(COUNT(1), 1) FROM rating_rates WHERE resource_type = 'Response' AND scopeable_type is NULL ) AS DECIMAL(17, 14)) / ( SELECT GREATEST(COUNT(DISTINCT resource_id), 1) FROM rating_rates WHERE resource_type = 'Response' AND scopeable_type is NULL )) count_avg, COALESCE(AVG(value), 0) rating_avg FROM rating_rates WHERE resource_type = 'Response' AND scopeable_type is NULL
D, [2020-02-19T18:40:05.832288 #4] DEBUG -- :    (1.2ms)  ROLLBACK

[wbotelhos]

Hi @fluffyx looks like your database field has a precision less (17/14) than the necessary (numeric field overflow). You should use a precision like the migration:

t.decimal :value, default: 0, precision: 25, scale: 16

[jeferbc]

I'm getting this error with a fresh instalation
ActiveRecord::ValueTooLong Exception: PG::StringDataRightTruncation: ERROR: value too long for type character varying(10)
Maybe related, how can i fix it?

[fluffyx]

I could have sworn the field was set up correctly, but maybe not.

After further troubleshooting, I found it occurred when more than 999 entries were in the table. I switched to ratyrate before @wbotelhos responded and that gem met my needs with some small customization.

Curious, does the migration trick work for you?

Good luck!

[jeferbc]

Yes, I don't have your same error. About my error, it's just increase the string fields limits in the migrations, from 10 to 20. Greetings

[wbotelhos]

Hi @jeferbc ,

varying(10) is too short to keep the precision needed.

@fluffyx I believe your problem is the same, just increase the precision. I use Postgres too and it is ok.

I don't know this gem, but I can see that is about build the Raty presentation. This gem is about make the hard and fair "average" calculation. Just check this goals.

[fongfan999]

Hi there, I got the same issue exactly although the schema was (25, 16) already. Any suggestions?

ActiveRecord::RangeError
PG::NumericValueOutOfRange: ERROR:  numeric field overflow
DETAIL:  A field with precision 17, scale 14 must round to an absolute value less than 10^3.
PG::NumericValueOutOfRange
ERROR:  numeric field overflow
DETAIL:  A field with precision 17, scale 14 must round to an absolute value less than 10^3.

# db/schema.rb

create_table "rating_rates", id: :serial, force: :cascade do |t|
  t.decimal "value", precision: 25, scale: 16, default: "0.0"
  t.string "author_type", limit: 10, null: false
  t.integer "author_id", null: false
  t.string "resource_type", limit: 50, null: false
  t.integer "resource_id", null: false
  t.string "scopeable_type", limit: 50
  t.integer "scopeable_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.text "comment"
  t.index ["author_type", "author_id", "resource_type", "resource_id", "scopeable_type", "scopeable_id"], name: "index_rating_rates_on_author_and_resource_and_scopeable", unique: true
  t.index ["author_type", "author_id"], name: "index_rating_rates_on_author_type_and_author_id"
  t.index ["resource_type", "resource_id"], name: "index_rating_rates_on_resource_type_and_resource_id"
  t.index ["scopeable_type", "scopeable_id"], name: "index_rating_rates_on_scopeable_type_and_scopeable_id"
end

create_table "rating_ratings", id: :serial, force: :cascade do |t|
  t.decimal "average", precision: 25, scale: 16, default: "0.0"
  t.decimal "estimate", precision: 25, scale: 16, default: "0.0"
  t.integer "sum", default: 0
  t.integer "total", default: 0
  t.string "resource_type", limit: 50, null: false
  t.integer "resource_id", null: false
  t.string "scopeable_type", limit: 50
  t.integer "scopeable_id"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["resource_type", "resource_id", "scopeable_type", "scopeable_id"], name: "index_rating_rating_on_resource_and_scopeable", unique: true
  t.index ["resource_type", "resource_id"], name: "index_rating_ratings_on_resource_type_and_resource_id"
  t.index ["scopeable_type", "scopeable_id"], name: "index_rating_ratings_on_scopeable_type_and_scopeable_id"
end

[wbotelhos]

Hi, @fongfan999 .

Do you have the number for us try to emulate the error? I'm trying to imagine the problem and maybe it could be the SQL returning a very large precision number that does not fit well in the DB constraint.

[fongfan999]

Hi @wbotelhos

I'm sorry, I'm not sure what number you expected?

Rating::Rating.averager_data(User.first, nil).rating_avg.to_s
=> "4.9469469469469469"

[wbotelhos]

@fongfan999 I checked the code and maybe I found a possible bug, but not using the number you provided.

Your number will be fit like this:

The value: 4.9469469469469469
Total number: 17
Precision: 1 (on the left side of the dot (.))
Scale: 16 (on the right side of the dot (.))

Rating uses a migration like this:

t.decimal :value, default: 0, precision: 25, scale: 16

So you can fit a number like this:

The value: 123456789.1234567812345678 (9+16)
Total number: 25 (9+16)
Precision: 9 (on the left side of the dot (.))
Scale: 16 (on the right side of the dot (.))

In a place where you can have (9+16) you should be allowed to fit yours (1+16)

How to debug it?

The place the code generates the result is here via SQL where we have:

(CAST(#{total_count} AS DECIMAL(17, 14)) / #{distinct_count}) count_avg

Using your app value, like I asked for an example we have:

SELECT (CAST(4.9469469469469469 AS DECIMAL(17, 14)) / 2) count_avg

-- 2.4734734734734750

The result 2.4734734734734750 is (1.16) and should work.

The possible bug

Maybe the problem is around the distinct_count division because when we divide by a number like 3 the scale can grow:

SELECT (CAST(4.9469469469469469 AS DECIMAL(17, 14)) / 3) count_avg

-- 1.6489823156489833

See that the division is outside the CAST so we're not forcing the "precision.scale". Now let's force it:

SELECT CAST(4.9469469469469469 / 3 AS DECIMAL(17, 14)) count_avg

-- 1.64898231564898

We have a difference here of 2 numbers on the scale:

1.6489823156489833
1.64898231564898

It still should not be a problem since default cast is DECIMAL(17, 14) being less than what migration expect precision: 25, scale: 16. The two extra numbers here would become scale equal to 16 that is covered by migration.

But it's almost impossible predict all possible rating values and so the result of the division that can increase more than only two numbers. So I think the distinct_count should be included on the CAST to make sure the precision and scale and we should increase the precision like the actual migration not the old 17.14 value.

[wbotelhos]

Just rephrasing, after a conversation with Peter we changed the final rounding to 2 decimals so it should not a problem anymore. Since the round was done on the Ruby side, not the SQL side we still can make the correction in the SQL and leave the round as it is. A future feature could be an adapter to change the final round based in a configuration so people can decide keeps the 25.16 or round it just change the config and the migration.