Postgres case sensitivity issue

[Jere McDevitt <je...@gmail.com>]

Ran into an issue that took me a little while to track down.  This isn't a
bug with Jackrabbit, but is something to watch out for.

I had configured my repository to use Postgres as the persistence manager
and in the <DataStore> entry of the repository.xml file I was original
configured like:

  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
      <param name="driver" value="org.postgresql.Driver"/>
      <param name="url" value="jdbc:postgresql://testserver/jackrabbit"/>
      <param name="user" value="testuser"/>
      <param name="password" value="**********"/>
      <param name="databaseType" value="postgresql"/>
      <param name="minRecordLength" value="1024"/>
      <param name="maxConnections" value="3"/>
      <param name="copyWhenReading" value="true"/>
      <param name="tablePrefix" value=""/>
      <param name="schemaObjectPrefix" value="DS_"/>
      <param name="schemaCheckEnabled" value="true"/>
  </DataStore>

I was using the First Hop 3  as a sample and when I first ran it, it created
and loaded everything fine.

The second time I ran the example it failed, saying "relation ds_datastore
already exists".

When the repository starts up, the DbDataStore.java file does this call:

            ResultSet rs = meta.getTables(null, null, schemaObjectPrefix +
tableSQL, null);
            boolean exists = rs.next();

where tableSQL is loaded from the postgresql.properties file and has the
value 'datastore'. It apparently fails to find the meta data for the
'DS_datastore' table (note my schemaObjectPrefix setting), because Postgres
creates all table names in all lower case so it is in my database as
'ds_datastore'.  For some reason, the metadata fetch in DbDataStore.java
fails to find the table so it thinks the tables need to be created and this
then fails with the error I received.

Im using a Postgres 8.3 engine running on Debian with the postgresql-8.4-701
JDBC4 driver.  Not sure why the Postgres JDBC driver doesn't account for
this with a metadata fetch, but apparently the Postgres engine has no
problem with SQL using the mixed-case table name.

I changed all my schemaObjectPrefix values to lower case and everything
worked.

Jere

Re: Postgres case sensitivity issue

[Thomas Müller <th...@day.com>]

Hi,

Thanks! I have documented that now in the wiki:
http://wiki.apache.org/jackrabbit/DataStore

"Before Jackrabbit version 2.0, this setting is case sensitive (needs
to be lowercase for PostgreSQL and MySQL, and uppercase for other
databases)."

Regards,
Thomas


On Sat, Jan 2, 2010 at 4:41 AM, Jere McDevitt <je...@gmail.com> wrote:
> Ran into an issue that took me a little while to track down.  This isn't a
> bug with Jackrabbit, but is something to watch out for.
>
> I had configured my repository to use Postgres as the persistence manager
> and in the <DataStore> entry of the repository.xml file I was original
> configured like:
>
>  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
>      <param name="driver" value="org.postgresql.Driver"/>
>      <param name="url" value="jdbc:postgresql://testserver/jackrabbit"/>
>      <param name="user" value="testuser"/>
>      <param name="password" value="**********"/>
>      <param name="databaseType" value="postgresql"/>
>      <param name="minRecordLength" value="1024"/>
>      <param name="maxConnections" value="3"/>
>      <param name="copyWhenReading" value="true"/>
>      <param name="tablePrefix" value=""/>
>      <param name="schemaObjectPrefix" value="DS_"/>
>      <param name="schemaCheckEnabled" value="true"/>
>  </DataStore>
>
> I was using the First Hop 3  as a sample and when I first ran it, it created
> and loaded everything fine.
>
> The second time I ran the example it failed, saying "relation ds_datastore
> already exists".
>
> When the repository starts up, the DbDataStore.java file does this call:
>
>            ResultSet rs = meta.getTables(null, null, schemaObjectPrefix +
> tableSQL, null);
>            boolean exists = rs.next();
>
> where tableSQL is loaded from the postgresql.properties file and has the
> value 'datastore'. It apparently fails to find the meta data for the
> 'DS_datastore' table (note my schemaObjectPrefix setting), because Postgres
> creates all table names in all lower case so it is in my database as
> 'ds_datastore'.  For some reason, the metadata fetch in DbDataStore.java
> fails to find the table so it thinks the tables need to be created and this
> then fails with the error I received.
>
> Im using a Postgres 8.3 engine running on Debian with the postgresql-8.4-701
> JDBC4 driver.  Not sure why the Postgres JDBC driver doesn't account for
> this with a metadata fetch, but apparently the Postgres engine has no
> problem with SQL using the mixed-case table name.
>
> I changed all my schemaObjectPrefix values to lower case and everything
> worked.
>
> Jere
>