[OSM-dev] Nominatim updates stuck with "tuple already modified"

[Frederik Ramm]

Hi,

a couple of our (Geofabrik) Nominatim servers stopped updating properly
about 4 days ago. Closer inspection has shown that this was due to a
self-referencing place=locality relation (Chernobyl-2). I have fixed
that in the OSM data just now.

Affected systems would log something like

ERROR:  tuple to be updated was already modified by an operation
triggered by the current command
HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to
propagate changes to other rows.

when running update.php. This would also be in your postgres log file. A
tell-tale sign would also be if your import_osmosis_log table ends with
something like

 2020-09-07 17:23:01 |  4185363 |    670328 | 2020-09-07 17:23:16 |
2020-09-07 17:23:20 | import

without a matching "index" line.

Fixing the problem seems to require this SQL command:

update planet_osm_rels set
parts='{437725919}',members='{w437725919,outer}' where id=8184246;

After this, if you run the update.php script again, it will complete
indexing and resume downloading new data from OSM. It is possible that -
depending how your updating is configured - you run into the same issue
a second time, because there are two broken versions of the relation on
OSM and if the 2nd hasn't been downloaded yet, then it will overwrite
your fix. You will then have to enter the same "update" command again.
But after that, it should work.

I've filed an issue with Nominatim about this
https://github.com/osm-search/Nominatim/issues/1941 - just writing here
in case someone encounters the same issue.

Bye
Frederik

-- 
Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00'09" E008°23'33"