Errors When Attempting to Use PostGis Functions in Jooq

38 views
Skip to first unread message

ad...@joinsharespace.com

unread,
May 11, 2018, 6:32:03 PM5/11/18
to jOOQ User Group
I've been trying to use postgis with jooq and have run into a problem when attempting to use postgis functions. I've defined a converter and binder for the "geometry" data type and it appears to work fine in queries that don't use postgis functions. 

For example, for the following table:
CREATE TABLE example (
 id BIGSERIAL PRIMARY KEY
,
 location GEOGRAPHY
(POINT, 4326) NOT NULL
);

queries like these work fine: (dbSessionManager is just a thin wrapper around DSLContext)

fun createExample(location: Location): ExampleRecord {
 
return dbSessionManager.session()
 
.insertInto(EXAMPLE)
 
.set(EXAMPLE.LOCATION, location.convertToPoint())
 
.returning()
 
.fetchOne()
}

or

fun getExampleByLocation(location: Location): ExampleRecord? {
 
return dbSessionManager.session()
 
.selectFrom(EXAMPLE)
 
.where(EXAMPLE.LOCATION.eq(location.convertToPoint()))
 
.limit(1)
 
.fetchOne()
}


But this query 
fun getExampleInRadius(location: Location, radius: Double):ExampleRecord? {
 val routine
= StDwithin3()
 routine
.set__1(EXAMPLE.LOCATION)
 routine
.set__2(location.convertToPoint())
 routine
.set__3(radius)
 
return dbSessionManager.session()
 
.selectFrom(EXAMPLE)
 
.where(routine.asField())
 
.limit(1)
 
.fetchOne()
}
fails with the following error: 

org.jooq.exception.DataAccessException: SQL [select "public"."example"."id", "public"."example"."location" from "public"."example" where "postgis"."st_dwithin"(cast("public"."example"."location" as USER-DEFINED), cast(?::geography as USER-DEFINED), cast(? as double precision)) limit ?]; ERROR: syntax error at or near "USER"


because for some reason jooq is trying to cast the location to "USER-DEFINED" instead of geography. It appears that `AbstractRoutine.pgArgNeedsCasting` forces all parameters of overloaded functions to be cast, but I'm not sure why its being cast to "USER-DEFINED" instead of "geography". Maybe I've set up the converter/binder wrong, but its very close to what was recommended here: https://groups.google.com/forum/#!topic/jooq-user/TBQZCPTCvnk

In the interest of making this easier to debug, I've put together a very stripped down version of my codebase that still exhibits this behavior. If you feel like taking a look, it is here: https://github.com/amc6/jooq_postgis_bug_example

The key files are:
Any insight would be greatly appreciated!

Best,
Adam

Lukas Eder

unread,
Jun 6, 2018, 5:32:59 AM6/6/18
to jooq...@googlegroups.com
Thank you very much for your report and for your patience.

I finally got around to reproducing this issue. When unknown data types are encountered, jOOQ generates (in PostgreSQL) the type "USER-DEFINED", which is the reported type from PostgreSQL's INFORMATION_SCHEMA.PARAMETERS.DATA_TYPE. This name is useless, we should generate the content from PARAMETERS.UDT_SCHEMA and PARAMETERS.UDT_NAME, instead, if available. This will improve the behaviour in the absence of a custom binding *and* in the presence of such a binding.

I have fixed this for jOOQ 3.11 through: https://github.com/jOOQ/jOOQ/issues/7547

Thanks again for your report.
Lukas



--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all
Reply to author
Forward
0 new messages