Openstreetmap-website: ERROR: column "nearby" of relation "users"

[trevorwilf]

Im pretty new to this so I am not sure if this is a new bug or not. I am following the install guide. I saw this
https://github.com/openstreetmap/openstreetmap-website/issues/2417
and thought it might be related to the change.

when I do an import using the following command:
/osmosis/bin/osmosis \
--read-pbf /data.osm.pbf \
--bounding-polygon file="/data.poly" \
--write-apidb host="localhost" \
dbType="postgresql" database="openstreetmap" \
user="openstreetmap" password="myPass" validateSchemaVersion="no"

I get the following error:

Dec 04, 2019 12:16:31 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.47
Dec 04, 2019 12:16:31 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Dec 04, 2019 12:16:31 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Dec 04, 2019 12:16:31 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Dec 04, 2019 12:16:32 AM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-pbf failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to insert user with id -1 into the database.
        at org.openstreetmap.osmosis.apidb.v0_6.impl.UserManager.insertUser(UserManager.java:115)
        at org.openstreetmap.osmosis.apidb.v0_6.impl.UserManager.addOrUpdateUser(UserManager.java:163)
        at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1169)
        at crosby.binary.osmosis.OsmosisBinaryParser.parse(OsmosisBinaryParser.java:259)
        at org.openstreetmap.osmosis.osmbinary.BinaryParser.handleBlock(BinaryParser.java:64)
        at org.openstreetmap.osmosis.osmbinary.file.FileBlock.process(FileBlock.java:135)
        at org.openstreetmap.osmosis.osmbinary.file.BlockInputStream.process(BlockInputStream.java:34)
        at crosby.binary.osmosis.OsmosisReader.run(OsmosisReader.java:45)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: column "nearby" of relation "users" does not exist
  Position: 129
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
        at org.openstreetmap.osmosis.apidb.v0_6.impl.UserManager.insertUser(UserManager.java:112)
        ... 8 more

Dec 04, 2019 12:16:32 AM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
        at org.codehaus.classworlds.Launcher.main(Launcher.java:47)

[tomhughes]

This is a bug in omosis, or rather osmosis needs updating to reflect recent schema changes - it is trying to fill in the nearby column in the users table but that no longer exists.

[gravitystorm]

Ouch, that's a bit of a headache, given that osmosis is unmaintained and any new release is unlikely.

Arguably osmosis shouldn't have been trying to fill in that column anyway, since it had a default and so the insert could have ignored it.

I think a workaround would be for the user to re-add the column, run the osmosis command, and then drop the column again. Alternatively, if you can compile osmosis from source, then edit the relevant line in the code first.

Does anyone know of any other mechanism for loading an osm file into the database? I imagine it's a fairly common task for small deployments.

[jalessio]

Does anyone know of any other mechanism for loading an osm file into the database?

This is definitely a part of the OSM ecosystem that I have struggled to understand. It seems there are a whole suite of tools for taking OSM data which _have already been extracted_ from the database defined in this repository and importing into _other tools_, but I haven't come across anything but ~osmium~ osmosis for importing data directly into the database defined in this repository.

I imagine it's a fairly common task for small deployments.

Just to clarify, is there another tool you'd recommend for "large deployments" or am I reading too much into you comment?

@gravitystorm if getting osmium updated and re-deployed widely is unrealistic, what tool do you think would be a good starting point for building a successor for this use case? Maybe https://github.com/omniscale/imposm3?

[tomhughes]

Well loading data into an API database is probably not a common requirement - it's certainly not something we ever need to do for example so we have no need ourselves to create tools for it.

It's osmosis by the way, not osmium that we're talking about here ;-)

As to imposm3 it's not something I have used myself though I have heard of it. I don't think it can currently loading an API database though? Certainly if I was looking at writing something to do that then might be somewhere I would look, or just at building something on top of libosmium.

[simonpoole]

Does anyone know of any other mechanism for loading an osm file into the database?

This is definitely a part of the OSM ecosystem that I have struggled to understand. It seems there are a whole suite of tools for taking OSM data which _have already been extracted_ from the database defined in this repository and importing into _other tools_, but I haven't come across anything but osmium for importing data directly into the database defined in this repository.

This is because there is no real use case that would require this (and actually reloading original OSM data is not something you would want to try except in a dire emergency), you are far better off with normal backups.

I imagine it's a fairly common task for small deployments.

Just to clarify, is there another tool you'd recommend for "large deployments" or am I reading too much into you comment?

@gravitystorm if getting osmium updated and re-deployed widely is unrealistic, what tool do you think would be a good starting point for building a successor for this use case? Maybe https://github.com/omniscale/imposm3?

imposm does something completely different (it's an alternative to osm2pgsql for rendering databases).

Dropping osmosis is a policy decision, explicit or not, it would be a small thing to pay somebody for further maintenance of osmosis until there is a full replacement. osmium is likely going to be taking over some of the functionality, but in the end suffers from the same issues (bus factor one).

[gravitystorm]

Just to clarify, is there another tool you'd recommend for "large deployments" or am I reading too much into you comment?

Sorry, I meant to distinguish other deployments from the main OSMF instance - afaik all secondary deployments are "smaller" than OSMF. For the OSMF instance, there's never going to be a need to load a blank database with fresh data. For many other instances, again people might start from an empty database, but they might also start by loading some data.

This is because there is no real use case that would require this

There's a real use case here, albeit a small one, which is organisations loading extracts in order to do things like exploring planning applications, or other mapping that's not being done in the OSMF instance like subjective or confidential humanitarian work. It's something that I've been contracted to work on in the past. But it's not a huge production use-case.

However, it's also a reasonable development use-case, since building the UI on an empty database is tedious for developers - particularly things like paging navigation on the relations browse pages. If you can't load an extract, you have to do a lot of fake mapping via id or JOSM to get 10,000 relations into your local db. Loading existing data, like an extract, makes more sense here.

@gravitystorm if getting osmosis updated and re-deployed widely is unrealistic, what tool do you think would be a good starting point for building a successor for this use case?

As @tomhughes says, if not osmosis then something new built on libosmium is likely reasonable. But I'd encourage you to look into osmosis, since the previous maintainer is quite happy to hand over control to anyone who is interested in working on it, and it does a ton of useful things already, and getting it fixed for this problem would only be a small amount of work.

[mmd-osm]

I came here because this issue popped up here again: https://help.openstreetmap.org/questions/72166/populating-database-on-rails-port-osmosis-fails

Given that we have pending fixes for osmosis production issues since quite some time (https://github.com/openstreetmap/osmosis/pull/51) I have zero hope for this tool. It should be completely removed from the ecosystem. taking over any kind of maintenance looks like it’s not going to happen at all.

[tomhughes]

It is already largely removed from production as far as the core project is concerned. The only thing that still uses it is the diff generation I believe.

There is ongoing work to redo the diff generation to work with newer versions of Postgres and that should also remove osmosis from the equation.

[mmd-osm]

Right, @zerebubuth mentioned that some new diff replication should be available real soon now, although I haven’t really found out where this is happening or who’s working on it. Seems to be some kind of undercover operation.

[tomhughes]

@joto is working on it...

[KidA001]

Came here because I'm stuck on the same issue. For anyone looking for a temporary workaround to import you can create a migration file and add

class AddNearbyToUsers < ActiveRecord::Migration[6.0]
  def change
    add_column "users", "nearby", :integer, :default => 50
  end
end

[mmd-osm]

However, it's also a reasonable development use-case, since building the UI on an empty database is tedious for developers

Agree. Although osmosis is somewhat slow, it used to be a valuable option to set up an OSM clone based on some country extract. In particular, creating identical user accounts (with fake email addresses) and object ids like on osm.org is really helpful. Hopefully we can somehow migrate this osmosis feature to a new tool, too.

[caduguedess]

There is a solution for this "Unable to insert user with id -1 into the database" issue?
There is another tool to substitute osmosis that do the job?

[migurski]

As of version 0.47.1, Osmosis now correctly omits the users.nearby column: https://github.com/openstreetmap/osmosis/releases/tag/0.47.1

[prusswan]

Was trying to setup API DB using regional extract (and running into import issues such as https://lists.openstreetmap.org/pipermail/osmosis-dev/2011-March/000949.html). @caduguedess's issue might be a case of not having the schema in place - the steps up till bundle exec rake db:migrate are necessary. On my end, still trying to resolve:

ERROR: duplicate key value violates unique constraint "users_display_name_idx"

It is unfortunate that a lot of guides on OSM data imports (even the official docs https://github.com/openstreetmap/openstreetmap-website/blob/master/CONFIGURE.md) still reference the use of osmosis (for lack of a better alternative), despite caveats and known issues etc. This poses a challenge for those hoping to leverage OSM dev stack and understand its actual workings.

[zerebubuth]

On my end, still trying to resolve:

ERROR: duplicate key value violates unique constraint "users_display_name_idx"

Unfortunately, it seems that loading an extract into a Rails-schema database isn't on a happy path because it isn't something anyone does very frequently. On the downside, this means you're likely to encounter problems, but on the upside you're a pioneer! :wink:

I'm not aware of any better tool than Osmosis to do this, sorry (even taking into account the high likelihood of encountering further problems).

The first item of advice in the mailing list post you referenced is the one I would take if I wanted to load an extract: drop the table constraint, load the data, then repair the data so that the display_name fields are unique (e.g: update users set display_name = 'user_' || id where display_name = {whatever the duplicate value is}), then re-create the table constraint so that Rails is happy.

Hope that helps!

[prusswan]

@zerebubuth thanks for your response, I will try that since there is nothing better. It is probably the same problem as explained by Simon here.

I also found the related discussion at #2543 which explains why there is no little/no support on seeding the API DB using data exports (as it is not required for the official workflows including testing). Even if that is the case, the documentation should be more explicit about this so that known issues can be made clear to new users.

[migurski]

I’ll explore ways that Osmosis can handle this.