pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
21+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-22 12:49 "monclaf (@monclaf)" <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-07-22 12:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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`
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-22 15:08 ` "vlsi (@vlsi)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: vlsi (@vlsi) @ 2025-07-22 15:08 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Can you share a reproducer?
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-25 14:13 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-07-25 14:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-25 14:20 ` "vlsi (@vlsi)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: vlsi (@vlsi) @ 2025-07-25 14:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
If database dump (e.g. schema-only dump) can reproduce the issue, it is fine. It might indeed help to narrow down the cause
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-28 13:29 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-07-28 13:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-29 19:09 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-07-29 19:09 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Using uppercase names in PostgreSQL is an antipattern.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-29 22:27 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-07-29 22:27 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-30 21:35 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-07-30 21:35 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Yes, the database accepts UPPERCASE names, but by default it folds to lower case. Either way, I'll have a look
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-30 22:33 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-07-30 22:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-31 10:05 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-07-31 10:05 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@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/mai...
If there are no delimiters it folds the name to lowercase
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-31 12:23 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-07-31 12:23 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-07-31 22:10 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-07-31 22:10 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-18 09:46 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-08-18 09:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-18 10:42 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-08-18 10:42 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
It is their bug. They need to make sure they preserve case for postgresql. What was their response ?
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-18 12:58 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-08-18 12:58 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I don't have any response yet
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-18 16:03 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-08-18 16:03 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-18 19:19 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-08-18 19:19 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Our code was changed https://github.com/pgjdbc/pgjdbc/commit/7c49f620be9914c4e26a827f08939467af8e93e5#diff-0571f8ac3385a7...
We now check to make sure the catalog is the same as the current database.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-19 15:25 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-08-19 15:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-19 15:31 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-08-19 15:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-19 16:04 ` "monclaf (@monclaf)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: monclaf (@monclaf) @ 2025-08-19 16:04 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4
@ 2025-08-20 09:16 ` "davecramer (@davecramer)" <[email protected]>
19 siblings, 0 replies; 21+ messages in thread
From: davecramer (@davecramer) @ 2025-08-20 09:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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`
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2025-08-20 09:16 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-22 12:49 [pgjdbc/pgjdbc] issue #3731: Entity Manager Creation issue with jdbc driver > 42.7.4 "monclaf (@monclaf)" <[email protected]>
2025-07-22 15:08 ` "vlsi (@vlsi)" <[email protected]>
2025-07-25 14:13 ` "monclaf (@monclaf)" <[email protected]>
2025-07-25 14:20 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 13:29 ` "monclaf (@monclaf)" <[email protected]>
2025-07-29 19:09 ` "davecramer (@davecramer)" <[email protected]>
2025-07-29 22:27 ` "monclaf (@monclaf)" <[email protected]>
2025-07-30 21:35 ` "davecramer (@davecramer)" <[email protected]>
2025-07-30 22:33 ` "davecramer (@davecramer)" <[email protected]>
2025-07-31 10:05 ` "davecramer (@davecramer)" <[email protected]>
2025-07-31 12:23 ` "monclaf (@monclaf)" <[email protected]>
2025-07-31 22:10 ` "davecramer (@davecramer)" <[email protected]>
2025-08-18 09:46 ` "monclaf (@monclaf)" <[email protected]>
2025-08-18 10:42 ` "davecramer (@davecramer)" <[email protected]>
2025-08-18 12:58 ` "monclaf (@monclaf)" <[email protected]>
2025-08-18 16:03 ` "monclaf (@monclaf)" <[email protected]>
2025-08-18 19:19 ` "davecramer (@davecramer)" <[email protected]>
2025-08-19 15:25 ` "monclaf (@monclaf)" <[email protected]>
2025-08-19 15:31 ` "davecramer (@davecramer)" <[email protected]>
2025-08-19 16:04 ` "monclaf (@monclaf)" <[email protected]>
2025-08-20 09:16 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox