NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum


When processing diff files (*.osc) how are changes sent to postgres? I assume it's via a delete and an insert, because when splitting long lines and multipolygons you don't have an unique id.

If I make my tables have a primary key (osm_id) and not splitting long lines and multipolygons are the changes then sent to postgres as an update statement?


Edit: to clarify, I want to add some filtering (current way I'm exploring is through triggers on the postgres tables) so I can expire tiles only when relevant changes appear. If the changes are done through delete and insert I can't compare them to see if anything interesting changed. If this isn't going to work, is it possible then in a lua script (flex-output)?

asked 15 Jun '21, 13:33

Paulosm2016's gravatar image

accept rate: 0%

edited 15 Jun '21, 14:55

osm2pgsql currently always uses DELETE to remove the old versions of objects and then COPY to insert the new versions, so you can use ON DELETE and ON INSERT triggers. (But note that this behaviour might change in the future.) This is independent of whether ids are unique or not (even without line /multipolygon splitting).

It is a bit complicated but you could probably use the ON DELETE trigger to copy the data about to be deleted to an extra table and then use the ON INSERT trigger to do the comparison with the old data. I can't think of any other way to do what you want. The Lua script can't see the old version of the data, so it can't do any comparison.

The developers are aware that the expire functionality isn't as good as it could be and making expiry better and more flexible is on the todo list, but it could take a while to get that done. I suggest you start a discussion, though so that your use case is considered when working on this.

permanent link

answered 15 Jun '21, 16:24

Jochen%20Topf's gravatar image

Jochen Topf
accept rate: 31%

As a variant of this you can also just not do the DELETE (ie. the trigger can signal the database it should not do it, I believe by returning NULL from the BEFORE DELETE trigger function). Then do the DELETE yourself in the BEFORE INSERT function after doing the comparison with the original data.

(16 Jun '21, 08:18) Jochen Topf

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Jun '21, 13:33

question was seen: 1,068 times

last updated: 16 Jun '21, 08:18

NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum