[OSM-dev] Minute Diffs Broken

[Steve Singer]

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 
postgresql.

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)

Steve


>
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
>