Thread: Upgrade to Scientific Linux 6.5 x86_64 breaks query

Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
CG
Date:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What
workedfine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with
sameissue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back
end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query
runsfine on other platforms. 

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c,
maxRows=0,fetchSize=0, flags=1 
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,
last(longitude)as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as
direction,   last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,
  speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between
now()- $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5
  ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,
$12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18
   ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,
     $25       ,     
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,
$32      ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38
  ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,
    $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by
trans_dateasc       ) foo   group by    t_uuid
",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1
hours0 mins 0.00 

secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-891e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44

99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>
,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
Dave Cramer
Date:
The crux of the problem is this:  ERROR: operator does not exist: timestamp with time zone >= interval

which appears to be caused by trans_date between now() - $2 and now()

What happens if you change it to :

trans_date between (now() - $2) and now()



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 12:10, CG <cgg007@yahoo.com> wrote:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What worked fine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with same issue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query runs fine on other platforms.

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c, maxRows=0, fetchSize=0, flags=1
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as direction,    last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,     speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between now() - $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5       ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,        $12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18       ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,        $25       ,
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,        $32       ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38       ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,        $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by     trans_date asc       ) foo   group by    t_uuid  ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
 secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-891e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44
99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>
,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
David G Johnston
Date:
I concur, and too am curious as to the "(now() - $2)" result, but even if
that clears up the symptom it doesn't make any sense...

David J.


Dave Cramer-8 wrote
> The crux of the problem is this:  ERROR: operator does not exist:
> timestamp
> with time zone >= interval
>
> which appears to be caused by trans_date between now() - $2 and now()
>
> What happens if you change it to :
>
> trans_date between (now() - $2) and now()
>
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On 23 July 2014 12:10, CG <

> cgg007@

> > wrote:
>
>> Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer
>> from the same issue); MyBatis 2.3.5
>>
>> We're migrating our app to a virtualized 64bit Linux server from a
>> virtualized 32bit Linux server, same farm. What worked fine on 32bit
>> Scientific Linux breaks on 64bit... Same JVM version (although we tried
>> 64
>> and 32 bit JVM both with same issue). We've done our best to make sure
>> the
>> ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...
>>
>> We've been sifting through everything and we can't conceive of what could
>> possibly cause this problem. Also, this query runs fine on other
>> platforms.
>>
>> Maybe you can see something I can't:
>>
>> 10:02:40.596 (2)  <=BE ReadyForQuery(I)
>> 10:02:40.601 (2) simple execute,
>> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c,
>> maxRows=0, fetchSize=0, flags=1
>> 10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
>> 10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
>> 10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as
>> t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,
>>  last(trans_date) as trans_date,    last(speed) as speed,
>>  last(direction) as direction,    last(revision) as revision   from (
>> SELECT     trans_date,     t_uuid,     latitude,     longitude,
>> speed,
>>     direction,     revision    FROM     t_vectors    WHERE     group_uuid
>> =
>> $1     and trans_date between now() - $2 and now()
>> AND       t_uuid in        (        $3       ,        $4       ,
>> $5
>>       ,        $6       ,        $7       ,        $8       ,        $9
>>   ,        $10       ,        $11       ,        $12       ,        $13
>>   ,        $14       ,        $15       ,        $16       ,        $17
>>   ,        $18       ,        $19       ,        $20       ,        $21
>>   ,        $22       ,        $23       ,        $24       ,        $25
>>   ,
>>     $26       ,        $27       ,        $28       ,        $29       ,
>>      $30       ,        $31       ,        $32       ,        $33       ,
>>      $34       ,        $35       ,        $36       ,        $37       ,
>>      $38       ,        $39       ,        $40       ,        $41       ,
>>      $42       ,        $43       ,        $44       ,        $45       ,
>>      $46       ,        $47       ,        $48       ,        $49       )
>>             order by     trans_date asc       ) foo   group by    t_uuid
>>
>> ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
>> 10:02:40.601 (2)  FE=>
>> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0
>> years 0 mons 0 days 1 hours 0 mins 0.00
>>
>>  secs>,$3=
> <cdd1aa29-48ae-4a8d-884f-43ea795defde>
> ,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=
> <fa21ec58-47e7-478a-b8b0-42a937200e70>
> ,$7=
> <ec909e6b-c214-4fbe-aea0-9baf4a33b54a>
>
,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=
> <dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>
> ,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=
> <fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>
> ,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=
> <f526b7ee-7823-4e4b-8bd5-dca091ca6aea>
>
,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=
> <da94401d-10c5-4607-9b09-fa2202bba846>
> ,$21=
> <ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>
> ,$22=<3448505e-b740-4084-891e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=
> <a3260ac3-3811-4857-95a6-2cbcd1f8119b>
> ,$25=<12a70091-0853-44
>>
>> 99-8f05-d80e3045ce5e>,$26=
> <e21a63e8-2835-4942-9a9e-18e1310a74e6>
>
,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=
> <f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>
>
,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=
> <a5b56133-7cd2-476d-bde6-1b8af68bc265>
>
,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=
> <f588610a-507a-4c4c-93bb-a6466ed140ba>
> ,$42=
> <d5a145c8-f8bd-486f-a0d7-f897715915c5>
> ,$43=
> <bd0ed505-27e8-43b4-be4e-664adc98a38c>
> ,$44=
> <b0948960-11b7-4bca-9d9c-4f8d7b91962c>
>
,$45=<70d2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>
>>
>> ,$48=
> <de7259b4-e1c8-481c-9043-46f09a0926de>
> ,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
>> 10:02:40.602 (2)  FE=> Describe(portal=null)
>> 10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
>> 10:02:40.602 (2)  FE=> Sync
>> 10:02:40.604 (2)  <=BE BindComplete [null]
>> 10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
>> 10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist:
>> timestamp with time zone >= interval
>>   Hint: No operator matches the given name and argument type(s). You
>> might
>> need to add explicit type casts.
>>   Position: 410)
>> org.postgresql.util.PSQLException: ERROR: operator does not exist:
>> timestamp with time zone >= interval
>>   Hint: No operator matches the given name and argument type(s). You
>> might
>> need to add explicit type casts.
>>   Position: 410
>>         at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
>>         at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
>>         at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
>>         at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
>>         at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
>>         at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
>>         at
>> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>>         at
>> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>>         at
>> com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
>>         at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
>>         at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
>>         at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
>>         at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
>>         at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
>>         at
>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
>>         at
>> com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
>>         at
>> com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
>>         at com.foo.util.Util.fetchSettings(Util.java:109)
>>         at com.foo.util.Util.fetchUpdates(Util.java:221)
>>         at com.foo.util.Util.fetchInit(Util.java:168)
>>         at
>> com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
>>         at
>> com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
>>         at
>> com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
>>         at
>> com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
>>         at
>> com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
>>         at
>> com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
>>         at
>> com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
>>         at
>> com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
>>         at com.restservice.RestService.process(RestService.java:224)
>>         at
>> com.restservice.RestService.processWrapper(RestService.java:122)
>>         at com.restservice.RestService.doPost(RestService.java:101)
>>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
>>         at
>> org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
>>         at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
>>         at
>> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
>>         at
>> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
>>         at
>> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
>>         at
>> org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
>>         at
>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
>>         at
>> com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
>>         at
>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
>>         at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>         at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>         at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
>>         at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
>>         at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
>>         at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>         at
>> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
>>         at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
>>         at
>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>         at java.lang.Thread.run(Thread.java:662)
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (

> pgsql-jdbc@

> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-to-Scientific-Linux-6-5-x86-64-breaks-query-tp5812546p5812571.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
Tom Lane
Date:
Dave Cramer <pg@fastcrypt.com> writes:
> The crux of the problem is this:  ERROR: operator does not exist: timestamp
> with time zone >= interval
> which appears to be caused by trans_date between now() - $2 and now()

> What happens if you change it to :
> trans_date between (now() - $2) and now()

That shouldn't change the results...

I think the real issue here is what is the datatype of parameter $2.
It's apparently intended to be an interval (so that now() - interval
is a timestamp, and the comparison is meaningful.)  But the new system
is evidently taking $2 as a timestamp: the timestamp minus timestamp
operator yields an interval (which is unsurprising if you think about
what it means), and that leads to the reported error when the parser
tries to figure out what the BETWEEN means.

I would bet that in the old system $2 was explicitly marked as an interval
value, but in the new system $2 isn't getting marked with any particular
datatype.  Given "timestamp minus unknown", the parser will resolve
the unknown type as timestamp, the same as is on the other side of the
operator.  (Obviously, that heuristic doesn't work all the time, but
it's right much of the time.)

So I think this is a client-side behavioral change, and what you need to
look for is how the type of that parameter is getting set, or not.

            regards, tom lane


Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
David G Johnston
Date:
Tom Lane-2 wrote
> Dave Cramer <

> pg@

> > writes:
>> The crux of the problem is this:  ERROR: operator does not exist:
>> timestamp
>> with time zone >= interval
>> which appears to be caused by trans_date between now() - $2 and now()
>
>> What happens if you change it to :
>> trans_date between (now() - $2) and now()
>
> That shouldn't change the results...
>
> I think the real issue here is what is the datatype of parameter $2.
> It's apparently intended to be an interval (so that now() - interval
> is a timestamp, and the comparison is meaningful.)  But the new system
> is evidently taking $2 as a timestamp: the timestamp minus timestamp
> operator yields an interval (which is unsurprising if you think about
> what it means), and that leads to the reported error when the parser
> tries to figure out what the BETWEEN means.
>
> I would bet that in the old system $2 was explicitly marked as an interval
> value, but in the new system $2 isn't getting marked with any particular
> datatype.  Given "timestamp minus unknown", the parser will resolve
> the unknown type as timestamp, the same as is on the other side of the
> operator.  (Obviously, that heuristic doesn't work all the time, but
> it's right much of the time.)
>
> So I think this is a client-side behavioral change, and what you need to
> look for is how the type of that parameter is getting set, or not.

Agreed though the detail provided shows:

$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00

which leads me to believe the server is indeed getting an interval from the
client...

The OP should also try explicitly casting the parameter symbol in the
original query:

trans_date between now() - ?::interval and now()

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-to-Scientific-Linux-6-5-x86-64-breaks-query-tp5812546p5812574.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
CG
Date:

I can change the SQL statement around to get it to run. 

The parenthesis don't work, but if I explicitly cast that second parameter to interval "CAST( $2 AS interval)" I can force it to work. That's not ideal.

I don't think it is the lack of an operator. Timestamptz should not be comparable directly to an interval, right? That's like saying "2 + 2 >= Orange" or "today is greater than a minute". I think something is eating that first "now() -" so that what is being prepared looks like "trans_date between $2 and now()".

The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit linux? Based on that log, it looks like client-side prepare is falling down.


On Wednesday, July 23, 2014 1:49 PM, Dave Cramer <pg@fastcrypt.com> wrote:


The crux of the problem is this:  ERROR: operator does not exist: timestamp with time zone >= interval

which appears to be caused by trans_date between now() - $2 and now()

What happens if you change it to :

trans_date between (now() - $2) and now()



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 12:10, CG <cgg007@yahoo.com> wrote:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What worked fine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with same issue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query runs fine on other platforms.

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c, maxRows=0, fetchSize=0, flags=1
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as direction,    last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,     speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between now() - $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5       ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,        $12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18       ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,        $25       ,
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,        $32       ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38       ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,        $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by     trans_date asc       ) foo   group by    t_uuid  ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
 secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-8 91e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44
99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d 2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>
,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc



Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
John R Pierce
Date:
On 7/23/2014 12:13 PM, CG wrote:
The parenthesis don't work, but if I explicitly cast that second parameter to interval "CAST( $2 AS interval)" I can force it to work. That's not ideal.

I don't think it is the lack of an operator. Timestamptz should not be comparable directly to an interval, right? That's like saying "2 + 2 >= Orange" or "today is greater than a minute". I think something is eating that first "now() -" so that what is being prepared looks like "trans_date between $2 and now()".

The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit linux? Based on that log, it looks like client-side prepare is falling down.

I think I'd try some experiments with  select pg_typeof($1),    passing it your 'interval' value and see what it thinks it is.


test=# select pg_typeof(interval '1 day');
 pg_typeof
-----------
 interval
(1 row)

test=# select pg_typeof('1 day');        
 pg_typeof
-----------
 unknown
(1 row)


-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
Tom Lane
Date:
CG <cgg007@yahoo.com> writes:
> The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit
linux?Based on that log, it looks like client-side prepare is falling down. 

It seems quite unlikely that this is a 32-vs-64-bit problem, especially
given that you're working in Java and not something more
machine-dependent.  I'd be looking at whether you have exactly the same
versions of the JDBC driver and other supporting libraries.  One way or
another, the backend was getting told that $2 was an interval before,
and now it's not being told that.

Another possible reason for a behavioral discrepancy would be if you were
forcing PG protocol version 2 on one system and not the other.

            regards, tom lane


Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
Dave Cramer
Date:
Do you still have access to the 32 bit system?

I'd be curious what happens if you try preparing a statement on each that just has the between 

so prepare foo (interval) as select * from xxx where trans_date between now()-?

using psql ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 15:13, CG <cgg007@yahoo.com> wrote:

I can change the SQL statement around to get it to run. 

The parenthesis don't work, but if I explicitly cast that second parameter to interval "CAST( $2 AS interval)" I can force it to work. That's not ideal.

I don't think it is the lack of an operator. Timestamptz should not be comparable directly to an interval, right? That's like saying "2 + 2 >= Orange" or "today is greater than a minute". I think something is eating that first "now() -" so that what is being prepared looks like "trans_date between $2 and now()".

The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit linux? Based on that log, it looks like client-side prepare is falling down.


On Wednesday, July 23, 2014 1:49 PM, Dave Cramer <pg@fastcrypt.com> wrote:


The crux of the problem is this:  ERROR: operator does not exist: timestamp with time zone >= interval

which appears to be caused by trans_date between now() - $2 and now()

What happens if you change it to :

trans_date between (now() - $2) and now()



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 12:10, CG <cgg007@yahoo.com> wrote:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What worked fine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with same issue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query runs fine on other platforms.

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c, maxRows=0, fetchSize=0, flags=1
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as direction,    last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,     speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between now() - $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5       ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,        $12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18       ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,        $25       ,
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,        $32       ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38       ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,        $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by     trans_date asc       ) foo   group by    t_uuid  ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
 secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-8 91e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44
99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d 2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>

,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc




Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
CG
Date:
@Tom : Digging around in the JDBC output logs, I have confirmed that both 32bit and 64bit servers are using protocol version 3 to communicate with our PostgreSQL server. Also, we've been very methodical to keep the environment as-same-as-possible between the servers. All the artifacts and configs were copied over unchanged.

@Dave : As you suggest, my next steps are going to be trying to create the simplest example that can repeatably demonstrate the problem. 

Psql is fine. "PREPARE foo (interval) AS SELECT * FROM t_vectors WHERE trans_date BETWEEN now() - $1 AND now();" executes as expected.

The full application environment has quite a few working parts. On one side, we're using MyBatis 2.3.5 to handle data access at the code level. On the other side is Tomcat's JNDI connection pooling. Right in the middle is the PostgreSQL JDBC driver. My gut says there's something up with JDBC client-side prepare, but I'll start by eliminating MyBatis and connection pooling so I'm not pointing my finger at the wrong thing.

I'll be back with some more data soon! Thanks all.

CG


On Thursday, July 24, 2014 8:09 AM, Dave Cramer <pg@fastcrypt.com> wrote:


Do you still have access to the 32 bit system?

I'd be curious what happens if you try preparing a statement on each that just has the between 

so prepare foo (interval) as select * from xxx where trans_date between now()-?

using psql ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 15:13, CG <cgg007@yahoo.com> wrote:

I can change the SQL statement around to get it to run. 

The parenthesis don't work, but if I explicitly cast that second parameter to interval "CAST( $2 AS interval)" I can force it to work. That's not ideal.

I don't think it is the lack of an operator. Timestamptz should not be comparable directly to an interval, right? That's like saying "2 + 2 >= Orange" or "today is greater than a minute". I think something is eating that first "now() -" so that what is being prepared looks like "trans_date between $2 and now()".

The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit linux? Based on that log, it looks like client-side prepare is falling down.


On Wednesday, July 23, 2014 1:49 PM, Dave Cramer <pg@fastcrypt.com> wrote:


The crux of the problem is this:  ERROR: operator does not exist: timestamp with time zone >= interval

which appears to be caused by trans_date between now() - $2 and now()

What happens if you change it to :

trans_date between (now() - $2) and now()



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 12:10, CG <cgg007@yahoo.com> wrote:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What worked fine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with same issue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query runs fine on other platforms.

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c, maxRows=0, fetchSize=0, flags=1
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as direction,    last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,     speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between now() - $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5       ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,        $12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18       ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,        $25       ,
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,        $32       ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38       ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,        $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by     trans_date asc       ) foo   group by    t_uuid  ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
 secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-8 91e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44
99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d 2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>

,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc






Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

From
CG
Date:
So here's where I am now. I can now point my finger at Tomcat 7 DBCP being the weak link.

Retrieving data from the database with plain JDBC in a main method works.
Retrieving data from the database co-opting Tomcat JNDI naming routines from tomcat-catalina-7.0.53 in a main method works.
Retrieving data from the database with plain JDBC running in a servlet under tomcat works.
Retrieving data from the database using tomcat's built-in context DBCP connection pooling in a servlet fails.

Clearing up a few mistakes I made in round one: We're using Centos and not Scientific Linux. Small and possibly irrelevant detail, but a detail none the less.
I also overstated the problem target was Centos 6.5 x86_64 over Centos 6.5 i686. 
I have been able to demonstrate that Tomcat 6 works on Centos 6.5 x86_64 through DBCP connection pooling while Tomcat 7 on the same server fails. 
I can run Tomcat 7 on Centos 6.5 i686 and fail.
I can run Tomcat 7 on Windows and not fail. This is odd, and probably will need some further examining...

My new problem target looks like it is PostgreSQL JDBC running through Tomcat 7 DBCP connection pooling on Centos 6.5.

There is one interesting difference between Tomcat 6 and Tomcat 7 that I can see in the JDBC logs:

This appears in Tomcat 6 logs. It does not appear in Tomcat 7 logs:

15:15:23.689 (2)  <=BE ReadyForQuery(I)
15:15:23.689 (2)     compatible = 8.3
15:15:23.689 (2)     loglevel = 2
15:15:23.690 (2)     prepare threshold = 5
15:15:23.715 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1364ee5, maxRows=0, fetchSize=0, flags=17
15:15:23.716 (2)  FE=> Parse(stmt=null,query="SELECT oid FROM pg_catalog.pg_type WHERE typname = $1",oids={0})
15:15:23.716 (2)  FE=> Bind(stmt=null,portal=null,$1=<interval>)
15:15:23.717 (2)  FE=> Describe(portal=null)
15:15:23.717 (2)  FE=> Execute(portal=null,limit=0)
15:15:23.717 (2)  FE=> Sync
15:15:23.720 (2)  <=BE ParseComplete [null]
15:15:23.720 (2)  <=BE BindComplete [null]
15:15:23.722 (2)  <=BE RowDescription(1)
15:15:23.724 (2)  <=BE DataRow
15:15:23.724 (2)  <=BE CommandStatus(SELECT 1)
15:15:23.740 (2)  <=BE ReadyForQuery(I)

And that is what is missing from Tomcat 7. Tomcat 7 goes straight for the SQL prepare without examining the PGInterval parameter and determining the internal type.

Maybe someone knows what triggers this pre-prepare inspection step?



On Friday, July 25, 2014 11:24 AM, CG <cgg007@yahoo.com> wrote:


@Tom : Digging around in the JDBC output logs, I have confirmed that both 32bit and 64bit servers are using protocol version 3 to communicate with our PostgreSQL server. Also, we've been very methodical to keep the environment as-same-as-possible between the servers. All the artifacts and configs were copied over unchanged.

@Dave : As you suggest, my next steps are going to be trying to create the simplest example that can repeatably demonstrate the problem. 

Psql is fine. "PREPARE foo (interval) AS SELECT * FROM t_vectors WHERE trans_date BETWEEN now() - $1 AND now();" executes as expected.

The full application environment has quite a few working parts. On one side, we're using MyBatis 2.3.5 to handle data access at the code level. On the other side is Tomcat's JNDI connection pooling. Right in the middle is the PostgreSQL JDBC driver. My gut says there's something up with JDBC client-side prepare, but I'll start by eliminating MyBatis and connection pooling so I'm not pointing my finger at the wrong thing.

I'll be back with some more data soon! Thanks all.

CG


On Thursday, July 24, 2014 8:09 AM, Dave Cramer <pg@fastcrypt.com> wrote:


Do you still have access to the 32 bit system?

I'd be curious what happens if you try preparing a statement on each that just has the between 

so prepare foo (interval) as select * from xxx where trans_date between now()-?

using psql ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 15:13, CG <cgg007@yahoo.com> wrote:

I can change the SQL statement around to get it to run. 

The parenthesis don't work, but if I explicitly cast that second parameter to interval "CAST( $2 AS interval)" I can force it to work. That's not ideal.

I don't think it is the lack of an operator. Timestamptz should not be comparable directly to an interval, right? That's like saying "2 + 2 >= Orange" or "today is greater than a minute". I think something is eating that first "now() -" so that what is being prepared looks like "trans_date between $2 and now()".

The question is what is causing valid SQL, prepared properly on 32bit linux to fail to prepare properly on 64bit linux? Based on that log, it looks like client-side prepare is falling down.


On Wednesday, July 23, 2014 1:49 PM, Dave Cramer <pg@fastcrypt.com> wrote:


The crux of the problem is this:  ERROR: operator does not exist: timestamp with time zone >= interval

which appears to be caused by trans_date between now() - $2 and now()

What happens if you change it to :

trans_date between (now() - $2) and now()



Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On 23 July 2014 12:10, CG <cgg007@yahoo.com> wrote:
Using PostgreSQL 9.1.3; JDBC3 driver (tried several versions-- all suffer from the same issue); MyBatis 2.3.5 

We're migrating our app to a virtualized 64bit Linux server from a virtualized 32bit Linux server, same farm. What worked fine on 32bit Scientific Linux breaks on 64bit... Same JVM version (although we tried 64 and 32 bit JVM both with same issue). We've done our best to make sure the ONLY thing different is the 64bit OS. Hitting the same 9.1.3 back end...

We've been sifting through everything and we can't conceive of what could possibly cause this problem. Also, this query runs fine on other platforms.

Maybe you can see something I can't:

10:02:40.596 (2)  <=BE ReadyForQuery(I)
10:02:40.601 (2) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1792d2c, maxRows=0, fetchSize=0, flags=1
10:02:40.601 (2)  FE=> Bind(stmt=S_1,portal=null)
10:02:40.601 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.601 (2)  FE=> Parse(stmt=null,query="      select    t_uuid as t_uuid,    last(latitude) as latitude,    last(longitude) as longitude,    last(trans_date) as trans_date,    last(speed) as speed,    last(direction) as direction,    last(revision) as revision   from (       SELECT     trans_date,     t_uuid,     latitude,     longitude,     speed,     direction,     revision    FROM     t_vectors    WHERE     group_uuid = $1     and trans_date between now() - $2 and now()                       AND       t_uuid in        (        $3       ,        $4       ,        $5       ,        $6       ,        $7       ,        $8       ,        $9       ,        $10       ,        $11       ,        $12       ,        $13       ,        $14       ,        $15       ,        $16       ,        $17       ,        $18       ,        $19       ,        $20       ,        $21       ,        $22       ,        $23       ,        $24       ,        $25       ,
    $26       ,        $27       ,        $28       ,        $29       ,        $30       ,        $31       ,        $32       ,        $33       ,        $34       ,        $35       ,        $36       ,        $37       ,        $38       ,        $39       ,        $40       ,        $41       ,        $42       ,        $43       ,        $44       ,        $45       ,        $46       ,        $47       ,        $48       ,        $49       )               order by     trans_date asc       ) foo   group by    t_uuid  ",oids={0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
10:02:40.601 (2)  FE=> Bind(stmt=null,portal=null,$1=<394fa90e-75a3-4dd1-9205-341409bcba4b>,$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
 secs>,$3=<cdd1aa29-48ae-4a8d-884f-43ea795defde>,$4=<3552acd6-e294-4d88-bd67-a856b4f5965c>,$5=<881d0513-37b9-4935-8051-b66552bada74>,$6=<fa21ec58-47e7-478a-b8b0-42a937200e70>,$7=<ec909e6b-c214-4fbe-aea0-9baf4a33b54a>,$8=<13c75de7-1e73-4dcd-906b-cee83ee0212e>,$9=<362d4c76-c09d-4e4f-8c05-821e9d46ba83>,$10=<76e02c3d-c108-443a-9cb3-6a1e138f00c6>,$11=<dbc8ee6d-b04d-4f48-90a3-b2a2bbd5de6e>,$12=<197c368a-669c-4d44-867b-6382726bae99>,$13=<8ae1f446-8364-4061-a89e-0058735cd35a>,$14=<fd148cc9-4b13-4773-9b7b-d5f2d1ca3a00>,$15=<7a4777eb-3767-43f8-9d67-f1caf3eff2b4>,$16=<f526b7ee-7823-4e4b-8bd5-dca091ca6aea>,$17=<8f4c2594-b368-43d2-8a27-8af24dc92512>,$18=<10ad0508-9eca-47e3-96f5-99745fbdcede>,$19=<112944c7-eef0-49c3-af79-9b54b484ca0c>,$20=<da94401d-10c5-4607-9b09-fa2202bba846>,$21=<ce9a4ddd-3c47-4bb9-af55-4d0c6c0600f8>,$22=<3448505e-b740-4084-8 91e-e2ff34c3754f>,$23=<8a3b486d-5e41-46e1-99fa-51f4f38af2fa>,$24=<a3260ac3-3811-4857-95a6-2cbcd1f8119b>,$25=<12a70091-0853-44
99-8f05-d80e3045ce5e>,$26=<e21a63e8-2835-4942-9a9e-18e1310a74e6>,$27=<5b41b6cb-de51-40ff-8488-7f3403b1f0a8>,$28=<83a3fe9b-cfb5-4908-b5fb-5b32a7be5eb1>,$29=<0a963150-eea0-4396-aa2a-9319ca9c9866>,$30=<0346505e-98be-4585-aeea-3b38df4a56a2>,$31=<069bf54d-c230-4bef-884b-46e54e28c65e>,$32=<8286bd41-ccd1-47bd-a1aa-1631d3ccbd56>,$33=<f7a3f9e2-e94b-4ff7-8b03-4e8549be07cb>,$34=<72f2af01-c359-45f9-bb20-37c7479869f3>,$35=<0143076a-3f9e-487d-bdec-c1a6a110f748>,$36=<2bc64e73-c771-43c8-aa83-b11059512a0f>,$37=<a5b56133-7cd2-476d-bde6-1b8af68bc265>,$38=<3c43a0dd-292a-44cc-a097-ab2f77757d25>,$39=<8b84e953-d6a9-42ae-9caa-481c164354db>,$40=<26d842b6-71e2-438f-a147-e18d102d21d5>,$41=<f588610a-507a-4c4c-93bb-a6466ed140ba>,$42=<d5a145c8-f8bd-486f-a0d7-f897715915c5>,$43=<bd0ed505-27e8-43b4-be4e-664adc98a38c>,$44=<b0948960-11b7-4bca-9d9c-4f8d7b91962c>,$45=<70d 2924f-c8cb-4594-a67b-261ca300406e>,$46=<60ad8ae8-4722-4a02-8d4f-88a0052299fc>,$47=<350558cc-e56f-4fb2-86f8-3d3252b56bce>

,$48=<de7259b4-e1c8-481c-9043-46f09a0926de>,$49=<2e5ecda2-bfdd-400a-a48a-e08e07d4ced4>)
10:02:40.602 (2)  FE=> Describe(portal=null)
10:02:40.602 (2)  FE=> Execute(portal=null,limit=0)
10:02:40.602 (2)  FE=> Sync
10:02:40.604 (2)  <=BE BindComplete [null]
10:02:40.604 (2)  <=BE CommandStatus(BEGIN)
10:02:40.607 (2)  <=BE ErrorMessage(ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410)
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= interval
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 410
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.DefaultSqlExecutor.executeQuery(DefaultSqlExecutor.java:183)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:578)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:552)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at com.foo.service.db.ibatis.lib.SqlMapExecutor.queryForList(SqlMapExecutor.java:139)
        at com.foo.service.db.ibatis.DepDaoIbatis.usersList(DepDaoIbatis.java:3352)
        at com.foo.util.Util.fetchSettings(Util.java:109)
        at com.foo.util.Util.fetchUpdates(Util.java:221)
        at com.foo.util.Util.fetchInit(Util.java:168)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:57)
        at com.foo.service.process.InitProcessor.process(InitProcessor.java:29)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:55)
        at com.foo.service.scaffolding.AbstractProcessor.process(AbstractProcessor.java:28)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:38)
        at com.restservice.process.AuthenticatedProcessor.process(AuthenticatedProcessor.java:12)
        at com.restservice.process.SerializedProcessor.processRequest(SerializedProcessor.java:74)
        at com.restservice.process.SerializedProcessor.process(SerializedProcessor.java:59)
        at com.restservice.RestService.process(RestService.java:224)
        at com.restservice.RestService.processWrapper(RestService.java:122)
        at com.restservice.RestService.doPost(RestService.java:101)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.foo.util.GZIPFilter.doFilter(GZIPFilter.java:33)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at com.googlecode.psiprobe.Tomcat70AgentValve.invoke(Tomcat70AgentValve.java:38)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc