Re: [Sequoiadb-discuss] [Sequoia] sql statement failed

[Emmanuel C. <ma...@fr...>]

Hi Julio,

Note that the Sequoia mailing list is now at 
seq...@li....
> We run sequoia 2.10, ,backend is postgresql 8.3.5
> Os Suse Linux 10.2
I guess that you are using Sequoia 2.10.10 otherwise I would suggest 
that you upgrade to that version first.
> This statement is failing in sequoia
>
> delete from c1 where promotionid=? and itemfactoryname=? and 
> (promotionid, itemfactoryname, item, datemodified) NOT in (select 
> promotionid, itemfactoryname, item, datemodified from view_c1 p1 where 
> p1.datemodified in (select max(p2.datemodified) from view_c1p2 where 
> p2.promotionid = p1.promotionid and p2.itemfactoryname = 
> p1.itemfactoryname and p2.item = p1.item and p2.restartdate1 = 
> p1.restartdate1))/<!%I|1001|!%><!%S|stub|!%>
>
>
> These are the errors
>
> 2009-10-27 12:13:09,829 WARN  controller.RequestManager.db1 Failed to 
> execute request delete from c1 where p... because of 
> (org.continuent.sequoia.controller.requests.StoredProcedure)
> java.lang.ClassCastException: 
> org.continuent.sequoia.controller.requests.StoredProcedure
>         at 
> org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.callStoredProcedure(RAIDb1.java:851)
>         at 
> org.continuent.sequoia.controller.loadbalancer.raidb1.RAIDb1.statementExecute(RAIDb1.java:281)
>         at 
> org.continuent.sequoia.controller.requestmanager.RequestManager.loadBalanceStatementExecute(RequestManager.java:974)
>         at 
> org.continuent.sequoia.controller.requestmanager.RequestManager.statementExecute(RequestManager.java:769)
>         at 
> org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.statementExecute(VirtualDatabase.java:735)
>         at 
> org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.statementExecute(VirtualDatabaseWorkerThread.java:2312)
>         at 
> org.continuent.sequoia.controller.virtualdatabase.VirtualDatabaseWorkerThread.run(VirtualDatabaseWorkerThread.java:445)
> 2009-10-27 12:13:09,834 WARN  
> virtualdatabase.VirtualDatabaseWorkerThread.abrazo Error during 
> command execution (Cannot abort transaction 0 since current worker 
> thread is assigned to transaction 0)
> 2009-10-27 12:13:09,852 WARN  
> virtualdatabase.VirtualDatabaseWorkerThread.abrazo Transaction 0 was 
> aborted by database
>
>
> Any ideas ?
There is a problem with the parser that somehow confuses your delete 
query with a stored procedure call.
An easy fix is to use statement.executeUpdate() instead of 
statement.execute(). It is a relatively bad practice to use 
statement.execute() and it will always be slower for Sequoia since it 
has to do extra processing to try to figure out what you are executing.

There was a bug in earlier versions of Sequoia that triggered that 
problem but it should be fixed in 2.10.10.

Keep us posted with your findings.
Emmanuel

-- 
Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: ma...@fr...
Skype: emmanuel_cecchet