[jira] Commented: (CONTINUUM-994) New indexes created during continuum startup when using postgresql

[Richard C. L. Li (JIRA)]

    [ http://jira.codehaus.org/browse/CONTINUUM-994?page=comments#action_85299 
] 
            
Richard C. L. Li commented on CONTINUUM-994:
--------------------------------------------

After some investigation, I figure out that the problem is in jpox.  When using 
Upper Case for identifiers, postgresql jdbc driver will response all identifier 
with quotes, but quoting identifiers will trigger a bug in jpox that it cannot 
identify existing indexes and recreating them.  To work around, simply set the 
use of identifiers to use Lower Case and problem solved.

Edit the file <continuum 
home>/apps/continuum/webapp/WEB-INF/classes/META-INF/plexus/application.xml and 
add the property org.jpox.identifier.case in the jpox component definition.

{code:xml}
    <component>
      <role>org.codehaus.plexus.jdo.JdoFactory</role>
      <role-hint>users</role-hint>
      
<implementation>org.codehaus.plexus.jdo.DataSourceConfigurableJdoFactory</implementation>
      <configuration>

        <connectionFactoryName>java:comp/env/jdbc/users</connectionFactoryName>
        
<shutdownConnectionFactoryName>java:comp/env/jdbc/usersShutdown</shutdownConnectionFactoryName>

        <!-- JPOX and JDO configuration -->
        
<persistenceManagerFactoryClass>org.jpox.PersistenceManagerFactoryImpl</persistenceManagerFactoryClass>
        <otherProperties>
          <property>
            <name>org.jpox.autoCreateSchema</name>
            <value>true</value>
          </property>
          <property>
            <name>org.jpox.validateTables</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.validateConstraints</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.validateColumns</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.autoStartMechanism</name>
            <value>None</value>
          </property>
          <property>
            <name>org.jpox.transactionIsolation</name>
            <value>READ_UNCOMMITTED</value>
          </property>
          <property>
            <name>org.jpox.poid.transactionIsolation</name>
            <value>READ_UNCOMMITTED</value>
          </property>
          <property>
            <name>org.jpox.rdbms.dateTimezone</name>
            <value>JDK_DEFAULT_TIMEZONE</value>
          </property>
          <property>
            <name>org.jpox.identifier.case</name>
            <value>LowerCase</value>
          </property>
        </otherProperties>
      </configuration>
    </component>

    <component>
      <role>org.codehaus.plexus.jdo.JdoFactory</role>
      
<implementation>org.codehaus.plexus.jdo.DataSourceConfigurableJdoFactory</implementation>
      <role-hint>continuum</role-hint>
      <configuration>
        
<connectionFactoryName>java:comp/env/jdbc/continuum</connectionFactoryName>
        
<shutdownConnectionFactoryName>java:comp/env/jdbc/continuum_shutdown</shutdownConnectionFactoryName>

        
<persistenceManagerFactoryClass>org.jpox.PersistenceManagerFactoryImpl</persistenceManagerFactoryClass>

        <otherProperties>
          <property>
            <name>org.jpox.autoCreateSchema</name>
            <value>true</value>
          </property>
          <property>
            <name>org.jpox.validateTables</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.validateColumns</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.validateConstraints</name>
            <value>false</value>
          </property>
          <property>
            <name>org.jpox.autoStartMechanism</name>
            <value>None</value>
          </property>
          <property>
            <name>org.jpox.transactionIsolation</name>
            <value>READ_UNCOMMITTED</value>
          </property>
          <property>
            <name>org.jpox.identifier.case</name>
            <value>LowerCase</value>
          </property>
        </otherProperties>
      </configuration>
    </component>

{code}



> New indexes created during continuum startup when using postgresql
> ------------------------------------------------------------------
>
>                 Key: CONTINUUM-994
>                 URL: http://jira.codehaus.org/browse/CONTINUUM-994
>             Project: Continuum
>          Issue Type: Bug
>          Components: Database
>    Affects Versions: 1.0.3
>            Reporter: Richard C. L. Li
>             Fix For: 1.1
>
>
> I used postgresql 8.1.4 with continuum and everytime when continuum startup, 
> it creates a set of indexes.  I configured to restart continuum once a day 
> and after 2 months it generated tens of indexes in every table.
> I guessed this maybe the problem of the the UPPER CASE of the table and 
> column names, this may make the detection of indexes fails and the JDO 
> recreate everytime it startup.
> Workaround: after starting continuum for the first time and set the property 
> org.jpox.autoCreateSchema to false so that indexes will not recreated.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira