[OSM-dev] Minute Diffs Broken
ssinger_pg at sympatico.ca
Tue May 5 02:30:30 BST 2009
On Tue, 5 May 2009, Brett Henderson wrote:
> The way osmosis identifies changed records is by query the history table
> for entities with a timestamp within a time interval. The time interval
> will be an hour long for hourly diffs, a minute long for minute diffs.
> For example, the node query is:
> SELECT e.id, e.version, e.timestamp, e.visible, u.data_public,
> u.id AS user_id, u.display_name, e.changeset_id, e.latitude, e.longitude
> FROM nodes e
> LEFT OUTER JOIN changesets c ON e.changeset_id = c.id LEFT OUTER JOIN
> users u ON c.user_id = u.id
> WHERE e.timestamp > ? AND e.timestamp <= ? ORDER BY e.id, e.version
> If the history table records don't exist (or aren't committed) when this
> query runs, the records won't be put into the diff file.
What you want is the timestamp that the change was committed at, not the
timestamp it was inserted at. However there is no way to get this with
The options that come to mind are
A) Modify the rails code to insert a row in a transaction table with a
timestamp just before issuing the 'COMMIT'. Then hope the timespan between
that and the commit finishing is less than your update window.
B) Setup some sort of queuing system that will get committed transactions in
a proper order. You might want to look at
PgQ (http://skytools.projects.postgresql.org/doc/pgq-sql.html) along with
triggers on the node/way/relation.... tables.
Most of the user-level async replication options for postgresql share some
core ideas. They tend to have triggers inserting into a journaling table
then use snapshots to get a consistent set of events that can be replayed.
I'm not familiar with the rails API code, but I want to make sure that the
nodes.timestamp column your querying isn't being populated with the
postgresql now() function but instead with some time that rails computes.
(The now() function returns the time when your transaction started not the
current time, this would make the skipped data problem more common)
> dev mailing list
> dev at openstreetmap.org
More information about the dev