Number exceeds maximum value that can be represented

[schakhboz]

Hi all !
I am trying to retrieve large amount of data from one database and insert it to another. There are 21mln rows (4.2GB).
Since there is no chunksize functionality in connectorx, I am using this kind of query in loop:

select * from process_par_value ppv where id >= 0 and id < 50000 order by id asc
select * from process_par_value ppv where id >= 50000  and id < 100000 order by id asc
select * from process_par_value ppv where id >= 100000  and id < 150000 order by id asc
select * from process_par_value ppv where id >= 150000  and id < 200000 order by id asc

... etc.

For retrieving rows I am using connectorx's read_sql method, for inserting pandas to_sql method. My code:

import connectorx as cx
import pandas
...
        try:
            print("----Start time: ", datetime.datetime.now())
            last_id = cx.read_sql(con, 'select id from {} order by id desc limit 1'.format(table_name))
            iter_id = 0
            while iter_id <= int(last_id.iloc[0][0]):
                k = iter_id+ 50000
                query = "".join(("select * from {} where id >= {} and id <".format(table_name, iter_id), "{} order by id asc".format(k)))
                dfcx = cx.read_sql(con, query)
                iter_id= k
                dfcx.to_sql(con=target_engine, name=table_name, if_exists='append', index=False)
            print("----Finish time: ", datetime.datetime.now())
        except Exception as e:
            print(e)
...

But when the number of rows reaches 4 million, this exception throws:

thread '<unnamed>' panicked at 'Number exceeds maximum value that can be represented.', C:\Users\runneradmin\.cargo\registry\src\github.com-1ecc6299db9ec823\rust_decimal-1.22.0\src\decimal.rs:444:23
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
Traceback (most recent call last):
  File "F:\pyproj\pyray\main.py", line 95, in <module>
    main()
  File "F:\pyproj\pyray\main.py", line 79, in main
    dfcx = cx.read_sql("postgresql://postgres:password@ip:port/dms_db_node3", select_to_query)
  File "F:\pyproj\pyray\venv\lib\site-packages\connectorx\__init__.py", line 224, in read_sql
    result = _read_sql(
pyo3_runtime.PanicException: Number exceeds maximum value that can be represented.

How can I fix this?

[wangxiaoying]

Hi @schakhboz , to me it looks like the error is caused by a decimal value which rust_decimal can not represent (like mentioned here and similar to this issue). Could you please check whether your returned data contains such decimal value and maybe one work around is to use the TRUNC() function in the query for postgres.

As for chunking, we actually use less memory than pandas (like this benchmark fetching 60M rows and 16 columns). So maybe you can try to increase the chunk size (enlarge the filter range) if this was the original chunk size you use with pandas. Of course it will also depends your machine's memory size.