ActiveRecord::RangeError: PG::NumericValueOutOfRange: ERROR: value overflows numeric format

[marcgreenstock]

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?

[palkan]

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

[marcgreenstock]

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.

[bf4]

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
      );

[palkan]

@bf4 Thanks for the suggestion! Updated the Readme and will create a separate issue regarding exceptions handling.