Wednesday, June 28, 2017

Postgres 9.5 to 9.6 JSON generation errors

I experienced an odd error at a client when I was trying to duplicate their database to a local Dockerized Postgres instance. I used clkao/postgres-plv8 as my baseline since the client had JS in their PLPGSQL. They have a complex series of queries and views that produce JSON output, and we're converting that to be done in Java instead.

The problems came when I ran the view that generated the JSON on my machine versus on their dev environment, from which I had taken a dump minutes before. I got completely different answers for the JSON, not just object ordering but objects in the wrong places. I downgraded to clkao/postgres-plv8:9.5-2.0 to be closer to the 9.5.2 version of Postgres the client was running and the problem went away.

I don't know what caused the difference in JSON output between Postgres 9.5 and 9.6 running the same views and procedures, but it's something to watch out for.