Problem with rendering PostgreSQL 9.0 beta ST_AsBinary

[robe]

I'm not sure if this is my install or something wrong with OpenJump? or the ST_AsBinary output emitted by PostgreSQL 9.0. I just tried displaying my PostgreSQL 9.0 beta 1 PostGIS 1.5.2SVN install on Windows in OpenJump?, and I can't.

It gives the error (Unknown WKB type 48(Parse Exception) when I run this query

SELECT ST_AsBinary(geom)
FROM
bay_area_cities

It fails for any geometry table in my PostgreSQL 9.0. However I can render the geometries fine in QuantumGIS.

I can also render my PostgreSQL 8.4 PostGIS 1.5 geometries fine as well in OpenJump?.

I'm not sure if this is just a windows thing or something strange that the PostgreSQL 9.0 is tacking on to the ST_AsBinary output that is tripping up OpenJump?.

Can someone running a Linux PostgreSQL 9.0 beta 1 install test it out.

As far as I can tell, the geometries are fine. I can union them, run areas etc. So seems like it might be isolated to the output.

Details of my install (that I built around 2010-05-11) -- the one on Windows Experimental page.

"POSTGIS="1.5.2SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.6.1, 21 August 2008" LIBXML="2.7.6" USE_STATS"

[mcayland]

Hmmm I wonder if this is a result of changing the default escaping from bytea to hex in PostgreSQL 9.0? For more details see:

 http://developer.postgresql.org/pgdocs/postgres/datatype-binary.html  http://developer.postgresql.org/pgdocs/postgres/runtime-config-client.html#GUC-BYTEA-OUTPUT

Does changing the bytea_output GUC back to escape fix this?

Mark.

[robe]

Mark,

You are a genius. Yes that solves it. Though you think its too much to expect users to do this. I imagine there are a lot of apps relying on this ST_AsBinary output being the old way and to force users to change the new default behavior sounds a bit unappealing.

[robe]

It just occurred to me that for PostGIS 1.5+, our minimum version of supported PostgreSQL is 8.3. Wasn't 8.3 the version where they introduced function level GUCs?

So we could tack on this GUC change on ST_AsBinary function so people don't need to make the change global. I can't think of why we would ever want ST_AsBinary to be hex encoded as that would just make it the same as our regular PostGIS output wouldn't it?

[robe]

Damn just tried making it a function level GUC and while it lets me do it, it doesn't work. Seems I have to make it a database level GUC for it to work. Extremely annoying.

[mcayland]

Well, it's useful to know. I'm not convinced that this is our bug to fix though - if OpenJump? are using their own parser to read escaped WKB, then it is their responsibility to update it to support the new format. Similarly, if the native PostgreSQL JDBC driver ( http://jdbc.postgresql.org) hasn't been updated to support the new format, then it is the PGDG's bug.

Does OpenJump? come with it's own PostgreSQL JDBC JAR? If so, you may find simply switching it for a JAR build built from the latest JDBC driver HEAD will fix the issue.

Mark.

[robe]

I tested the newest jbdbc3 driver I found on that link and doesn't work.

You think its our postgis.jar file at fault. I'm using a recent one of that too I believe. Though I suppose it could be reading from elsewhere.

Do we have any java programmers out there listening (Paul, Kevin, Martin -- I'm looking at you virtually speaking of course :)) -- perhaps one of them could test the whole mix against a PostgreSQL 9.0 to see if its an epidemic problem.

[robe]

I tested with jdbc4 too and no go. Though evidentally openjump doesn't seem to use postgis.jar for hte ad hoc query part anyrate since I removed the postgis.jar and it didn't seem to care.

[robe]

I'm not sure this is fixable and we do have a workaround so not urgent.

[pracine]

So is it an OpenJump? only issue? Should we fill a ticket in their bug system?

[robe]

Pierre,

I suspect its an issue with a lot of software. Basically any that was relying on the display output of ST_AsBinary. Safe FME already pointed it out on their end as I recall but I think they have it on their todo to fix.

It wouldn't hurt to put it in as a ticket item in OpenJump?.

[michaudm]

Hi,

So, the bytea output format from PostgreSQL has changed. My guess is that java code concerned by this change is ResultSet?.getBytes(columnIndex), which, I think, is the jdbc driver job.

From the following page, it appears the problem has been fixed in last postgresql drivers.  http://jdbc.postgresql.org/changes.html#version_9.0-dev800 Could anyone with Postgresql 9.0 installed check if this new driver + openjump fix the problem.

Thanks for your bug report on OpenJUMP bug tracker

Michaël, OpenJUMP contributor

[robe]

Michael,

Just tested comparing with the 8.4 jdbc driver and the new 9.0 jdbc driver against my PostgreSQL 9.0 install. I confirmed it doesn't work with the 8.4 jdbc but does with the 9.0.

Thanks for clearing this up. I'll update the docs in a bit to reflect the issue is a jdbc driver one and not PostGIS or OpenJump? etc.

Regina