BUG: Operational error while to trying to load data #273

marcelonp · 2022-09-20T20:11:32Z

Hello, I'm trying to load all data on a postgres 13 database, but I run into some Integrity errors and then a operational error, after running the command like this:
python3 manage.py cities_light -v 3 --progress --traceback

I haven't changed any of the default settings. All help is appreciated.

Results:

RAM used: 56 MB Time:  0:00:00 Done: 100%|###################################################################################################################################################################|
RAM used: 56 MB Time:  0:00:01 Done: 100%|###################################################################################################################################################################|
RAM used: 58 MB Time:  0:00:25 Done: 100%|###################################################################################################################################################################|
RAM used: 59 MB ETA:   0:01:10 Done:  13%|#####################                                                                                                                                              |Saving Zhonghe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Zhonghe) already exists.\n')
RAM used: 59 MB ETA:   0:01:10 Done:  13%|######################                                                                                                                                             |Saving Xinglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Xinglong) already exists.\n')
RAM used: 59 MB ETA:   0:01:07 Done:  16%|##########################                                                                                                                                         |Saving Guandu, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Guandu) already exists.\n')
RAM used: 59 MB ETA:   0:01:06 Done:  16%|###########################                                                                                                                                        |Saving Longxing, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Longxing) already exists.\n')
RAM used: 59 MB ETA:   0:01:06 Done:  17%|###########################                                                                                                                                        |Saving Shuanglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Shuanglong) already exists.\n')
RAM used: 59 MB ETA:   0:01:06 Done:  17%|############################                                                                                                                                       |Saving Shima, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Shima) already exists.\n')
RAM used: 59 MB ETA:   0:01:06 Done:  17%|############################                                                                                                                                       |Saving Taiping, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Taiping) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  18%|#############################                                                                                                                                      |Saving E’zhou, Hubei, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_slug_efb2e768_uniq"\nDETAIL:  Key (region_id, subregion_id, slug)=(627, 9884, ezhou) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  18%|##############################                                                                                                                                     |Saving Wushan, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Wushan) already exists.\n')
Saving Zhuxi, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Zhuxi) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  18%|##############################                                                                                                                                     |Saving Tai’an, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Tai’an) already exists.\n')
Saving Xinmin, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Xinmin) already exists.\n')
Saving Yong’an, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Yong’an) already exists.\n')
Saving Gaofeng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Gaofeng) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  18%|##############################                                                                                                                                     |Saving Xinsheng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Xinsheng) already exists.\n')
Saving Sanhe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Sanhe) already exists.\n')
Saving Xintian, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Xintian) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  18%|##############################                                                                                                                                     |Saving Yihe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Yihe) already exists.\n')
Saving Longhe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Longhe) already exists.\n')
Saving Gulu, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Gulu) already exists.\n')
RAM used: 59 MB ETA:   0:01:05 Done:  19%|###############################                                                                                                                                    |Saving Longfeng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Longfeng) already exists.\n')
Saving Longxing, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Longxing) already exists.\n')
RAM used: 59 MB ETA:   0:01:04 Done:  19%|###############################                                                                                                                                    |Saving Shizuishan, Ningxia Hui Autonomous Region, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(623, 9792, Shizuishan) already exists.\n')
RAM used: 59 MB ETA:   0:01:04 Done:  19%|################################                                                                                                                                   |Saving Liangping, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Liangping) already exists.\n')
Saving Xinglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 10019, Xinglong) already exists.\n')
RAM used: 59 MB ETA:   0:01:03 Done:  20%|##################################                                                                                                                                 |Saving Buenaventura, Valle del Cauca, Colombia failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(645, 11044, Buenaventura) already exists.\n')
RAM used: 59 MB ETA:   0:00:50 Done:  35%|##########################################################                                                                                                         |Saving Hayes, England, United Kingdom failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(1028, 13560, Hayes) already exists.\n')
RAM used: 59 MB ETA:   0:00:26 Done:  66%|############################################################################################################                                                       |Saving Panauti, Bagmati Province, Nepal failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_slug_efb2e768_uniq"\nDETAIL:  Key (region_id, subregion_id, slug)=(2406, 28049, panauti) already exists.\n')
RAM used: 59 MB ETA:   0:00:02 Done:  96%|##############################################################################################################################################################     |Saving Vincent, California, United States failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(3628, 42443, Vincent) already exists.\n')
RAM used: 59 MB Time:  0:01:18 Done: 100%|###################################################################################################################################################################|
RAM used: 219 MB Time:  0:02:23 Done: 100%|##################################################################################################################################################################|
RAM used: 219 MB ETA:   0:00:03 Done:  48%|#############################################################################                                                                                     |Traceback (most recent call last):
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ProgramLimitExceeded: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (195,1) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    main()
  File "manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/.venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/.venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/.venv/lib/python3.8/site-packages/django/core/management/base.py", line 414, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/.venv/lib/python3.8/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/.venv/lib/python3.8/site-packages/cities_light/management/commands/cities_light.py", line 242, in handle
    self.translation_import()
  File "/.venv/lib/python3.8/site-packages/cities_light/management/commands/cities_light.py", line 686, in translation_import
    model.save(force_update=True)
  File "/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 806, in save
    self.save_base(
  File "/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 857, in save_base
    updated = self._save_table(
  File "/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 970, in _save_table
    updated = self._do_update(
  File "/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 1034, in _do_update
    return filtered._update(values) > 0
  File "/.venv/lib/python3.8/site-packages/django/db/models/query.py", line 885, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/.venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1783, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/.venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1361, in execute_sql
    cursor.execute(sql, params)
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/.venv/lib/python3.8/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (195,1) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

The text was updated successfully, but these errors were encountered:

LudovicPatey · 2022-12-19T12:25:32Z

I have the same problem. Any insight?

Muhammadinaam · 2023-06-14T21:10:21Z

I also faced this issue:

django.db.utils.OperationalError: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (523,1) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

marianoeramirez · 2023-06-14T21:41:20Z

I will take a look on this during the weekend.

marianoeramirez · 2023-06-30T23:38:42Z

Hey people, this looks more like a problem in your database configuration than something general. Can you add more information on what database you have and if you checked any of the Stackoverflow answers?

darklow · 2023-09-05T22:00:15Z

Same issue for me, couldn't find to make it work.

I tried Postgres with and without Postgis versions 12, 14, 15 and initial import always fail with same error.

Django==4.2.5 (Also tried Django 4.0)
django-cities-light==3.9.2

./manage.py cities_light

Always fails with:

django.db.utils.OperationalError: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (794,4) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

Full log:

./manage.py cities_light --force-import-all
Assuming local download is up to date for http://download.geonames.org/export/dump/countryInfo.txt
Assuming local download is up to date for http://download.geonames.org/export/dump/admin1CodesASCII.txt
Assuming local download is up to date for http://download.geonames.org/export/dump/admin2Codes.txt
Assuming local download is up to date for http://download.geonames.org/export/dump/cities15000.zip
Saving Zhonghe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Zhonghe) already exists.\n')
Saving Xinglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Xinglong) already exists.\n')
Saving Longtan, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Longtan) already exists.\n')
Saving Linjiang, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Linjiang) already exists.\n')
Saving Guandu, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Guandu) already exists.\n')
Saving Longxing, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Longxing) already exists.\n')
Saving Shuanglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Shuanglong) already exists.\n')
Saving Sanjiao, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Sanjiao) already exists.\n')
Saving Shima, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Shima) already exists.\n')
Saving Taiping, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Taiping) already exists.\n')
Saving E’zhou, Hubei, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_slug_efb2e768_uniq"\nDETAIL:  Key (region_id, subregion_id, slug)=(627, 9789, ezhou) already exists.\n')
Saving Wushan, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Wushan) already exists.\n')
Saving Zhuxi, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Zhuxi) already exists.\n')
Saving Tai’an, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Tai’an) already exists.\n')
Saving Xinmin, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Xinmin) already exists.\n')
Saving Yong’an, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Yong’an) already exists.\n')
Saving Gaofeng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Gaofeng) already exists.\n')
Saving Xinsheng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Xinsheng) already exists.\n')
Saving Tonggu, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Tonggu) already exists.\n')
Saving Sanhe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Sanhe) already exists.\n')
Saving Xintian, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Xintian) already exists.\n')
Saving Yihe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Yihe) already exists.\n')
Saving Longhe, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Longhe) already exists.\n')
Saving Gulu, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Gulu) already exists.\n')
Saving Longfeng, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Longfeng) already exists.\n')
Saving Longxing, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Longxing) already exists.\n')
Saving Shizuishan, Ningxia Hui Autonomous Region, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(623, 9697, Shizuishan) already exists.\n')
Saving Liangping, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Liangping) already exists.\n')
Saving Xinglong, Chongqing, China failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(636, 9924, Xinglong) already exists.\n')
Saving Buenaventura, Valle del Cauca, Colombia failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(645, 10949, Buenaventura) already exists.\n')
Saving Bosconia, Cesar, Colombia failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(664, 10906, Bosconia) already exists.\n')
Saving Hayes, England, United Kingdom failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(1028, 13468, Hayes) already exists.\n')
Saving Panauti, Bagmati Province, Nepal failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_slug_efb2e768_uniq"\nDETAIL:  Key (region_id, subregion_id, slug)=(2414, 27926, panauti) already exists.\n')
Saving Vincent, California, United States failed: IntegrityError('duplicate key value violates unique constraint "cities_light_city_region_id_subregion_id_name_cdfc77ea_uniq"\nDETAIL:  Key (region_id, subregion_id, name)=(3637, 42267, Vincent) already exists.\n')
Assuming local download is up to date for http://download.geonames.org/export/dump/alternateNames.zip
Traceback (most recent call last):
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ProgramLimitExceeded: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (794,4) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 22, in <module>
    main()
  File "./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/Users/project/.venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/Users/project/.venv/lib/python3.8/site-packages/cities_light/management/commands/cities_light.py", line 242, in handle
    self.translation_import()
  File "/Users/project/.venv/lib/python3.8/site-packages/cities_light/management/commands/cities_light.py", line 686, in translation_import
    model.save(force_update=True)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 814, in save
    self.save_base(
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 877, in save_base
    updated = self._save_table(
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 990, in _save_table
    updated = self._do_update(
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/base.py", line 1054, in _do_update
    return filtered._update(values) > 0
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/query.py", line 1231, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1984, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/project/.venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: index row size 2848 exceeds btree version 4 maximum 2704 for index "cities_light_city_search_names_fb77fed2"
DETAIL:  Index row references tuple (794,4) in relation "cities_light_city".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

mo-waseem · 2024-01-03T10:27:07Z

Hello @marianoeramirez
any updates about this issue ?

marianoeramirez · 2024-01-03T22:45:38Z

Thanks, that's very helpful for understanding the issue.

In the meantime we include any longterm improvement in the code, please drop the index that is generating that problem, from the logs looks like "cities_light_city_search_names_fb77fed2"

marianoeramirez · 2024-01-08T20:57:00Z

Another alternative is you can use CITIES_LIGHT_INDEX_SEARCH_NAMES = False once start to do the migration

marianoeramirez · 2024-01-08T21:33:04Z

#287 fixes the problem with the default configuration for PostgreSQL, to allow the owner of the project decide on the index depending on his use. Closing this ticket for now.

marianoeramirez closed this as completed Jan 8, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

BUG: Operational error while to trying to load data #273

BUG: Operational error while to trying to load data #273

marcelonp commented Sep 20, 2022

LudovicPatey commented Dec 19, 2022

Muhammadinaam commented Jun 14, 2023

marianoeramirez commented Jun 14, 2023

marianoeramirez commented Jun 30, 2023

darklow commented Sep 5, 2023

mo-waseem commented Jan 3, 2024

marianoeramirez commented Jan 3, 2024

marianoeramirez commented Jan 8, 2024

marianoeramirez commented Jan 8, 2024

BUG: Operational error while to trying to load data #273

BUG: Operational error while to trying to load data #273

Comments

marcelonp commented Sep 20, 2022

LudovicPatey commented Dec 19, 2022

Muhammadinaam commented Jun 14, 2023

marianoeramirez commented Jun 14, 2023

marianoeramirez commented Jun 30, 2023

darklow commented Sep 5, 2023

mo-waseem commented Jan 3, 2024

marianoeramirez commented Jan 3, 2024

marianoeramirez commented Jan 8, 2024

marianoeramirez commented Jan 8, 2024