Re: Upgrade to Scientific Linux 6.5 x86_64 breaks query

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(at)yahoo(dot)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(at)fastcrypt(dot)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(at)yahoo(dot)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(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
>
>