ActiveRecord::RangeError: PG::NumericValueOutOfRange: ERROR: value overflows numeric format
marcgreenstock opened this issue · 4 comments
I'm having a problem with any string that appears to be in scientific notation. For instance '557236406134e62000323100'.
The specific error is:
ActiveRecord::RangeError: PG::NumericValueOutOfRange: ERROR: value overflows numeric format
CONTEXT: PL/pgSQL function logidze_logger() line 68 at assignment
Postgres version: 9.6
Edit: I have my suspicions that it is because of the to_jsonb method.
Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.
I believe to_jsonb is erroneously attempting to typecast the string, could this be a bug in PG?
I believe to_jsonb is erroneously attempting to typecast the string, could this be a bug in PG?
to_jsonb works fine http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=b1f6f9128c3db270785c15f7426583e1 (::jsonb fails but we do not use).
The problem is with hstore_to_jsonb_loose:
# select hstore_to_jsonb_loose('a=>557236406134e62000323100'::hstore);
ERROR: value overflows numeric format
One way to solve this is to avoid hstore manipulations and calculate the diff using another technique (for example, this one https://github.com/palkan/logidze/blob/master/bench/triggers/keys2_trigger_setup.sql). That would be less performant though (about 1.5x).
Hi @palkan, thanks for looking into this so quickly.
This gem is brilliant btw, thank you so much for your effort.
I see that the docs for hstore_to_jsonb_loose say:
get hstore as a jsonb value, but attempt to distinguish numerical and Boolean values so they are unquoted in the JSON
This is exactly the problem.
It looks as though this should have been fixed in 9.6 commited postgres/postgres@e09996f#diff-0f8b296e470f9ba30622ac12b4cea554 and again amended postgres/postgres@41d2c08#diff-0f8b296e470f9ba30622ac12b4cea554
Make contrib/hstore's hstore_to_jsonb_loose() and hstore_to_json_loose() functions agree on what is a number (Tom Lane)
Previously, hstore_to_jsonb_loose() would convert numeric-looking strings to JSON numbers, rather than strings, even if they did not exactly match the JSON syntax specification for numbers. This was inconsistent with hstore_to_json_loose(), so tighten the test to match the JSON syntax.
And yet the the problem still exists. I'm going to try and look into the bug on hstore_to_jsonb_loose a little more. For now I am working around it by base64 encoding the strings before they are inserted.
I think maybe this should be in the README as a possible error raised by logidze_logger().
We've recently gotten this a few times in
UPDATE \"tender_job_schedule_shifts\" SET \"scheduled_check_in_request_jid\" = '3981465518e9665560300635' WHERE \"tender_job_schedule_shifts\".\"id\" = 581565",
relatedly, while I'm here: I'd also appreciate if there were an error_handling function which I could define to 'rescue' any errors inside logidze without failing the transaction. I do this for a number of my other function.
e.g.
EXCEPTION
-- https://www.postgresql.org/docs/11/errcodes-appendix.html
-- https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
WHEN DEADLOCK_DETECTED OR QUERY_CANCELED OR LOCK_NOT_AVAILABLE THEN
RAISE WARNING 'APP_NOTICE: DATABASE ERROR DETECTED: function % on invoice_ids %. running: %\n', fn_name, invoice_ids, current_query();
GET STACKED DIAGNOSTICS
_db_err_sql_state := RETURNED_SQLSTATE,
_db_err_message := MESSAGE_TEXT,
_db_err_detail := PG_EXCEPTION_DETAIL,
_db_err_hint := PG_EXCEPTION_HINT,
_db_err_context := PG_EXCEPTION_CONTEXT;
IF COALESCE(current_setting('logidze.meta', true), '') <> '' THEN
_db_err_responsible_id := current_setting('logidze.meta')::text;
END IF;
INSERT INTO database_errors (
sql_state, message, detail, hint, context,
fn_name, fn_args, current_query, responsible_id
) VALUES (
_db_err_sql_state, _db_err_message, _db_err_detail, _db_err_hint, _db_err_context,
fn_name, quote_literal(invoice_ids::text), current_query()::text, _db_err_responsible_id
);