AnsweredAssumed Answered

Postgres Schema Creation

Question asked by flavio.donze on Nov 3, 2010
Hello

I'm using PostgreSQL 8.4, Activiti in an OSGi environment together with Spring DM.
I tried the different dbSchemaStrategy options and run into trouble with each of them.

First up my spring configuration:

   <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
      <property name="processEngineName" value="ProcessEngine"/>
      <property name="transactionManager" ref="transactionManager"/>
      <property name="dataSource" ref="dataSource"/>
      <property name="dbSchemaStrategy" value="create"/>
      <property name="databaseType" value="postgres"/>
      <property name="jpaEntityManagerFactory" ref="entityManagerFactory" />
      <property name="jpaHandleTransaction" value="true" />
      <property name="jpaCloseEntityManager" value="true" />
   </bean>
   
   <bean id="actRepositoryService" factory-bean="processEngine" factory-method="getRepositoryService" />
   <bean id="actRuntimeService" factory-bean="processEngine" factory-method="getRuntimeService" />
   <bean id="actTaskService" factory-bean="processEngine" factory-method="getTaskService" />
   <bean id="actHistoryService" factory-bean="processEngine" factory-method="getHistoryService" />
   <bean id="actManagementService" factory-bean="processEngine" factory-method="getManagementService" />
transactionManager, dataSource and entityManagerFactory are provided by another OSGi bundle and used by other components of the framework (hibernate, jackrabbit).


If I use <property name="dbSchemaStrategy" value="create"/> the first launch (empty database) of the framework does fine, but by the second launch I get this exception:

ERROR: org.activiti.engine.impl.db.DbSqlSessionFactory - problem during schema create, statement 'create table ACT_GE_PROPERTY (
    NAME_ varchar(64),
    VALUE_ varchar(300),
    REV_ integer,
    primary key (NAME_)
)
org.postgresql.util.PSQLException: FEHLER: Relation »act_ge_property« existiert bereits
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:328)
   at org.enhydra.jdbc.core.CoreStatement.execute(CoreStatement.java:87)
   at org.tranql.connector.jdbc.StatementHandle.execute(StatementHandle.java:121)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:293)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.dbSchemaCreate(DbSqlSessionFactory.java:264)
   at org.activiti.engine.impl.ProcessEngineImpl.performSchemaOperationsCreate(ProcessEngineImpl.java:99)
   at org.activiti.engine.impl.ProcessEngineImpl$1.execute(ProcessEngineImpl.java:70)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:22)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:36)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
   at org.activiti.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:68)
   at org.activiti.engine.impl.cfg.ProcessEngineConfiguration.buildProcessEngine(ProcessEngineConfiguration.java:302)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:83)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:54)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1429)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.osgi.service.exporter.support.OsgiServiceFactoryBean.afterPropertiesSet(OsgiServiceFactoryBean.java:167)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:563)
   at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.access$1600(AbstractDelegatedExecutionApplicationContext.java:69)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext$4.run(AbstractDelegatedExecutionApplicationContext.java:355)
   at org.springframework.osgi.util.internal.PrivilegedUtils.executeWithCustomTCCL(PrivilegedUtils.java:85)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.completeRefresh(AbstractDelegatedExecutionApplicationContext.java:320)
   at org.springframework.osgi.extender.internal.dependencies.startup.DependencyWaiterApplicationContextExecutor$CompleteRefreshTask.run(DependencyWaiterApplicationContextExecutor.java:132)
   at java.lang.Thread.run(Thread.java:619)
org.activiti.engine.ActivitiException: couldn't create or drop db schema
   at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:311)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.dbSchemaCreate(DbSqlSessionFactory.java:264)
   at org.activiti.engine.impl.ProcessEngineImpl.performSchemaOperationsCreate(ProcessEngineImpl.java:99)
   at org.activiti.engine.impl.ProcessEngineImpl$1.execute(ProcessEngineImpl.java:70)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:22)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:36)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
   at org.activiti.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:68)
   at org.activiti.engine.impl.cfg.ProcessEngineConfiguration.buildProcessEngine(ProcessEngineConfiguration.java:302)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:83)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:54)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1429)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.osgi.service.exporter.support.OsgiServiceFactoryBean.afterPropertiesSet(OsgiServiceFactoryBean.java:167)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:563)
   at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.access$1600(AbstractDelegatedExecutionApplicationContext.java:69)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext$4.run(AbstractDelegatedExecutionApplicationContext.java:355)
   at org.springframework.osgi.util.internal.PrivilegedUtils.executeWithCustomTCCL(PrivilegedUtils.java:85)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.completeRefresh(AbstractDelegatedExecutionApplicationContext.java:320)
   at org.springframework.osgi.extender.internal.dependencies.startup.DependencyWaiterApplicationContextExecutor$CompleteRefreshTask.run(DependencyWaiterApplicationContextExecutor.java:132)
   at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Failed lazy association with ManagedConnection
   at org.tranql.connector.jdbc.ConnectionHandle.getManagedConnection(ConnectionHandle.java:73)
   at org.tranql.connector.jdbc.ConnectionHandle.createStatement(ConnectionHandle.java:199)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.executeSchemaResource(DbSqlSessionFactory.java:290)
   … 45 more

Using <property name="dbSchemaStrategy" value="create-if-necessary"/> I get the exception on the first start:

org.postgresql.util.PSQLException: FEHLER: Relation »act_ge_property« existiert nicht
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
   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.enhydra.jdbc.core.CorePreparedStatement.execute(CorePreparedStatement.java:82)
   at org.tranql.connector.jdbc.PreparedStatementHandle.execute(PreparedStatementHandle.java:94)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
   at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:62)
   at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:95)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:59)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:53)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:38)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:33)
   at org.activiti.engine.impl.db.DbSqlSessionFactory.dbSchemaCheckVersion(DbSqlSessionFactory.java:233)
   at org.activiti.engine.impl.ProcessEngineImpl.performSchemaOperationsCreate(ProcessEngineImpl.java:106)
   at org.activiti.engine.impl.ProcessEngineImpl$1.execute(ProcessEngineImpl.java:70)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:22)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:36)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
   at org.activiti.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:68)
   at org.activiti.engine.impl.cfg.ProcessEngineConfiguration.buildProcessEngine(ProcessEngineConfiguration.java:302)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:83)
   at org.activiti.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:54)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
   at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1429)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.osgi.service.exporter.support.OsgiServiceFactoryBean.afterPropertiesSet(OsgiServiceFactoryBean.java:167)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
   at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:563)
   at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.access$1600(AbstractDelegatedExecutionApplicationContext.java:69)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext$4.run(AbstractDelegatedExecutionApplicationContext.java:355)
   at org.springframework.osgi.util.internal.PrivilegedUtils.executeWithCustomTCCL(PrivilegedUtils.java:85)
   at org.springframework.osgi.context.support.AbstractDelegatedExecutionApplicationContext.completeRefresh(AbstractDelegatedExecutionApplicationContext.java:320)
   at org.springframework.osgi.extender.internal.dependencies.startup.DependencyWaiterApplicationContextExecutor$CompleteRefreshTask.run(DependencyWaiterApplicationContextExecutor.java:132)
   at java.lang.Thread.run(Thread.java:619)

Using <property name="dbSchemaStrategy" value="create-drop"/> the same as with create after the second launch.
Using <property name="dbSchemaStrategy" value="check-version"/> I get the same exception on the first launch.

Could my configuration be wrong? Is this a bug?

BTW: will mssql and db2 be supported in the final release?

greets and thanks for any help
flavio

Outcomes