Subject: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4

From: "monclaf (@monclaf)" <[email-masked]>
Date: Tue, 22 Jul 2025 12:49:39 +0000

Hello,

I use JPA and I get error : "ReportingSQLException : ERREUR: la relation « element » existe déjà" when I use jdbc driver version newer than v42.7.4. That happend when I try to create the entity manager. The database with their tables already exist so I don't understand why the driver try to create table that already exists.
Thanks for any help.
Fred

`EntityManagerFactory emf = Persistence.createEntityManagerFactory(Constants.DB_NAME_PREFIX + Constants.DB_NAME_SUFFIX, properties);
EntityManager em = emf.createEntityManager();`

Persistence.xml :

    <persistence-unit name="sinop_db" transaction-type="RESOURCE_LOCAL">
		<!-- provider -->
		<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
		<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.MappingDefaults" value="ForeignKeyDeleteAction=restrict, JoinForeignKeyDeleteAction=restrict" />
			<property name="openjpa.Log" value="slf4j" />
		</properties>
	</persistence-unit>

Context : 
postgreSQL v17 - postgresql-42.7.5/6/7.jar - openjpa-all-4.1.1.jar or openjpa-all-4.0.1.jar

Stack trace :
`22-07-2025 13:05:42.109 [pool-6-thread-1] DEBUG [c.t.s.s.p.d.i.f.APersistencedb][openPhaseRemote] dbUrl = jdbc:postgresql://localhost:9917/db_COM
22-07-2025 13:05:42.110 [pool-6-thread-1] INFO  [c.t.s.s.p.d.i.f.APersistencedb][openPhaseRemote] {openjpa.ConnectionURL=jdbc:postgresql://localhost:9917/db_COM, openjpa.ConnectionUserName=postgres, openjpa.ConnectionPassword=postgres}

22-07-2025 13:05:52.082 [pool-6-thread-1] ERROR [c.t.s.s.p.d.i.f.APersistencedb][openPhaseRemote] ERREUR: la relation « element » existe déjà {stmnt 445531560 CREATE TABLE element ...))} [code=0, state=42P07]
org.apache.openjpa.persistence.PersistenceException: ERREUR: la relation « element » existe déjà {stmnt 445531560 CREATE TABLE element ...)} [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)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
	at java.base/java.lang.Thread.run(Thread.java:1583)

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERREUR: la relation « element » existe déjà {stmnt 445531560 CREATE TABLE element ...))} [code=0, state=42P07]
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:203)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingStatement.executeUpdate(LoggingConnectionDecorator.java:955)
	at org.apache.openjpa.lib.jdbc.DelegatingStatement.executeUpdate(DelegatingStatement.java:123)
	at org.apache.openjpa.jdbc.schema.SchemaTool.executeSQL(SchemaTool.java:1375)
	at org.apache.openjpa.jdbc.schema.SchemaTool.createTable(SchemaTool.java:1114)
	at org.apache.openjpa.jdbc.schema.SchemaTool.buildSchema(SchemaTool.java:660)
	at org.apache.openjpa.jdbc.schema.SchemaTool.add(SchemaTool.java:565)
	at org.apache.openjpa.jdbc.schema.SchemaTool.add(SchemaTool.java:400)
	at org.apache.openjpa.jdbc.schema.SchemaTool.run(SchemaTool.java:372)
	at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:571)
	... 19 common frames omitted`

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 22 Jul 2025 15:08:05 +0000

Can you share a reproducer?

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Fri, 25 Jul 2025 14:13:31 +0000

Hello @vlsi 
I've created a reproducer but the result is not the one expected. If I run the same code with the same cots version on a newly created database I have no issue, but If I run it on the existing database I get the issue. If it does not make you thinking of the root cause, I can try to dump the database and to include it in the reproducer. What do you think ?
Many thanks.
Fred

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 25 Jul 2025 14:20:17 +0000

If database dump (e.g. schema-only dump) can reproduce the issue, it is fine. It might indeed help to narrow down the cause

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Mon, 28 Jul 2025 13:29:15 +0000

Hello @vlsi 
I finally succeeded in creating a reproducer :
[Z_JDBC_POSTGRESQL_DRIVER_ISSUE.zip](https://github.com/user-attachments/files/21470263/Z_JDBC_POSTGRESQL_DRIVER_ISSUE.zip)
I understand the root cause, but I don't know if it's directly or indirectly caused by the versions: postgresql-42.7.5, 6 and 7.
The cause of the problem is that I'm using uppercase letters to name the database and the jdbc driver is affected when it searches for database tables and, as a result, JPA tries to create tables that already exist when it's configured to automatically build the database schema (persistence.xml). Can you confirm ?
The issue is simple to reproduce, 
- create a database with lowercase run java program to build the schema
- run again the program to create the entity manager
- rename the database using uppercase
- run again the program that will reproduce the issue and generate exception

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 29 Jul 2025 19:09:47 +0000

Using uppercase names in PostgreSQL is an antipattern.

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Tue, 29 Jul 2025 22:27:11 +0000

Hi @davecramer 
Thanks for your message. 
The root cause of that issue seems to be the same as the post : https://github.com/pgjdbc/pgjdbc/issues/3722 opened by @frederikz
I hope it will be fixed.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 30 Jul 2025 21:35:27 +0000

Yes, the database accepts UPPERCASE names, but by default it folds to lower case. Either way, I'll have a look

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 30 Jul 2025 22:33:00 +0000

Interesting, so I was able to replicate the issue once I figured out how to run this on a mac. I suspect the problem is that we now filter meta-data by database name

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 31 Jul 2025 10:05:45 +0000

@monclaf OK, as I suspected in 42.7.5 we changed the code to make sure the catalog was the same as the current database. This is consistent with the JDBC spec. 
It looks like if you want to use an upper case database name you need to use delimiters around it. 
See https://github.com/apache/openjpa/blob/87e253b9e58a8f061b0431a1ab4df3e5e1660519/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/identifier/DBIdentifierUtilImpl.java#L407-L431

If there are no delimiters it folds the name to lowercase

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Thu, 31 Jul 2025 12:23:39 +0000

Hi @davecramer 
Many thanks for your reply. Does it mean that I can setup openjps.jdbc schema case property in persistence.xml to "preserve" ? What king of delimiter can i use it ? Do you have an example ? Thanks

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 31 Jul 2025 22:10:00 +0000

I'm not familiar with openjpa options. The delimiter they are talking about is `"` Postgres uses `"` to enforce case.

Let me know how this works for you. Honestly I think it's an issue with openjpa. They should not be converting it to lowercase.

See https://github.com/pgjdbc/pgjdbc/issues/3722 looks like spring has modified their code to work properly

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Mon, 18 Aug 2025 09:46:13 +0000

Hi @davecramer 
I contacted openjpa support and I tried the following parameter ;
`<property name="openjpa.jdbc.DBDictionary" value="postgres(schemaCase=preserve)"/>`
But it still doesn't work since your last change.
Do you have another idea to solve the issue ?
Thanks in advance,
Fred

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 18 Aug 2025 10:42:55 +0000

It is their bug. They need to make sure they preserve case for postgresql. What was their response ?

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Mon, 18 Aug 2025 12:58:24 +0000

I don't have any response yet

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Mon, 18 Aug 2025 16:03:32 +0000

Hi @davecramer,

I was able to reproduce the issue with version v42.7.4 by setting values 'preserve' in property value :
`<property name="openjpa.jdbc.DBDictionary" value="postgres(schemaCase=preserve)"/>`

In fact the default schema case value is : "lower". So if I use this value with this property in v42.7.4 it works, but after this version is not work anymore. Do you have an idea why, by setting whatever value to schemaCase the issue happend from newer version than v42.7.4 ?

Thanks,
Fred

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 18 Aug 2025 19:19:39 +0000

Our code was changed https://github.com/pgjdbc/pgjdbc/commit/7c49f620be9914c4e26a827f08939467af8e93e5#diff-0571f8ac3385a7f7bb34e5c77f8afd24810311506989379c2e85c6c16eea6ce4L1195-R1275

We now check to make sure the catalog is the same as the current database.

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Tue, 19 Aug 2025 15:25:18 +0000

Hi @davecramer

I think I've found the issue in openjpa. I did compare version v42.7.4 and v42.7.5 and I noticed that a filter was added in the getColumns based on sql function 'current_database()' that always return value with case sensitive. The jdbc property 'schemaCase' set to 'preserve' in openjpa (via persistence.xml) change the tablename and force it to uppercase.  So when in v42.7.4 you have only one filter 'lower' (default value) is working for tablename filter, but in v42.7.5, 'lower' value does not work for 'current_database()' test with 'catalog' that is case depending of schemaCase, and 'preserve' and 'upper' values make tablename filter fails. (in my case table name is uppercase and tablename is lowercase (automatic generation)). So I wait for confirmation from openjpa to open a ticket on their side.

<img width="1860" height="844" alt="Image" src="https://github.com/user-attachments/assets/c11a53a2-5903-4483-b059-cb493a26c6ad" />

>From these analysis, even, if there is an issue on openjpa, I wonder if it would be another issue on PostgreSQL JDBC driver, I explain. In the following test of the getColums method of PgDatabaseMetaData :
`    if (catalog != null) {
      sql += " AND current_database() = " + escapeQuotes(catalog);
    }`
currrent_database() result from PosgreSQL database is always case sensitive, but because 'catalog' is schema case dependant it will match 'current_database()' only if 'preserve' schema case is set, otherwise it will depend on the fact the database name is all upper case or all lower case. Wouldn't be better to get the 'current_database()' value and to apply schema case on it before to execute the query ?

Thanks,
Fred

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 19 Aug 2025 15:31:50 +0000

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.

---

From: "monclaf (@monclaf)" <[email-masked]>
Date: Tue, 19 Aug 2025 16:04:05 +0000

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 ?
Fred

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 20 Aug 2025 09:16:31 +0000

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`