Thread: Hibernate and Postgres 8.3/8.4

I've found that Hibernate breaks very easily in 8.3/8.4 as the server. This is Hibernate 3.3.0 (I didn't see any diffs
in3.3.1 in the changelog) Here's a sample: 

2009-08-14 15:55:37,515 [Store_1250286937500] ERROR com.gwava.utils.ErrorHandle - reportError: StoreEmail ::
com.gwava.archive.StoreEmail.handleArchiveExceptions:426:: EXCEPTION : org.hibernate.exception.SQLGrammarException:
couldnot execute query org.hibernate.exception.SQLGrammarException: could not execute query 
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2216)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
    at com.gwava.hibernate.DBEngine.getEmailFromDB(DBEngine.java:164)
    at com.gwava.hibernate.DBEngine.insertEmail(DBEngine.java:256)
    at com.gwava.engine.AbstractDBEngine.archiveDB(AbstractDBEngine.java:467)
    at com.gwava.engine.AbstractDBEngine.archive(AbstractDBEngine.java:245)
    at com.gwava.engine.AbstractDBEngine.dbStore(AbstractDBEngine.java:810)
    at com.gwava.engine.standard.RetainDBHybridEngine.archiveEmail(RetainDBHybridEngine.java:75)
    at com.gwava.archive.StoreEmail.handlePUT(StoreEmail.java:264)
    at org.apache.jsp.Server.index_jsp._jspService(Unknown Source)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at com.gwava.Filters.MainFilter.doFilter(MainFilter.java:234)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
    at org.apache.coyote.ajp.AjpAprProcessor.process(AjpAprProcessor.java:444)
    at org.apache.coyote.ajp.AjpAprProtocol$AjpConnectionHandler.process(AjpAprProtocol.java:472)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1286)
    at java.lang.Thread.run(Thread.java:595)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 628
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2213)
    ... 33 more

Now, googling around I figured out it was the dropping of implicit casts. Readding these fixes that, but there are
obviousreasons the PG devs dropped these. 

Question 1:  Is this a well known issue? If so, why is there no JIRA opened on Hibernate's site about it. (I'd open one
ifI get consensus that it makes sense to do so, but since my PG knowledge is weak... 
Question 2: Anything that can be done BESIDES adding these implicit casts? I looked at the Dialect, and couldn't find
anythingobvious. 

The queries in question are all "correct" in that they use query.setXXX, where XXX is a "reasonable" thing like String,
Integer,Long.....nothing fancy... 

Michael Bell wrote:

> Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying
>   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
>   Position: 628

> The queries in question are all "correct" in that they use query.setXXX, where XXX is a "reasonable" thing like
String,Integer, Long.....nothing fancy... 

Well, from the exception, either Hibernate is doing setString() on a
parameter that is compared to a smallint, or it's doing setShort() on a
parameter that is compared to a varchar type.. so one of the two sides
is wrong.

-O

Aw jeez, ya nailed it. There was indeed a setString where there should be setShort. Dunno where my head was.

Thanks.



----- Original Message ----
From: Oliver Jowett <oliver@opencloud.com>
To: Michael Bell <mikebell90@yahoo.com>
Cc: pgsql-jdbc@postgresql.org
Sent: Saturday, August 22, 2009 12:23:11 AM
Subject: Re: [JDBC] Hibernate and Postgres 8.3/8.4

Michael Bell wrote:

> Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying
>   Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
>   Position: 628

> The queries in question are all "correct" in that they use query.setXXX, where XXX is a "reasonable" thing like
String,Integer, Long.....nothing fancy... 

Well, from the exception, either Hibernate is doing setString() on a
parameter that is compared to a smallint, or it's doing setShort() on a
parameter that is compared to a varchar type.. so one of the two sides
is wrong.

-O

Oliver Jowett <oliver@opencloud.com> writes:
> Michael Bell wrote:
>> Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying
>> Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
>> Position: 628

>> The queries in question are all "correct" in that they use query.setXXX, where XXX is a "reasonable" thing like
String,Integer, Long.....nothing fancy... 

> Well, from the exception, either Hibernate is doing setString() on a
> parameter that is compared to a smallint, or it's doing setShort() on a
> parameter that is compared to a varchar type.. so one of the two sides
> is wrong.

Yeah.  This looks to me like Hibernate contains instances of the exact
sort of bug --- not trivial issue, real bug --- that we removed the
implicit casts to help people catch.  You should definitely file a bug
report with them.

            regards, tom lane