search

coderholic / django-cities

Countries and cities of the world for Django projects
MIT License
913 stars 377 forks source link

Import fails on Postgres / Postgis #237

Closed darklow closed 10 months ago

darklow commented 10 months ago

I tried Postgres+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.
darklow commented 10 months ago

Sorry, wrong github repo / app. Maybe best to delete this issue.