Hi,

I'm using PostgreSQL 8.1.0 in conjunction with hibernate-3.2.0.GA and hibernate-annotations-3.2.0.GA. I have a number of POJO properties of (Java) type Boolean. The corresponding database fields are of type bool. The Hibernate generated SQL contains 0 for false and 1 for true. PostgreSQL expects true for true and false for false.

I have tried changing the database types to bit and to int2. But, this doesn't work with booleans. I recognize that bool is not a standard SQL type. I'm just trying to find a workaround.

Any suggestions?

Thanks!

Cheers,

Greg[/code]

I have just tried it with Postgres 8.1.4 with the 3.2.0GA version EntitiyManager, Core, and Annotation, and in pgadmin is shows up as true and false.

After just quickly glancing over the postgres documentation 0 and 1 are valid literal values for it, if enclosed in single quotations. (http://www.postgresql.org/docs/8.1/interactive/datatype-boolean.html, note bool is an alias for the boolean datatype)

Cheers,

Andy

Hmm. Just upgraded to Spring 2.0 (I am using Spring). Now, it works on insert, but not update:

Insert:

com.ergonosis.model.Mailing@7dac02[id=550,sendDate=Thu Oct 26 08:51:18 PDT 2006,customMessage=bmbnmbvnmb,sendToSubscribers=true,sendToContacts=true,sendHomePage=true]

Update:

08:53:15.593 WARN!! [SocketListener0-0] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:71) >77> SQL Error: 0, SQLState: null
08:53:15.593 ERROR! [SocketListener0-0] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72) >77> Batch entry 0 update mailing set job_id=mailing-1161877878328, custom_message=Now is the time for all good men to come to the aid of the party. , send_homepage=1, send_to_contacts=1, send_to_subscribers=1, send_date=2006-09-26 08:51:00.000000 -0700 where id=550 was aborted. Call getNextException to see the cause.
08:53:15.593 WARN!! [SocketListener0-0] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:71) >77> SQL Error: 0, SQLState: 25006
08:53:15.609 ERROR! [SocketListener0-0] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72) >77> ERROR: transaction is read-only
08:53:15.625 ERROR! [SocketListener0-0] org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:301) >72> Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:558)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:540)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:510)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:310)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
at $Proxy23.scheduleMailing(Unknown Source)
at com.ergonosis.web.action.MailingController.onSubmit(MailingController.java:155)
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:258)
at com.ergonosis.web.action.BaseFormController.processFormSubmission(BaseFormController.java:149)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:250)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:806)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:736)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:616)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:830)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:323)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at com.ergonosis.web.filter.GZIPFilter.doFilter(GZIPFilter.java:50)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:78)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at com.opensymphony.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:42)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at com.ergonosis.web.filter.ActionFilter.doFilter(ActionFilter.java:125)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at com.ergonosis.web.filter.RememberMeFilter.doFilter(RememberMeFilter.java:126)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:292)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:79)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:143)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:138)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:50)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:220)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:303)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:173)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:120)
at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:471)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:633)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
at org.mortbay.http.HttpServer.service(HttpServer.java:909)
at org.mortbay.http.HttpConnection.service(HttpConnection.java:816)
at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:982)
at org.mortbay.http.HttpConnection.handle(HttpConnection.java:833)
at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357)
at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)
Caused by: java.sql.BatchUpdateException: Batch entry 0 update mailing set job_id=mailing-1161877878328, custom_message=Now is the time for all good men to come to the aid of the party. , send_homepage=1, send_to_contacts=1, send_to_subscribers=1, send_date=2006-09-26 08:51:00.000000 -0700 where id=550 was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2497)
at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:399)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1298)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:347)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2559)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 74 more

Looking at my own stack trace again, I noticed "ERROR: transaction is read-only". Could this be the actual source of the problem? I googled for this phrase, but didn't find much.

Thanks!

Greg

It might be that Postgres does not allow batched operation when there is no explicit transaction demarcation.
It is a good (essential) practice to use transaction.