search

osm-search / Nominatim

Open Source search based on OpenStreetMap data
https://nominatim.org
GNU General Public License v3.0
3.15k stars 715 forks source link

psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "parent" when trying to import #2942

Closed jhrvalente closed 1 year ago

jhrvalente commented 1 year ago

I had created an empty database called nominatim on my DB cluster, pointed the DSN to my database with the dbname, host, port, user and pass and tried to import the map of Portugal with the following params:

nohup <path/to/file>/build/utils/setup.php \
 --threads 4 \
 --setup-db \
 --import-data \
 --create-functions \
 --enable-diff-updates \
 --enable-debug-statements \
 --create-tables \
 --create-partition-tables \
 --create-partition-functions \
 --import-wikipedia-articles \
 --load-data \
 --import-tiger-data \
 --calculate-postcodes \
 --index \
 --index-noanalyse \
 --create-search-indices \
 --create-country-names \
 --osm-file <path/to/file>/portugal-latest.osm.pbf >> <path/to/file>/logImportPortugal.txt 2>&1

But it is giving me the following error:

 WARNING: Starting rank 25
Traceback (most recent call last):
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 370, in <module>
    Indexer(options).run()
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 215, in run
    self.index(RankRunner(self.maxrank))
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 259, in index
    thread = next(next_thread)
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 300, in find_free_thread
    if thread.is_done():
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 178, in is_done
    if self.conn.poll() == psycopg2.extensions.POLL_OK:
psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "parent"
LINE 1: parent.place_id
        ^
QUERY:  parent.place_id
CONTEXT:  PL/pgSQL function find_parent_for_address(text,text,smallint,geometry) line 25 at RAISE
PL/pgSQL function find_parent_for_poi(character,bigint,smallint,geometry,text,text,boolean) line 31 at assignment
PL/pgSQL function placex_update() line 178 at assignment

ERROR: error status 1 running nominatim!
string(33) "error status 1 running nominatim!"

The flag --setup-db shouldn't create all the relations? What am I missing?

Below is the full log:

nohup: ignoring input
2023-01-10 13:00:49 == module path: /srv/nominatim/build/module
2023-01-10 13:00:49 == Setup DB
Postgres version found: 14
Postgis version found: 3.2
 set_config
------------

(1 row)

2023-01-10 13:00:54 == Import data
osm2pgsql version 1.2.0 (64 bit id space)

Mid: loading persistent node cache from /srv/nominatim/flatnode.file
Mid: pgsql, cache=0
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Parsing gazetteer style file '/srv/nominatim/Nominatim-3.5.1/settings/import-full.style'.
Using projection SRS 4326 (Latlong)
NOTICE:  table "place" does not exist, skipping

Reading in file: /home/izadmin/planet_full/portugal-latest.osm.pbf
Using PBF parser.
^MProcessing: Node(50k 50.0k/s) Way(0k 0.00k/s) Relation(0 0.00/s)^MProcessing: Node(150k 75.0k/s) Way(0k 0.00k/s) Relation(0 0.00/s)^MProcessing: Node(310k 103.3k/s) Way(0k 0.00k/s) Relation>Node stats: total(36476942), max(10423206981) in 223s
Way stats: total(3678221), max(1129408209) in 88s
Relation stats: total(75907), max(15101706) in 7s
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Stopped table: planet_osm_ways in 0s
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
Stopped table: planet_osm_rels in 0s

Osm2pgsql took 319s overall
2023-01-10 13:06:14 == Create Functions
2023-01-10 13:06:14 == Create Tables
2023-01-10 13:06:14 == Create Functions
2023-01-10 13:06:14 == Create Tables
2023-01-10 13:06:15 == Create Partition Tables
2023-01-10 13:06:16 == Create Partition Functions
2023-01-10 13:06:16 == Importing wikipedia articles and redirects
2023-01-10 13:08:08 == Drop old Data
...............................................................................................................................................................................................>2023-01-10 13:08:13 == Loading word list
 count
--------
 318141
(1 row)

 count
-------
 14556
(1 row)

2023-01-10 13:09:01 == Load Data

2023-01-10 13:43:34 == Reanalysing database
Latest data imported from 2023-01-09T21:15:53Z.
2023-01-10 13:43:37 == Import Tiger data
2023-01-10 13:43:37 == Found 0 SQL files in path /srv/nominatim/Nominatim-3.5.1/data/tiger
2023-01-10 13:43:37 == WARNING: Tiger data import selected but no files found in path /srv/nominatim/Nominatim-3.5.1/data/tiger
2023-01-10 13:43:37 == Calculate Postcodes
2023-01-10 13:44:47 == Index ranks 0 - 4
'/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py' --database nominatim --port 5432 --threads 4 -v --host '127.0.0.1' --user postgres --maxrank 4WARNING: Starting indexing rank (0 to 4) >WARNING: Starting rank 0
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 0

WARNING: Starting rank 1
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 1

WARNING: Starting rank 2
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 2

WARNING: Starting rank 3
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 3

WARNING: Starting rank 4
WARNING: Done 2/2 in 0 @ 3.477 per second - FINISHED rank 4

2023-01-10 13:44:47 == Index ranks 5 - 25
WARNING: Starting indexing rank (5 to 25) using 4 threads
WARNING: Starting rank 5
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 5

WARNING: Starting rank 6
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 6
WARNING: Starting rank 7
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 7

WARNING: Starting rank 8
WARNING: Done 2/2 in 0 @ 7.341 per second - FINISHED rank 8

WARNING: Starting rank 9
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 9

WARNING: Starting rank 10
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 10

WARNING: Starting rank 11
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 11

WARNING: Starting rank 12
WARNING: Done 37/37 in 0 @ 139.460 per second - FINISHED rank 12

WARNING: Starting rank 13
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 13

WARNING: Starting rank 14
INFO: Done 100 in 0 @ 389.785 per second - rank 14 ETA (seconds): 0.53
WARNING: Done 308/308 in 0 @ 321.383 per second - FINISHED rank 14

WARNING: Starting rank 15
WARNING: Done 1/1 in 0 @ 661.813 per second - FINISHED rank 15

WARNING: Starting rank 16
INFO: Done 100 in 0 @ 587.337 per second - rank 16 ETA (seconds): 5.38
INFO: Done 687 in 1 @ 516.197 per second - rank 16 ETA (seconds): 4.99
INFO: Done 1203 in 2 @ 507.508 per second - rank 16 ETA (seconds): 4.06
INFO: Done 1710 in 3 @ 488.050 per second - rank 16 ETA (seconds): 3.18
INFO: Done 2198 in 4 @ 455.463 per second - rank 16 ETA (seconds): 2.34
INFO: Done 2653 in 5 @ 450.394 per second - rank 16 ETA (seconds): 1.35
INFO: Done 3103 in 7 @ 425.337 per second - rank 16 ETA (seconds): 0.37
WARNING: Done 3262/3262 in 8 @ 388.872 per second - FINISHED rank 16

WARNING: Starting rank 17
INFO: Done 100 in 0 @ 1959.248 per second - rank 17 ETA (seconds): 0.01
WARNING: Done 127/127 in 0 @ 1647.831 per second - FINISHED rank 17
WARNING: Starting rank 18
INFO: Done 100 in 0 @ 1458.789 per second - rank 18 ETA (seconds): 2.29
INFO: Done 1558 in 0 @ 1776.158 per second - rank 18 ETA (seconds): 1.06
INFO: Done 3334 in 1 @ 1774.513 per second - rank 18 ETA (seconds): 0.06
WARNING: Done 3447/3447 in 1 @ 1777.266 per second - FINISHED rank 18

WARNING: Starting rank 19
INFO: Done 100 in 0 @ 3007.609 per second - rank 19 ETA (seconds): 1.98
INFO: Done 3107 in 1 @ 2444.868 per second - rank 19 ETA (seconds): 1.20
INFO: Done 5551 in 2 @ 2570.094 per second - rank 19 ETA (seconds): 0.19
WARNING: Done 6053/6052 in 2 @ 2644.112 per second - FINISHED rank 19

WARNING: Starting rank 20
INFO: Done 100 in 0 @ 2110.150 per second - rank 20 ETA (seconds): 13.73
INFO: Done 2210 in 0 @ 2969.273 per second - rank 20 ETA (seconds): 9.04
INFO: Done 5179 in 2 @ 2321.562 per second - rank 20 ETA (seconds): 10.29
INFO: Done 7500 in 3 @ 2142.735 per second - rank 20 ETA (seconds): 10.07
INFO: Done 9642 in 4 @ 2055.257 per second - rank 20 ETA (seconds): 9.45
INFO: Done 11697 in 5 @ 2019.092 per second - rank 20 ETA (seconds): 8.60
INFO: Done 13716 in 6 @ 1998.935 per second - rank 20 ETA (seconds): 7.68
INFO: Done 15714 in 7 @ 1993.985 per second - rank 20 ETA (seconds): 6.70
INFO: Done 17707 in 8 @ 1990.804 per second - rank 20 ETA (seconds): 5.71
INFO: Done 19697 in 9 @ 1978.861 per second - rank 20 ETA (seconds): 4.74
INFO: Done 21675 in 11 @ 1960.704 per second - rank 20 ETA (seconds): 3.77
INFO: Done 23635 in 12 @ 1936.453 per second - rank 20 ETA (seconds): 2.81
INFO: Done 25571 in 13 @ 1929.992 per second - rank 20 ETA (seconds): 1.81
INFO: Done 27500 in 14 @ 1939.698 per second - rank 20 ETA (seconds): 0.81
WARNING: Done 29070/29067 in 14 @ 1942.903 per second - FINISHED rank 20

WARNING: Starting rank 21
INFO: Done 100 in 0 @ 2234.986 per second - rank 21 ETA (seconds): 0.48
WARNING: Done 1167/1167 in 0 @ 1538.119 per second - FINISHED rank 21

WARNING: Starting rank 22
INFO: Done 100 in 0 @ 1638.646 per second - rank 22 ETA (seconds): 18.97
INFO: Done 1738 in 0 @ 1982.608 per second - rank 22 ETA (seconds): 14.85
INFO: Done 3720 in 2 @ 1732.995 per second - rank 22 ETA (seconds): 15.85
INFO: Done 5452 in 3 @ 1742.091 per second - rank 22 ETA (seconds): 14.77
INFO: Done 7194 in 4 @ 1723.857 per second - rank 22 ETA (seconds): 13.92
INFO: Done 8917 in 5 @ 1717.254 per second - rank 22 ETA (seconds): 12.97
INFO: Done 10634 in 6 @ 1712.904 per second - rank 22 ETA (seconds): 12.00
INFO: Done 12346 in 7 @ 1716.312 per second - rank 22 ETA (seconds): 10.98
INFO: Done 14062 in 8 @ 1718.356 per second - rank 22 ETA (seconds): 9.96
INFO: Done 15780 in 9 @ 1720.232 per second - rank 22 ETA (seconds): 8.95
INFO: Done 17500 in 10 @ 1727.692 per second - rank 22 ETA (seconds): 7.92
INFO: Done 19227 in 11 @ 1727.651 per second - rank 22 ETA (seconds): 6.92
INFO: Done 20954 in 12 @ 1723.779 per second - rank 22 ETA (seconds): 5.93
INFO: Done 22677 in 13 @ 1715.023 per second - rank 22 ETA (seconds): 4.96
INFO: Done 24392 in 14 @ 1717.552 per second - rank 22 ETA (seconds): 3.95
INFO: Done 26109 in 15 @ 1697.694 per second - rank 22 ETA (seconds): 2.99
INFO: Done 27806 in 16 @ 1659.334 per second - rank 22 ETA (seconds): 2.04
INFO: Done 29465 in 17 @ 1662.434 per second - rank 22 ETA (seconds): 1.03
INFO: Done 31127 in 18 @ 1667.951 per second - rank 22 ETA (seconds): 0.03
WARNING: Done 31184/31183 in 18 @ 1668.224 per second - FINISHED rank 22

WARNING: Starting rank 23
WARNING: Done 0/0 in 0 @ 0.000 per second - FINISHED rank 23

WARNING: Starting rank 24
INFO: Done 100 in 0 @ 2041.400 per second - rank 24 ETA (seconds): 2.52
INFO: Done 2141 in 1 @ 1956.016 per second - rank 24 ETA (seconds): 1.59
INFO: Done 4097 in 2 @ 1911.586 per second - rank 24 ETA (seconds): 0.60
WARNING: Done 5248/5245 in 2 @ 1908.391 per second - FINISHED rank 24

WARNING: Starting rank 25
Traceback (most recent call last):
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 370, in <module>
    Indexer(options).run()
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 215, in run
    self.index(RankRunner(self.maxrank))
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 259, in index
    thread = next(next_thread)
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 300, in find_free_thread
    if thread.is_done():
  File "/srv/nominatim/Nominatim-3.5.1/nominatim/nominatim.py", line 178, in is_done
    if self.conn.poll() == psycopg2.extensions.POLL_OK:
    psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "parent"
LINE 1: parent.place_id
        ^
QUERY:  parent.place_id
CONTEXT:  PL/pgSQL function find_parent_for_address(text,text,smallint,geometry) line 25 at RAISE
PL/pgSQL function find_parent_for_poi(character,bigint,smallint,geometry,text,text,boolean) line 31 at assignment
PL/pgSQL function placex_update() line 178 at assignment

ERROR: error status 1 running nominatim!
string(33) "error status 1 running nominatim!"

I am using Nominatim 3.5.1

mtmail commented 1 year ago

Since this is a new installation, can you try the latest Nominatim release (4.2.0)? https://nominatim.org/release-docs/latest/admin/Installation/

jhrvalente commented 1 year ago

Per my requirements I have to use 3.5.1, and I have also everything already set for 3.5.1

mtmail commented 1 year ago

2023-01-10 13:43:37 == Found 0 SQL files in path /srv/nominatim/Nominatim-3.5.1/data/tiger 2023-01-10 13:43:37 == WARNING: Tiger data import selected but no files found in path /srv/nominatim/Nominatim-3.5.1/data/tiger

TIGER data is for the United States, you don't need to import it (--import-tiger-data) for Portugal.

Who is making the requirement to use version 3.5.1? There have been over 2 years of improvements. 3.5.1 is no longer supported https://github.com/osm-search/Nominatim/blob/master/SECURITY.md

jhrvalente commented 1 year ago

Yes, I know I dont need Tiger for Portugal, I just used the same command that I am using for other import with the same params. On the machine I am working, they already have a folder with the src compiled for nominatim with version 3.5.1 but I will try to create another folder for nominatim with version 4.2.0 and give it a try