Postgres DB SQL calls resulting in inconsistent results

Question

Hello OSM,

As mentioned here, I have an extract of a recent planet file created with Osmosis (with the command line arguement that keeps ways even if they extend outside the cut polygon). I dumped the extract into a Postgres database using the snapshot schema. I then have a Java program that calls the following two SQL statements in two nested loops to draw the data.

OUTER LOOP BEGIN
     "SELECT nodes, id from public.ways LIMIT 1 OFFSET" + val
     INNER LOOP BEGIN
          "SELECT ST_X(geom) as x, ST_Y(geom) as y from public.nodes WHERE id=" + nodes[i] + "LIMIT 1"
     INNER LOOP END
     val++
OUTER LOOP END

When I run this, if a node belonging to a given way is not found, my program fires off a message to the log and keeps chugging. I have noticed that these logs are showing up in a VERY inconsistent fashion. To demonstrate this, I took the outer loop of my program and clamped it to only loop 1000 times (IE draw 1000 ways) and ran the program twice, which should yield the same results. This is what I got...

alt text

Now please understand that I already know that:

A. Planet files dumps take hours so SOME nodes will be missing unless you grab a daily build and use Osmosis to update your planet file...

B. My extract included ways that crossed my cut polygon, for my needs I require the entire road system to be intact.

But this is different, this is one isolated database and one program asking it for the same exact data, and I'm getting inconsistencies between runs which is driving me mad! I don't understand it at all. Do I need to order this data by the id in order to get consistent results? Is this a Postgres "thing" or should I be looking for some kind of illusive bug in my program (which is pretty strait-forward)? Any help would be vastly appreciated.

Thanks, -Cody

Answer 1

I figured it out, for anyone who stumbles upon this in the future I'm going to break down the problem.

I made a dumb noob mistake by writing a function that executed my SQL statement for me and returned a ResultSet which I then operated on in my main loop. Problem is this little line found in the JavaDocs on ResultSet...

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

With that alone the problem becomes obvious, my function is closing - leaving the SQL statement object unreferenced and thus a target for garbage collection. Most of the time the garbage collection wasn't getting to the statement object in time to cause problems, but sometimes it was able to which resulted in my rs.getDouble statements throwing seemingly random SQL Exceptions, which registered in my program as a missing point (because with OSM we have to assume that some nodes are in fact missing).

Case closed, hope this helps someone out there eventually... -Cody

Postgres DB SQL calls resulting in inconsistent results

Follow this question

Related questions