Hi Romain
Thanks for your reply.
In DBDictionary.java, "cols" is empty because
"getTableNameForMetadata(tableName)" returns the default value : public static
final String DEFAULT_TABLE = "OPENJPA_SEQUENCE_TABLE"; (TableJDBCSeq.java)
which is in uppercase when the property schemaCase value is 'preserve' and the
existing database is "openjpa_sequence_table" in lower case, so the comparison
in JDBC Driver fails :
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
There is inconsistency in the way of managing the parameter tableNamePattern
because it doesn't preserve the sensitivity case of the existing schema, it
preserve only the sensitivity of the default value, so once the schema has been
built in lower case, you can't use 'preserve' anymore and that is a pity
because of the last change in JDBC Driver I need to use 'preserve' because they
introduce a call to PostgreSQL function that is case sensitive and is not
affected by the parameter 'schemaCase'.
Also, on PostrgeSQL JDBC side, they said that Spring did adapt to this change :
https://github.com/pgjdbc/pgjdbc/issues/3731
· JDBC Driver Community : "Yes, this is where the problem occurs. Where
would we get schemaCase from ? OpenJPA is calling getColumns() with the current
catalog in lowerCase. It is their bug. As I mentioned spring fixed their code."
· Me: "Yes, this is there bug. But I think there is a risk of bug by
comparing "current_database()" and "catalog" because it will not work if case
is forced to 'lower' or 'upper' and database name is not the same or a mix of
both, don't you ?"
· JDBC Driver Community : "That is why they must honour presereve to
make sure they are using the same case. we can't ignore case as DATABASE1 is
different than database1"
>From my point of view, because schemaCase is applied on all existing table
>name that has been created for your app, it should work the same way for
>system table of OpenJPA, I means the existing table name in the database
>should be used since it has been created instead of hard coded default value.
>And the default value should be used only when a new schema is going to be
>created.
Then, could you please fix this issue ?
Thanks,
Fred
-----------------------
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1710
v47.2.4
@Override
public ResultSet getColumns(@Nullable String catalog, @Nullable String
schemaPattern,
@Nullable String tableNamePattern,
@Nullable String columnNamePattern) throws SQLException {
...
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
if (connection.haveMinimumServerVersion(ServerVersion.v8_4)) {
sql += ") c WHERE true ";
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
sql += " AND attname LIKE " + escapeQuotes(columnNamePattern);
}
sql += " ORDER BY nspname,c.relname,attnum ";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
...
-----------------------
https://github.com/pgjdbc/pgjdbc/blob/REL42.7.5/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1663
v47.2.5
@Override
public ResultSet getColumns(@Nullable String catalog, @Nullable String
schemaPattern,
@Nullable String tableNamePattern,
@Nullable String columnNamePattern) throws SQLException {
...
if (catalog != null) {
sql += " AND current_database() = " + escapeQuotes(catalog);
}
-----------------------
https://github.com/apache/openjpa/blob/master/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java#L4477
v4.1.1
/**
* Reflect on the schema to find columns matching the given table and
* column patterns.
*/
public Column[] getColumns(DatabaseMetaData meta, DBIdentifier catalog,
DBIdentifier schemaName, DBIdentifier tableName, DBIdentifier columnName,
Connection conn)
throws SQLException {
if (DBIdentifier.isNull(tableName) && !supportsNullTableForGetColumns)
return null;
String sqlSchemaName = null;
if (!DBIdentifier.isNull(schemaName)) {
sqlSchemaName = schemaName.getName();
}
if (!supportsSchemaForGetColumns)
sqlSchemaName = null;
else
sqlSchemaName = getSchemaNameForMetadata(schemaName);
beforeMetadataOperation(conn);
ResultSet cols = null;
try {
cols = meta.getColumns(getCatalogNameForMetadata(catalog),
sqlSchemaName, getTableNameForMetadata(tableName),
getColumnNameForMetadata(columnName));
List columnList = new ArrayList();
while (cols != null && cols.next())
columnList.add(newColumn(cols));
return (Column[]) columnList.toArray
(new Column[columnList.size()]);
} finally {
if (cols != null)
try {
cols.close();
} catch (Exception e) {
}
}
}
-----------------------
Le 27 août 2025 21:51, Romain Manni-Bucau <[email protected]> a écrit :
Hi Frédéric,
I'm not sure I get this one, you can control default values so ensure it
exactly match.
Doesn't it work for you? There are cases you do not want to hit the schema
but still be right so the tuning is quite required generally speaking.
I didn't run this particular one but isnt the issue that postgres changed
the case initially and now can't match it anymore so the issue is more on
postgres side or in the will to use preserve?
(to rephrase it, I'm not shock we use the runtime values without checking
the db since it is what we want to enforce on openjpa side, if you have a
case sensitive db like herddb and you test openjpa_sequence_table for
OPENJPA_SEQUENCE_TABLE in the config then this would be a bug)
side note: if the dictionary is configured to be case insensitive with
case=preserve we should just wrap names in "lower()" or alike to avoid
these issues in all comparisons
Romain Manni-Bucau
@rmannibucau <https://x.com/rmannibucau> | .NET Blog
<https://dotnetbirdie.github.io/> | Blog <https://rmannibucau.github.io/> | Old
Blog <http://rmannibucau.wordpress.com> | Github
<https://github.com/rmannibucau> | LinkedIn
<https://www.linkedin.com/in/rmannibucau> | Book
<https://www.packtpub.com/en-us/product/java-ee-8-high-performance-9781788473064>
Javaccino founder (Java/.NET service - contact via linkedin)
Le mer. 27 août 2025 à 21:43, MONCLAR Frederic
<[email protected]> a écrit :
> 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));
>
>
> ======================================================================================================================================================
>
>
