Opened 6 years ago

Last modified 22 months ago

#4597 new defect

Osmosis crashes when loading planet.osm

Reported by: marcelo@… Owned by: brett@…
Priority: blocker Milestone:
Component: osmosis Version:
Keywords: planet.osm, osmosis, transaction, limit Cc:

Description

I've been trying to load the full planet.osm data file into postgreSQL for more than two months now... and ran into a completely new problem.

The first time I tried was with a planet.osm from the beginning of August/2012, and it loaded all the nodes, ways and relations, but when it was loading the current tables, a power failure resetted the machine and I was left with only the history tables loaded. Tried loading them by hand since the history tables were the same as the current tables (no changes were applied). Had other problems not related, and gave up.

I decided to download a new version of the planet.osm, this one is from 02/Sep/2012. Unpacked everything, deleted all the tables, and tried:

osmosis -v --read-xml file=g:\OSM\planet-latest.osm --buffer bufferCapacity=100000 --log-progress --write-apidb host=localhost database=postgres user=<user> password=<password>

Osmosis version was 0.40.1, and postgreSQL version was 9.1

It ran for about 6 days, loaded all nodes, and at about 90% of the ways, it crashed. The message for this error was not saved, but the most critical part ("cannot have morre than 232-1 commands in a transaction") is the same.

Then, postgreSQL 9.2 was released, and I decided to upgrade both postgreSQL (to 9.2) and osmosis (to 0.41 - the latest at the time). Also got and used the latest schema version. Ended doing a clean postgreSQL install, and tried again with the command:

osmosis -v --fast-read-xml file=g:\OSM\planet-latest.osm --buffer bufferCapacity=100000 --log-progress --write-apidb host=localhost:5433 database=postgres user=<user> password=<password>

After one week, crashed with the same error (the full message is attached):

ERRO: nÒo pode ter mais do que 232-1 comandos em uma transaþÒo

Onde: comando SQL "SELECT 1 FROM ONLY "osm"."ways" x WHERE "way_id" OPERATOR(pg_catalog.=) $1 AND "version" OPERATOR(p

g_catalog.=) $2 FOR SHARE OF x"

Translation:

ERROR: Cannot have more than 232-1 commands in a transaction Where: SQL command is "SELECT 1 FROM ONLY "osm"."ways" x WHERE "way_id" OPERATOR(pg_catalog.=) $1 AND "version" OPERATOR(p
g_catalog.=) $2 FOR SHARE OF x"

Something changed (or grew) beyond some internal limit in postgreSQL and now it is impossible to load the full planet.osm data file.

I do not have the time available to retry loading an older planet.osm and see if the error appears again.

I believe that osmosis shouldn't encapsulate ALL the commands for the FULL planet.osm load in a single transaction (or at least I should have the option of not doing it). I also believe that committing the nodes, then the ways and finally the relations would be only a temporary measure, since the database WILL grow to more than 232-1 nodes and (given enough time) to more than 232-1 ways. Or, maybe postgreSQL should start using a 64 bit value for the commands in a transaction...

Attachments (1)

osmosis_error_20120927.txt (5.5 KB) - added by marcelo@… 6 years ago.
Full error message thrown by osmosis

Download all attachments as: .zip

Change History (9)

Changed 6 years ago by marcelo@…

Full error message thrown by osmosis

comment:1 Changed 6 years ago by brett@…

I believe the right fix for this is to modify the implementation to import data using the PostgreSQL COPY command. The COPY command is used to load the pgsnapshot schema and is far more efficient. This is not a trivial undertaking and may not get done for some time unless somebody else steps in to assist.

comment:2 Changed 6 years ago by marcelo@…

I tried to load a PBF planet.osm file from the first days of August, 2012, and the crash did not happen at all. Main tables loaded succesfully, now loading the current tables. So, indeed, there is some kind of limit that was crossed between August, 2012 and September, 2012.

I also believe that the COPY command would not solve the problem, at least not without the use of intermediate TEXT ou CSV files, because of the keys that relate one kind of object to the other. However, I am not a PostgreSQL specialist.

comment:3 Changed 6 years ago by marcelo@…

Downloaded the source code for osmosis directly from the git repository, and changed the file responsible for the database context:

  • Setting "autoCommit" to true, every time, ignoring if any code tried to set it to false.
  • Disabling the "commit" method (since everything would be committed immediately anyway).

Compiled successfully, now trying to load a planet.osm file from 11/Dec/2012. Nodes and node tags are appearing instantly on the tables (before the changes, they only appeared after all the history tables were loaded - with the old planet.osm file described before).

I'll post an update in a few days, with the success (or failure) status.

P.S.: I *KNOW* it is not a good practice to do this, but if it works, I'll be able to try a more refined solution. Nothing was committed to the git repository as of now.

P.P.S.: Performance seems to be noticeably lower than before the changes.

comment:4 Changed 6 years ago by marcelo@…

Success! The history tables are loaded, and currently a few percent into loading the current tables. Using the same planet.osm that gave me the error before, but no error now.

Therefore it is confirmed that the problem is directly linked to the usage of a single transaction encapsulating all the operations of the loading of the full planet.osm file.

In my opinion, the problem here may be treated in two distinct ways:

  • Treat the problem as a PostgreSQL-specific problem and not an osmosis problem. In this case, a bug report (or feature request) will have to be made through the appropriate channel of the PostgreSQL community.
  • Treat the problem as one of how to "break" the transactions in osmosis in smaller batches, in a way that does not trigger this error.

comment:5 Changed 6 years ago by marcelo@…

Current tables are fully loaded now (in truth, for more than one month now, OSM was not high in my priorities for the last months), now updating the database, catching up with many months of delay. Using the "stock" version of osmosis (with transactions enabled).

Ran into other (serious) problems with the "populateCurrentTables" option, but this is material for another, separated, ticket.

Therefore no doubt exists that the problem is solvable by the (temporary) disabling of transactions in osmosis through changes in the source code when loading the full planet.osm file.

Last edited 6 years ago by marcelo@… (previous) (diff)

comment:6 follow-up: Changed 3 years ago by baditaflorin

This bug still exists ?

I am waiting for six days, and i think i will have to wait some more

If i will wait, i would like to know if it will finish or not

http://www.openstreetmap.org/user/baditaflorin/diary/35844#comments

comment:7 in reply to: ↑ 6 Changed 3 years ago by marcelo@…

Replying to baditaflorin:

This bug still exists ?

I am waiting for six days, and i think i will have to wait some more

If i will wait, i would like to know if it will finish or not

http://www.openstreetmap.org/user/baditaflorin/diary/35844#comments

Sorry, I do not know if the bug still persists. Work (and family) demands have left me with far too little time available for OSM-related tasks.

Note: See TracTickets for help on using tickets.