Hello,
Context : openjpa-all-4.1.1 / PostgreSQL JDBC Driver v42.7.5 / PostgreSQL v17
The option : <property name="openjpa.jdbc.DBDictionary"
value="postgres(schemaCase=preserve)"/> In persistence.xml generates
PersistenceException (see below) because in method getColumns of class
DBDictionary ResultSet cols is empty when calling meta.getColumns(...) for
default table "OPENJPA_SEQUENCE_TABLE" when this table has been created in
database with schemaCase=lower before.
Since several years I was using the default value of schemaCase=lower but since
PostgreSQL JDBC Driver v42.7.5 it does work because they add introduce a call
to PostgreSQL internal function current_database() that can't be modified
according to schemaCase, so the following code is comparing the name of my
database which is in uppercase, with the catalog which is in lowercase then the
list of the columns returns empty and JPA try to create table that already
exists
v42.7.5 : PgDatabaseMetaData / getColumns()
if (catalog != null) {
sql += " AND current_database() = " + escapeQuotes(catalog);
}
So, I tried the option "schemaCase=preserve" but in that case (and also for
previous version of PostgreSQL JDBC Driver v42.7.5) a new problem occurs,
because the name of the default JPA table is by default in uppercase (In class
org.apache.openjpa.jdbc.kernel.TableJDBCSeq) and my table was created
previously in lowercase then the list of the columns returns empty for this
table and JPA try to create table that already exists
v42.7.4 : PgDatabaseMetaData / getColumns()
if (tableNamePattern != null) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
A solution would be to get the name of this table like it is existing in
database when exists instead of applying the schemaCase to default values that
can change :
public static final String DEFAULT_TABLE = "OPENJPA_SEQUENCE_TABLE";
private DBIdentifier _table = DBIdentifier.newTable(DEFAULT_TABLE);
private DBIdentifier _seqColumnName = DBIdentifier.newColumn("SEQUENCE_VALUE");
private DBIdentifier _pkColumnName = DBIdentifier.newColumn("ID");
To summarize, the option schemaCase=preserve does not work because it doesn't
take into account the case of the current table and column names of default
OPENJPA table.
Could you please fix this issue ?
Thanks in advance,
Frederic
======================================================================================================================================================
=> persistence.xml :
<properties>
<property name="javax.persistence.lock.timeout" value="10000" /> <!-- in
milliseconds -->
<property name="javax.persistence.query.timeout" value="10000" /> <!--
in milliseconds -->
<property name="openjpa.ConnectionDriverName"
value="org.postgresql.Driver" />
<property name="openjpa.Multithreaded" value="true" />
<property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(foreignKeys=true)" />
<property name="openjpa.jdbc.SchemaFactory"
value="native(foreignKeys=true)" />
<property name="openjpa.jdbc.DBDictionary"
value="postgres(schemaCase=preserve)"/>
<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=restrict, JoinForeignKeyDeleteAction=restrict" />
<property name="openjpa.Log" value="slf4j" />
</properties>
=> openjpa PersistenceException:
27-08-2025 13:58:33.009 [main] DEBUG [d.t.e.APersistencedb][openPhaseRemote]
dbUrl = jdbc:postgresql://localhost:xxxx/DB_TEST
27-08-2025 13:58:33.010 [main] INFO [d.t.e.APersistencedb][openPhaseRemote]
{openjpa.ConnectionURL=jdbc:postgresql://localhost:xxxx/DB_TEST,
openjpa.ConnectionUserName=xxx, openjpa.ConnectionPassword=xxx}
27-08-2025 13:58:33.381 [main] INFO [o.Runtime][info] OpenJPA dynamically
loaded a validation provider.
27-08-2025 13:58:33.416 [main] INFO [o.Runtime][info] Starting OpenJPA 4.1.1
27-08-2025 13:58:33.562 [main] INFO [o.j.JDBC][info] Using dictionary class
"org.apache.openjpa.jdbc.sql.PostgresDictionary".
27-08-2025 13:58:35.179 [main] INFO [o.j.JDBC][info] Connected to PostgreSQL
version 17.2 using JDBC driver PostgreSQL JDBC Driver version 42.7.5.
27-08-2025 14:03:20.486 [main] ERROR [d.t.e.APersistencedb][openPhaseRemote]
ERREUR: la relation « openjpa_sequence_table » existe déjà {stmnt 123629835
CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE
BIGINT, PRIMARY KEY (ID))} [code=0, state=42P07]
org.apache.openjpa.persistence.PersistenceException: ERREUR: la relation «
openjpa_sequence_table » existe déjà {stmnt 123629835 CREATE TABLE
OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY
KEY (ID))} [code=0, state=42P07]
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:625)
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:488)
at
org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:173)
at
org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:178)
at
org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.newBrokerImpl(JDBCBrokerFactory.java:134)
at
org.apache.openjpa.kernel.AbstractBrokerFactory.newBroker(AbstractBrokerFactory.java:213)
at
org.apache.openjpa.kernel.DelegatingBrokerFactory.newBroker(DelegatingBrokerFactory.java:166)
at
org.apache.openjpa.persistence.EntityManagerFactoryImpl.doCreateEM(EntityManagerFactoryImpl.java:282)
at
org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:201)
at
org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:188)
at
org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:178)
at
org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:64)
=> openjpa DBDictionary :
ResultSet cols = null;
try {
cols = meta.getColumns(getCatalogNameForMetadata(catalog),
sqlSchemaName, getTableNameForMetadata(tableName),
getColumnNameForMetadata(columnName));
======================================================================================================================================================
