Applying changes to database via osm2pgsql freezes when processing relations

Help and support
1

Hi all, maybe you can help me on this.

I have set up a OSM planet database. When applying a changeset processing always freezes at the step where it apparently processes relations. Output hangs at "Left to process: ":

2023-11-26 12:14:55 [00] Going over 523 pending relations (using 1 threads)
Left to process: 522…

The osm2pgsql are at 100% CPU. I waited for several hours and processing was not continued.So far I have tried out the following:

  • different change sets, neither worked
  • initially osm2pgsql version was 1.9.2. Update to 1.10 and building osm2pgsql from source didn’t help
  • running osm2pgsql update with different parameters showed no difference
  • I set up a test system from scratch, which showed the same behaviour.

Any idea?

Thanks, Thomas

More detailed information:

System:
Ubuntu 22.04
Postresql 14

osm2pgsql --version
2023-11-26 11:58:59 osm2pgsql version 1.9.2 (1.9.2-31-gbf00c512)
Build: RelWithDebInfo
Compiled using the following library versions:
Libosmium 2.19.0
Proj [API 6] 8.2.1
Lua 5.3.6

database was created with:
osm2pgsql --create --style /./default.style -d gis -U osm -C 64000 --slim --tablespace-slim-data hdd --tablespace-slim-index hdd --number-processes 16 --extra-attributes -r pbf /data/osm/planet-latest.osm.pbf -v

update:
osm2pgsql --append --style ./default.style -d gis -U osm --slim --number-processes 1 --log-level=debug ./workdir/changes.osc.gz -v

2023-11-26 12:14:47 osm2pgsql version 1.10.0 (1.10.0)
2023-11-26 12:14:47 [00] Database version: 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
2023-11-26 12:14:47 [00] PostGIS version: 3.2
2023-11-26 12:14:47 [00] Found properties table ‘osm2pgsql_properties’: true.
2023-11-26 12:14:47 [00] Loading properties from table ‘“public”.“osm2pgsql_properties”’.
2023-11-26 12:14:47 [00] Updating with attributes (same as on import).
2023-11-26 12:14:47 [00] Not using flat node file (same as on import).
2023-11-26 12:14:47 [00] Using prefix ‘planet_osm’ (same as on import).
2023-11-26 12:14:47 [00] Using output ‘pgsql’ (same as on import).
2023-11-26 12:14:47 [00] Using style file ‘/singletrails/conf/osm2pgsql/default.style’ (same as on import).
2023-11-26 12:14:47 [00] Reading file: /singletrails/scripts/osmosis/workdir/changes.osc.gz
2023-11-26 12:14:47 [00] Started pool with 1 threads.
2023-11-26 12:14:47 [00] Mid: pgsql, cache=800, db_format=1
2023-11-26 12:14:47 [00] Using projection SRS 3857 (Spherical Mercator)
2023-11-26 12:14:47 [00] Using built-in tag transformations
2023-11-26 12:14:47 [00] Middle ‘pgsql’ options:
2023-11-26 12:14:47 [00] nodes: true
2023-11-26 12:14:47 [00] untagged_nodes: true
2023-11-26 12:14:47 [00] db_format: 1
2023-11-26 12:14:47 [00] use_flat_node_file: false
2023-11-26 12:14:47 [00] way_node_index_id_shift: 5
2023-11-26 12:14:47 [00] with_attributes: true
2023-11-26 12:14:47 [00] Setting up table ‘planet_osm_point’
NOTICE: table “planet_osm_point_tmp” does not exist, skipping
2023-11-26 12:14:47 [00] Setting up table ‘planet_osm_line’
NOTICE: table “planet_osm_line_tmp” does not exist, skipping
2023-11-26 12:14:47 [00] Setting up table ‘planet_osm_polygon’
NOTICE: table “planet_osm_polygon_tmp” does not exist, skipping
2023-11-26 12:14:47 [00] Setting up table ‘planet_osm_roads’
NOTICE: table “planet_osm_roads_tmp” does not exist, skipping
2023-11-26 12:14:49 [00] Found 46035 new/changed nodes in input.
2023-11-26 12:14:49 [00] Found in 0s their 6778 parent ways and 78 parent relations.
Processing: Node(53k 26.5k/s) Way(1k 1.00k/s) Relation(0 0.0/s)
2023-11-26 12:14:50 [00] Found 7958 ways that are new/changed in input or parent of changed node.
2023-11-26 12:14:50 [00] Found in 0s their 656 parent relations.
2023-11-26 12:14:52 [00] Reading input files done in 5s.
2023-11-26 12:14:52 [00] Processed 53039 nodes in 2s - 27k/s
2023-11-26 12:14:52 [00] Processed 7122 ways in 1s - 7k/s
2023-11-26 12:14:52 [00] Processed 229 relations in 2s - 114/s
2023-11-26 12:14:52 [00] Overall memory usage: peak=725MByte current=701MByte
2023-11-26 12:14:53 [00] Going over 1252 pending ways (using 1 threads)
Left to process: 0…
2023-11-26 12:14:55 [00] Processing 1252 pending ways took 2s at a rate of 626.00/s
2023-11-26 12:14:55 [00] Going over 523 pending relations (using 1 threads)
Left to process: 522…2023-11-26 12:14:55 [00] Going over 523 pending relations (using 1 threads)
Left to process: 522…

3

Hi all,

I think I’ve figured it out

With some additional tests I found that the problem was caused by the --extra-attributes flag and the way the additional attributes osm_version and osm_timestamp were defined in the osm2pgsql style file:

node,way osm_version int4 linear
node,way osm_timestamp text linear

When I changed linear to polygon and rebuild the database with the updated style file the osmosis updates worked perfectly.

I also found out that when running osm2pgsql in the --append mode the handling of extra attributes seems to be defined rather by the “attributes” entry in the osm2pgsql_properties table than the --extra-attributes flag.

1 Like
2

Hi Jochen,

I tried it with different changes.osc.gz, that I got via:
osmosis --rri workingDirectory=./workdir --simplify-change --write-xml-change ./workdir/changes.osc.gz
To produce different sets I used state.txt with different timestamps. Initially I produced them via
http://osm.personalwerk.de/replicate-sequences/

As I set up two different servers approximately 4 weeks apart with the actual planet.osm.pbf a that time, this already resulted in different changesets. To try out even more I downloaded some more state.txt files from Index of /replication/hour (e.g. https://planet.openstreetmap.org/replication/hour/000/096/997.state.txt)

All tests resulted in the same error of osm2pgsql hanging at the “Left to process:”-step of relations, but with different numbers (always 1 less then the total number indicated in “Going over xxx pending relations”)