Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query - Mailing list pgsql-jdbc

From Dave Cramer
Subject Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query
Date
Msg-id CADK3HHLxnqiUmHsPWTh6NTs0v++mV_BbW2esxQi2GQXe7jjyGw@mail.gmail.com
Whole thread Raw
In response to Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query  (CG <cgg007@yahoo.com>)
Responses Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query
List pgsql-jdbc
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




pgsql-jdbc by date:

Previous
From: John Neal
Date:
Subject: Re: JBDC LDAP support for connection parameters
Next
From: "vjai.krishnan"
Date:
Subject: Re: Slow statement when using JDBC