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 <frederic.monc...@thalesaleniaspace.com.invalid> 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)); > > > ====================================================================================================================================================== > >
