Thread: exception with a temporary table

Pablo,

You should be aware that the temporary table only exists for the
connection that created it.

Once that connection closes the table is dropped, also that table is
only visible to the connection that created it.

Dave
On 20-Nov-06, at 5:05 AM, Pablo Araujo wrote:

> Hello forum, i am new in this forum. Excuse, but my English is not
> very good.
>
>  I have an application CRUD, created from AndroMDA, with several
> classes that inherit of others, for example:
>
> camera ---> item
> host ---> item
>
> and other tables are associate as well to these:
>
> cameraassignment --associations--> camera
> I am using PostgreSQL 8.1 and hibernate 3.
>
> The exception happens when i delete a camera, for example.
>
> The full stack trace is:
>
> 10:42:03,013 INFO  [STDOUT] Hibernate: insert into HT_item select
> itemimpl0_.item_id as item_id from public.item itemimpl0_ where
> item_id in (?)
> 10:42:03,029 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState:
> 42P01
> 10:42:03,029 ERROR [JDBCExceptionReporter] ERROR: relation
> "ht_item" does not exist
> 10:42:03,029 WARN  [MultiTableDeleteExecutor] unable to drop
> temporary id table after use
> org.postgresql.util.PSQLException : ERROR: table "ht_item" does not
> exist
>         at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse
> (QueryExecutorImpl.java:1512)
>         at org.postgresql.core.v3.QueryExecutorImpl.processResults
> (QueryExecutorImpl.java:1297)
>         at org.postgresql.core.v3.QueryExecutorImpl.execute
> (QueryExecutorImpl.java:188)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
> (AbstractJdbc2Statement.java:437)
>         at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
> (AbstractJdbc2Statement.java:339)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate
> (AbstractJdbc2Statement.java:285)
>         at
> org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate
> (WrappedStatement.java:184)
>         at org.hibernate.hql.ast.exec.AbstractStatementExecutor
> $2.doWork(AbstractStatementExecutor.java:149)
>         at
> org.hibernate.hql.ast.exec.AbstractStatementExecutor.dropTemporaryTabl
> eIfNecessary (AbstractStatementExecutor.java:170)
>         at
> org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute
> (MultiTableDeleteExecutor.java:136)
>         at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate
> (QueryTranslatorImpl.java :334)
>         at
> org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate
> (HQLQueryPlan.java:209)
>         at org.hibernate.impl.SessionImpl.executeUpdate
> (SessionImpl.java:1126)
>         at org.hibernate.impl.QueryImpl.executeUpdate
> (QueryImpl.java:94)
>         at
> es.indra.vipet.appcrud.domain.crud.CameraManageableDaoBase.delete
> (CameraManageableDaoBase.java:380)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke
> (NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke
> (DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke (Method.java:585)
>         at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflectio
> n(AopUtils.java:287)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoi
> npoint(ReflectiveMethodInvocation.java :181)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
> (ReflectiveMethodInvocation.java:148)
>         at
> org.springframework.orm.hibernate3.HibernateInterceptor.invoke
> (HibernateInterceptor.java :97)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
> (ReflectiveMethodInvocation.java:170)
>         at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke
> (JdkDynamicAopProxy.java :176)
>         at $Proxy75.delete(Unknown Source)
>         at
> es.indra.vipet.appcrud.domain.crud.CameraManageableServiceBase.delete(
> CameraManageableServiceBase.java:88)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native
> Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke
> (NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke
> (DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflectio
> n(AopUtils.java:287)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoi
> npoint (ReflectiveMethodInvocation.java:181)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
> (ReflectiveMethodInvocation.java:148)
>         at
> org.springframework.orm.hibernate3.HibernateInterceptor.invoke
> (HibernateInterceptor.java:97)
>         at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
> (ReflectiveMethodInvocation.java:170)
>         at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke
> (JdkDynamicAopProxy.java:176)
>         at $Proxy76.delete(Unknown Source)
>         at es.indra.vipet.appcrud.domain.crud.ManageCamera.delete
> (ManageCamera.java:147)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native
> Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke
> (NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke
> (DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at org.apache.struts.actions.DispatchAction.dispatchMethod
> (DispatchAction.java:274)
>         at org.apache.struts.actions.DispatchAction.execute
> (DispatchAction.java :194)
>         at es.indra.vipet.appcrud.domain.crud.ManageCamera.execute
> (ManageCamera.java:22)
>         at
> org.apache.struts.action.RequestProcessor.processActionPerform
> (RequestProcessor.java:419)
>         at org.apache.struts.action.RequestProcessor.process
> (RequestProcessor.java:224)
>         at org.apache.struts.action.ActionServlet.process
> (ActionServlet.java:1194)
>         at org.apache.struts.action.ActionServlet.doPost
> (ActionServlet.java:432)
>         at
> org.andromda.presentation.bpm4struts.ActionServlet.doPost
> (ActionServlet.java:48)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:
> 717)
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:
> 810)
>         at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
> (ApplicationFilterChain.java:252)
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter
> (ApplicationFilterChain.java:173)
>         at org.displaytag.filter.ResponseOverrideFilter.doFilter
> (ResponseOverrideFilter.java :125)
>         at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
> (ApplicationFilterChain.java:202)
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter
> (ApplicationFilterChain.java:173)
>         at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter
> (ReplyHeaderFilter.java:96)
>         at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
> (ApplicationFilterChain.java:202)
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter
> (ApplicationFilterChain.java:173)
>         at org.apache.catalina.core.StandardWrapperValve.invoke
> (StandardWrapperValve.java:213)
>         at org.apache.catalina.core.StandardContextValve.invoke
> (StandardContextValve.java:178)
>         at
> org.jboss.web.tomcat.security.SecurityAssociationValve.invoke
> (SecurityAssociationValve.java:175)
>         at org.jboss.web.tomcat.security.JaccContextValve.invoke
> (JaccContextValve.java :74)
>         at org.apache.catalina.core.StandardHostValve.invoke
> (StandardHostValve.java:126)
>         at org.apache.catalina.valves.ErrorReportValve.invoke
> (ErrorReportValve.java:105)
>         at org.apache.catalina.core.StandardEngineValve.invoke
> (StandardEngineValve.java:107)
>         at org.apache.catalina.connector.CoyoteAdapter.service
> (CoyoteAdapter.java:148)
>         at org.apache.coyote.http11.Http11Processor.process
> (Http11Processor.java:869)
>         at org.apache.coyote.http11.Http11BaseProtocol
> $Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:
> 664)
>         at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket
> (PoolTcpEndpoint.java:527)
>         at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run
> (MasterSlaveWorkerThread.java:112)
>         at java.lang.Thread.run(Thread.java:595)
> what I have found out until now is that the exception happens when
> ht_item tries to use the temporary table that must be created before.
> Why it is not created?
>
> I have proven with mysql and works correctly.
>
> Thanks

Pablo Araujo wrote:
> ok, but this assumes that being created everything in the same session.
>
> that is to say, it creates it, it inserts data and it destroys it in the
> same session.

Can you get a trace of the SQL commands sent to the server? One way to
get that is to set the log_statement configuration parameter in your
postgresql.conf file to "all".

That would help diagnosing the problem.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Pablo Araujo wrote:
> I think that the problem is in "on commit drop", because i excecute this
> statment in the query browse of pgAdmin III and happens the same, What I
> must modify?

Yep. I don't know where the create table statement is coming from;
Hibernate, AndroMDA or your application code, but apparently it needs to
be changed to "on commit delete rows".

Or maybe you're supposed to be running the statements as a single
transaction, but you're not?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Pablo Araujo wrote:
> Yes I suppose that the statment is running as a single transaction, but,
> why is running it with MySQL?
> I think that it must have a property in hibernate or postgresql, about who
> can be indicated this type of things.
>
> What do you think about this?

I'm afraid I can't help you any further. PostgreSQL is just executing
what the application and/or the 3rd party libraries are sending to it.
You might consider getting a similar trace from MySQL and compare, or
search the manuals of Hibernate for any related properties. And make
sure you understand where your transaction begins and where it ends, and
where it should begin and end.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com