PostgreSql AdminIndex Bug

[Migrated From Jforum.net]

Hi,

An error has occurred in admin index
java.sql.SQLException: ERROR: operator does not exist: timestamp without time zone integer
is this bug ?
[originally posted on jforum.net by omidp]

[Migrated From Jforum.net]

Most likely either a problem with a column definition (DB servers like PostGres and MySQL are morphing their proprietary time/date columns into SQL standard ones as new versions come out...) or in the related query.

It might also be related to using a new DB with an old JDBC driver (or vice versa).

Where is this happening, what does the log file say, what is your environment?
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

monroe wrote:

It might also be related to using a new DB with an old JDBC driver (or vice versa).

Where is this happening, what does the log file say, what is your environment?

Where is log file?
My environment is : Fedora 9
JDK1.5.16
DB : Postgresql 1.8.4
[originally posted on jforum.net by omidp]

[Migrated From Jforum.net]

HTH. I have the same error too.

PostgreSQL 8.3.6.
Tomcat 5.0.28.
postgresql-8.3-603.jdbc3.jar (in common/lib).

22:59:51,135 ERROR [ExceptionWriter     ] net.jforum.exceptions.ForumException: java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at net.jforum.Command.process(Command.java:114)
	at net.jforum.view.admin.AdminAction.process(AdminAction.java:251)
	at net.jforum.JForum.processCommand(JForum.java:217)
	at net.jforum.JForum.service(JForum.java:200)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
	at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
	at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
	at java.lang.Thread.run(Thread.java:534)
Caused by: java.lang.reflect.InvocationTargetException
	... 38 more
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: timestamp without time zone > integer
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:314)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:221)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
	at net.jforum.dao.generic.GenericForumDAO.getBoardStatus(GenericForumDAO.java:748)
	at net.jforum.view.admin.AdminAction.main(AdminAction.java:124)
	... 38 more
Caused by: java.sql.SQLException: ERROR: operator does not exist: timestamp without time zone > integer
	... 47 more

URL is: /jforum/jforum.page?module=admBase&action=main

[originally posted on jforum.net by javafueled]

[Migrated From Jforum.net]

Possibly related.

Same environment as earlier post. Happens viewing forum post.

Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1]
The problematic instruction:
----------
==> assignment: moderators=forum.getModeratorList() [on line 311, column 41 in default/forum_show.htm]
----------

Java backtrace for programmers:
----------
freemarker.template.TemplateModelException: Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1]
	at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:136)
	at freemarker.core.MethodCall._getAsTemplateModel(MethodCall.java:93)
	at freemarker.core.Expression.getAsTemplateModel(Expression.java:89)
	at freemarker.core.Assignment.accept(Assignment.java:90)
	at freemarker.core.Environment.visit(Environment.java:196)
	at freemarker.core.MixedContent.accept(MixedContent.java:92)
	at freemarker.core.Environment.visit(Environment.java:196)
	at freemarker.core.Environment.process(Environment.java:176)
	at freemarker.template.Template.process(Template.java:232)
	at net.jforum.JForum.processCommand(JForum.java:233)
	at net.jforum.JForum.service(JForum.java:200)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
	at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
	at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
	at java.lang.Thread.run(Thread.java:534)
Caused by: net.jforum.exceptions.DatabaseException: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at net.jforum.dao.generic.GenericForumDAO.getModeratorList(GenericForumDAO.java:498)
	at net.jforum.repository.ForumRepository.getModeratorList(ForumRepository.java:596)
	at net.jforum.entities.Forum.getModeratorList(Forum.java:101)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at freemarker.ext.beans.BeansWrapper.invokeMethod(BeansWrapper.java:825)
	at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:113)
	... 40 more
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	... 56 more
Caused by: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	... 56 more
<!-- BEGIN ERROR STACK TRACE

Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1]
The problematic instruction:
----------
== assignment: moderators=forum.getModeratorList() [on line 311, column 41 in default/forum_show.htm]
----------

Java backtrace for programmers:
----------
freemarker.template.TemplateModelException: Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1]
	at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:136)
	at freemarker.core.MethodCall._getAsTemplateModel(MethodCall.java:93)
	at freemarker.core.Expression.getAsTemplateModel(Expression.java:89)
	at freemarker.core.Assignment.accept(Assignment.java:90)
	at freemarker.core.Environment.visit(Environment.java:196)
	at freemarker.core.MixedContent.accept(MixedContent.java:92)
	at freemarker.core.Environment.visit(Environment.java:196)
	at freemarker.core.Environment.process(Environment.java:176)
	at freemarker.template.Template.process(Template.java:232)
	at net.jforum.JForum.processCommand(JForum.java:233)
	at net.jforum.JForum.service(JForum.java:200)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
	at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)
	at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
	at java.lang.Thread.run(Thread.java:534)
Caused by: net.jforum.exceptions.DatabaseException: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at net.jforum.dao.generic.GenericForumDAO.getModeratorList(GenericForumDAO.java:498)
	at net.jforum.repository.ForumRepository.getModeratorList(ForumRepository.java:596)
	at net.jforum.entities.Forum.getModeratorList(Forum.java:101)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at freemarker.ext.beans.BeansWrapper.invokeMethod(BeansWrapper.java:825)
	at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:113)
	... 40 more
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	... 56 more
Caused by: java.sql.SQLException: ERROR: operator does not exist: character varying = integer
	... 56 more

URL is: /jforum/forums/show/1.page?null
END ERROR STACK TRACE-->

[originally posted on jforum.net by javafueled]

[Migrated From Jforum.net]

Any patches, send them my way. I will be happy to test.

Regards,
Tim
[originally posted on jforum.net by javafueled]

[Migrated From Jforum.net]

The solution to this is explained here http://archives.postgresql.org//pgsql-bugs/2008-04/msg00092.php

I'm going to download the source. Possibly even submit a patch. JIRA is down, so ... watch for it here.

[originally posted on jforum.net by javafueled]

[Migrated From Jforum.net]

The solution is explained here: http://californiaccw.org/posts/list/5016.page

Requires a patch to a SQL file and rebundling of the war file to implement.
[originally posted on jforum.net by javafueled]