Return-Path: 
 <users-return-13877-apmail-jackrabbit-users-archive=jackrabbit.apache.org@jackrabbit.apache.org>
Delivered-To: apmail-jackrabbit-users-archive@minotaur.apache.org
Received: (qmail 46871 invoked from network); 2 Jan 2010 03:41:56 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3)
  by minotaur.apache.org with SMTP; 2 Jan 2010 03:41:56 -0000
Received: (qmail 92574 invoked by uid 500); 2 Jan 2010 03:41:55 -0000
Delivered-To: apmail-jackrabbit-users-archive@jackrabbit.apache.org
Received: (qmail 92393 invoked by uid 500); 2 Jan 2010 03:41:53 -0000
Mailing-List: contact users-help@jackrabbit.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:users-help@jackrabbit.apache.org>
List-Unsubscribe: <mailto:users-unsubscribe@jackrabbit.apache.org>
List-Post: <mailto:users@jackrabbit.apache.org>
List-Id: <users.jackrabbit.apache.org>
Reply-To: users@jackrabbit.apache.org
Delivered-To: mailing list users@jackrabbit.apache.org
Received: (qmail 92382 invoked by uid 99); 2 Jan 2010 03:41:53 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 02 Jan 2010 03:41:53 +0000
X-ASF-Spam-Status: No, hits=2.2 required=10.0
	tests=HTML_MESSAGE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of jere.mcdevitt@gmail.com
 designates 209.85.219.217 as permitted sender)
Received: from [209.85.219.217] (HELO mail-ew0-f217.google.com)
 (209.85.219.217)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 02 Jan 2010 03:41:46 +0000
Received: by ewy9 with SMTP id 9so3265562ewy.11
        for <users@jackrabbit.apache.org>;
 Fri, 01 Jan 2010 19:41:24 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=gamma;
        h=domainkey-signature:mime-version:received:date:message-id:subject
         :from:to:content-type;
        bh=zuYxy3/8yK6NsN57myOPjY0677t3uEzCHqW+zmeUARs=;
        b=b4OgvRPCTTaNEBFbyM0cvFHj/txRRtz1FsAu0piOoqYkXomfTE4G8vkVxgVYoDYzKm
         Y042R8HFcRWfQuq+QoUCIcWQ1PY31PHO1/o7kNqbZItp045CEAW3/kdegcL4BgUiPGTh
         i4qGxQlMWDiJngorwP/McDqkTQ22Xed42aVb8=
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:date:message-id:subject:from:to:content-type;
        b=LUSUm6u8URUKtecv+KhgZ80oTmQAdF1NJhTKNmja+YFVSITmSlL/+/c4NnmTCfETq0
         epSy8nYZlaEgHwgiqwEBpB55h7V3/rqJ4On+sc/cl7RE1H3XfoEYBpc4IuWt54nP9I9I
         w3O1PHsjc77H6b+I/9ftMFhRqWxU6EgEvmszc=
MIME-Version: 1.0
Received: by 10.216.87.203 with SMTP id y53mr7429423wee.177.1262403682141;
	Fri, 01 Jan 2010 19:41:22 -0800 (PST)
Date: Fri, 1 Jan 2010 22:41:22 -0500
Message-ID: <d0897c3b1001011941y7dbf3b2bt75a6a24dcadc4392@mail.gmail.com>
Subject: Postgres case sensitivity issue
From: Jere McDevitt <jere.mcdevitt@gmail.com>
To: users@jackrabbit.apache.org
Content-Type: multipart/alternative; boundary=0016e6d9a329098704047c26431b

--0016e6d9a329098704047c26431b
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d9a329098704047c26431b--