[OSM-dev] Minute Diffs Broken

Brett Henderson brett at bretth.com
Tue May 5 03:02:04 BST 2009

Steve Singer wrote:
> 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.
What would this look like?  Does this row link to the modified data in 
some way?
> 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.
That does look interesting.  I'd hope to use that outside the main 
database though.  My thoughts were to use triggers to populate short 
term flag tables which a single threaded process would read, use as keys 
to select modified data into an offline database, then clear.  This 
offline database could then use a queueing system such as PgQ (I haven't 
seen it before, will have to check it out) to send events to the various 
consumers of the data.  I'd like to minimise access to the central 
database if possible because 1. it will scale better, and 2. it adds 
less burden to existing DBAs.
> 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)
I'd be interested to hear the answer to this one.  I had a quick look at 
the problematic changesets last night and it looked like all data had 
the same timestamp but I could have been wrong.


More information about the dev mailing list