Hello,
Do you confirm if it is a bug to fix and if yes, can you open an issue on this
point because I have no access to Jira.
Thanks
Fred
-----Message d'origine-----
De : MONCLAR Frederic
Envoyé : mardi 19 août 2025 17:02
À : dev@openjpa.apache.org
Objet : RE: how to get database name case preserved in url of ConnectionURL
property
Critère de diffusion : Confidentiel
Hello,
First point : using openjpa-all-4.1.1 and PostgreSQL JDBC Driver v42.7.4 there
is no issue only if I use <property name="openjpa.jdbc.DBDictionary"
value="postgres(schemaCase=lower)"/> or if I don’t specify this parameter
because 'lower' value is the default value. When 'preserve' is set, the '
tableNamePattern' in the JDBC Driver method getColumns of the class
PgDatabaseMetaData fails because it is written in uppercase and in PostgreSQL
database it is in lowercase (automatic shema generation), see extract code
below where the sql request execution result is empty (no column found => then
try to create tables) :
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
In my case, database name is written in uppercase and table name are written in
lowercase. In my database (v17.2) 'relname' in pg_catalog.pg_class is always in
lowercase, I don’t understand why if we use preserve, the value is modified to
uppercase.
Second point : using openjpa-all-4.1.1 and PostgreSQL JDBC Driver v42.7.5, the
sql function 'current_database()' has been introduce and a new test has been
added base on it :
if (catalog != null) {
sql += " AND current_database() = " + escapeQuotes(catalog);
}
The query 'getColumns' fails in that case because in the where condition we
test database name and table name where
- if you set 'lower' the dataset name test fails : current_database() = " +
escapeQuotes(catalog) because current_database is always preserving the case
- and if you put 'preserve' or 'upper' the sql filter fails : c.relname LIKE "
+ escapeQuotes(tableNamePattern)
I did a compare between v42.7.4 and v42.7.5 to highlight the changes that
generate that issue that youcan find here :
https://github.com/pgjdbc/pgjdbc/compare/REL42.7.4...REL42.7.5
In conlusion, it seems that there is an issue in setting 'preserve' in
<property name="openjpa.jdbc.DBDictionary"
value="postgres(schemaCase=preserve)"/>. It is like it will force to uppercase
in all cases.
Fred
-----Message d'origine-----
De : Romain Manni-Bucau <rmannibu...@gmail.com> Envoyé : mardi 19 août 2025
11:24 À : dev@openjpa.apache.org Cc : MONCLAR Frederic
<frederic.monc...@thalesaleniaspace.com>
Objet : Re: how to get database name case preserved in url of ConnectionURL
property
some comments inline
Le mar. 19 août 2025 à 11:20, Maxim Solodovnik <solomax...@gmail.com> a écrit :
> Hello Romain,
>
> On Tue, 19 Aug 2025 at 15:15, Romain Manni-Bucau
> <rmannibu...@gmail.com>
> wrote:
> >
> > Hi,
> >
> > schemaCase is mainly for the database metadata - column names for
> > ex, so has no impact on the connection url itself normally.
> > The jdbc url case is normally passthrough.
> >
> > However you do setup buildSchema: <property
> > name="openjpa.jdbc.SynchronizeMappings"
> > value="buildSchema(foreignKeys=true)" />
> >
> > So schema factory (native) is skipped to be replaced by dynamic
> > behavior
> so
> > long story short the table is always assumed missing. You can tune
> > it to
> be
> > *refresh* instead of *buildSchema * to ensure you do use native mode.
>
> `refresh` behave the same way `buildSchema` does
>
Would have been great but not in the code, if you setup buildSchema you do use
"dynamic" schema discovery (partly at least) and do not respect the 'native'
configuration.
It is invisible most of the time but a configuration conflict which can have
side effects.
>
> from what I can see Postgres17 always creates DB Table in lower case
> and made it accessible in-ignore-case mode
>
Ack
>
> same time schema tool executes following SQL to check if table already
> exists:
>
> SELECT * FROM (SELECT current_database() AS current_database,
> n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype =
> 'd' AND t.typnotnull) AS
> attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number() OVER
> (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum,
> nullif(a.attidentity, '') as attidentity,nullif(a.attgenerated, '') as
> attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS
> adsrc,dsc.description,t.typbasetype,t.typtype FROM
> pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON
> (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
> LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND
> a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON
> (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN
> pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
> dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON
> (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE c.relkind
> in ('r','p','v','f','m') and a.attnum > 0 AND NOT a.attisdropped AND
> c.relname LIKE 'postgre17_test') c WHERE true ORDER BY
> nspname,c.relname,attnum;
>
> it has `c.relname LIKE 'postgre17_test'` part which is case sensitive
>
but we do reprocess it a lot so until you explicit the configuration to match
1-1 it can sadly fail
>
> >
> > However there are a lot of toggles involved there:
> >
> > <property name="openjpa.jdbc.SynchronizeMappings"
> > value="buildSchema(readSchema=true,foreignKeys=true)" /> <property
> > name="openjpa.jdbc.SchemaFactory"
> value="native(foreignKeys=true)" />
> > <property name="openjpa.jdbc.DBDictionary"
> > value="postgres(schemaCase=preserve,supportsSchemaForGetColumns=fals
> > e)"/>
> > <property name="openjpa.Sequence"
> > value="table(Table=openjpa_sequence_table, Increment=1)"/> <property
> > name="openjpa.jdbc.Schema" value="public"/>
> >
> >
> > to show a few which impact this discovery
> >
> > But the root issue is that schema and table names are treated the
> > same so until you do @Table on all entities to comply to what the
> > dictionary "generates" and explicit the table (openjpa.Sequence) I
> > fear you will
> need
> > your own dictionary to workaround this posgres driver regression.
> >
> > On the long run we can want to do a few changes at openjpa level:
> >
> > * ensure we have a case configuration for table and for schema but
> > not
> the
> > same one
> > * do the test in schema case insensitively for postgres since it is
> > case sensitive but behaves as being insensitive
> >
> > If you are motiviated to do a PR we would be happy to review it or
> > guide you to do it.
>
> I'm not sure I'll have enough free time to "collect this puzzle", but
> i'll plan to check the code again (at least once :))
>
> >
> > 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-9781
> 788473064
> >
> > Javaccino founder (Java/.NET service - contact via linkedin)
> >
> >
> > Le mar. 19 août 2025 à 07:12, Maxim Solodovnik
> > <solomax...@gmail.com> a écrit :
> >
> > > Hello Fred,
> > >
> > > sorry for the late responses (my free time is limited :(( ) I'll
> > > try to create reproducer for your case locally and will report
> > > back to this thread :)
> > >
> > > On Mon, 18 Aug 2025 at 16:52, MONCLAR Frederic
> > > <frederic.monc...@thalesaleniaspace.com> wrote:
> > > >
> > > > Hi Maxim,
> > > >
> > > > I saw that openjpa v4.1.1 was not verified with PostgreSQL v17
> > > > and
> > > PostgreSQL JDBC Driver v42.7.5 :
> > > > (PostgreSQL 8.3.5, 8.4, 9, 11, 12, 13 / PostgreSQL Native
> > > Driver 8.3 JDBC3 (build 603), 8.4 JDBC3 (build 701), 42.2.5, ,
> 42.2.19)
> > > > So do you think that it can explain why this property does not
> > > > work
> in
> > > my case ?
> > > > Thanks
> > > > Fred
> > > >
> > > > -----Message d'origine-----
> > > > De : MONCLAR Frederic
> > > > Envoyé : jeudi 14 août 2025 09:35 À : 'Maxim Solodovnik'
> > > > <solomax...@gmail.com> Cc : dev@openjpa.apache.org Objet : RE:
> > > > how to get database name case preserved in url of
> > > ConnectionURL property
> > > > Critère de diffusion : Confidentiel
> > > >
> > > > Hi Maxim,
> > > > Thank you for your reply.
> > > > I attach the picture to my reply in order to make it visible.
> > > > This
> image
> > > is a capture of the code changes in PostgreSQL JDBC Driver v42.7.5
> > > that create my issue. It is annoying that the JDBC driver has
> > > evolved like
> that
> > > on a minor version.
> > > > I tried what you proposed : <property
> name="openjpa.jdbc.DBDictionary"
> > > value="postgres(schemaCase=preserve)"/> but it does not work. I
> > > still
> get
> > > the error where the lib try to create tables like if the table
> > > does not exists :-( Fred
> > > >
> > > > 14-08-2025 09:32:10.317 [main] INFO
> > > [d.t.e.APersistencedb][openPhaseRemote]
> > > {openjpa.ConnectionURL=jdbc:postgresql://localhost:9999/DB_TEST,
> > > openjpa.ConnectionUserName=postgres,
> openjpa.ConnectionPassword=postgres}
> > > > 14-08-2025 09:32:12.031 [main] ERROR
> > > [d.t.e.APersistencedb][openPhaseRemote] ERREUR: la relation «
> > > openjpa_sequence_table » existe déjà {stmnt 971404566 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 971404566 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:6
> > > 25)
> > > > at
> > > org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:4
> > > 88)
> > > > at
> > >
> org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(J
> DBCBrokerFactory.java:173)
> > > > at
> > >
> org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(J
> DBCBrokerFactory.java:178)
> > > >
> > > > -----Message d'origine-----
> > > > De : Maxim Solodovnik <solomax...@gmail.com> Envoyé : jeudi 14
> > > > août
> > > 2025 06:46 À : MONCLAR Frederic <
> frederic.monc...@thalesaleniaspace.com>
> > > > Cc : dev@openjpa.apache.org
> > > > Objet : Re: how to get database name case preserved in url of
> > > ConnectionURL property
> > > >
> > > > re-sending with modified email of TS :)
> > > >
> > > > On Thu, 14 Aug 2025 at 11:44, Maxim Solodovnik
> > > > <solomax...@gmail.com
> >
> > > wrote:
> > > > >
> > > > > Hello Fred,
> > > > >
> > > > > welcome to the list :)
> > > > >
> > > > >
> > > > > On Wed, 13 Aug 2025 at 13:35, MONCLAR Frederic
> > > > > <frederic.monc...@thalesaleniaspace.com.invalid> wrote:
> > > > > >
> > > > > > Hello,
> > > > > >
> > > > > >
> > > > > >
> > > > > > I would like to know if there is an option in
> > > > > > persistence.xml or
> in
> > > creating entity manager factory methods where I can specify to use
> > > “CASE_PRESERVE” in order to preserve the case of the name of the
> database
> > > in ConnectionURL property?
> > > > > >
> > > > >
> > > > > It seems to me this question better fits users@openjpa.a.o
> > > > > mailing-list :))
> > > > >
> > > > > >
> > > > > >
> > > > > > I recently posted an issue on PostgreSQL JDBC Driver
> > > > > > v42.7.5/6/7
> :
> > > https://github.com/pgjdbc/pgjdbc/issues/3731 because I get the
> following
> > > exception :
> > > > > >
> > > > > >
> > > > >
> > > > > I would try to set
> > > > >
> > > > > openjpa.jdbc.DBDictionary: postgres(schemaCase=preserve)
> > > > >
> > > > > like this:
> > > > >
> > > > > <properties>
> > > > > <property name="openjpa.jdbc.DBDictionary"
> > > > > value="postgres(schemaCase=preserve)" /> </properties>
> > > > >
> > > > >
> > > > > >
> > > > > > 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
> > > > > > )
> > > > > >
> > > > > >
> > > > > >
> > > > > > when I try to create an EntityManager and it was working
> > > > > > fine
> until
> > > version 42.7.4 :
> > > > > >
> > > > > >
> > > > > >
> > > > > > String dbUrl = "jdbc:postgresql://" +
> > > > > > "localhost:9999/\"DB_TEST\"";
> > > > > >
> > > > > > properties.put("openjpa.ConnectionURL", dbUrl);
> > > > > >
> > > > > > properties.put("openjpa.ConnectionUserName",
> > > > > > "postgres");
> > > > > >
> > > > > > properties.put("openjpa.ConnectionPassword",
> > > > > > "postgres");
> > > > > >
> > > > > >
> > > > > >
> > > > > > EntityManagerFactory emf =
> > > > > > Persistence.createEntityManagerFactory("test_db",
> > > > > > properties);
> > > > > >
> > > > > > EntityManager em = emf.createEntityManager();
> > > > > >
> > > > > >
> > > > > >
> > > > > > PostgreSQL JDBC Driver support explained that they did the
> following
> > > change in v42.7.5 :
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> https://github.com/apache/openjpa/blob/87e253b9e58a8f061b0431a1ab4df
> > > > > >
> 3e5e1660519/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/ident
> > > > > > ifier/DBIdentifierUtilImpl.java#L407-L431
> > > > > >
> > > > > >
> > > > >
> > > > > Unfortunately your image was not delivered to the list Please
> upload
> > > > > it to some image-sharing-service and send URL :))
> > > > >
> > > > > >
> > > > > > and since, I can’t use uppercase for the name of the
> > > > > > database- L
> > > > > >
> > > > > > They proposes to find a way in openjpa to preserve case for
> database
> > > name.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Thanks for any help.
> > > > > >
> > > > > > Fred
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Best regards,
> > > > > Maxim
> > > >
> > > >
> > > >
> > > > --
> > > > Best regards,
> > > > Maxim
> > >
> > >
> > >
> > > --
> > > Best regards,
> > > Maxim
> > >
>
>
>
> --
> Best regards,
> Maxim
>
