pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
10+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-09 14:44 "rtrier (@rtrier)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-09 14:44 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
After creation of a databaseConnection I call addDataType multible times
`pgconn.addDataType("\"xplan_gml\".\"xp_gemeinde\"", Gemeinde.class);`
`pgconn.addDataType("\"xplan_gml\".\"xp_planaufstellendegemeinde\"", PlanaufstellendeGemeinde.class);`
`pgconn.addDataType("\"xplan_gml\".\"xp_spezexternereferenz\"", PGExterneReferenz.class);`
`pgconn.addDataType("\"xplan_gml\".\"so_planart\"", PG_SO_Planart.class);`
`pgconn.addDataType("\"xplan_gml\".\"bp_status\"", CodeList.class);`
`pgconn.addDataType("\"bp_status\"", CodeList.class);`
If I make a select Statement the MetaData of the ResultSet is correct:
`ColumnName Type ColumnClassName ColumnTypeName`
`gml_id 1111 java.util.UUID uuid`
`name 12 java.lang.String varchar`
`nummer 12 java.lang.String varchar`
`gemeinde 2003 java.sql.Array "xplan_gml"."_xp_gemeinde"`
`externereferenz 2003 java.sql.Array "xplankonverter"."_xp_spezexternereferenzauslegung"`
`inkrafttretensdatum 91 java.sql.Date date`
`auslegungsstartdatum 2003 java.sql.Array _date`
`auslegungsenddatum 2003 java.sql.Array _date`
`rechtsstand 12 java.lang.String "xplan_gml"."bp_rechtsstand"`
`planart 2003 java.sql.Array "xplan_gml"."_bp_planart"`
`raeumlichergeltungsbereich 1111 org.postgis.jts.JtsGeometry geometry`
`konvertierung_id 4 java.lang.Integer int4`
`internalid 12 java.lang.String varchar`
`aendert 2003 java.sql.Array "xplan_gml"."_xp_verbundenerplan"`
`wurdegeaendertvon 2003 java.sql.Array "xplan_gml"."_xp_verbundenerplan"`
`status 2002 de.gdiservice.bplan.CodeList "xplan_gml"."bp_status"`
`verfahren 12 java.lang.String "xplan_gml"."bp_verfahren"`
`untergangsdatum 91 java.sql.Date date`
`genehmigungsdatum 91 java.sql.Date date`
`gruenordnungsplan -7 java.lang.Boolean bool`
`ausfertigungsdatum 91 java.sql.Date date`
`durchfuehrungsvertrag -7 java.lang.Boolean bool`
`erschliessungsvertrag -7 java.lang.Boolean bool`
`rechtsverordnungsdatum 91 java.sql.Date date`
`satzungsbeschlussdatum 91 java.sql.Date date`
`staedtebaulichervertrag -7 java.lang.Boolean bool`
`planaufstellendegemeinde 2003 java.sql.Array "xplan_gml"."_xp_planaufstellendegemeinde"`
`veraenderungssperredatum 91 java.sql.Date date`
`aufstellungsbeschlussdatum 91 java.sql.Date date`
`traegerbeteiligungsenddatum 2003 java.sql.Array _date`
`veraenderungssperreenddatum 91 java.sql.Date date`
`traegerbeteiligungsstartdatum 2003 java.sql.Array _date`
`verlaengerungveraenderungssperre 12 java.lang.String "xplan_gml"."xp_verlaengerungveraenderungssperre"`
`veraenderungssperrebeschlussdatum 91 java.sql.Date date`
If I insert into the table a row, which failed because the count of values in the column gemeinde is not correct, I get an exception as expected. so far so good.
If I call with the same connection a select statement the MetaData looks like:
`ColumnName Type ColumnClassName ColumnTypeName`
`gml_id 1111 java.util.UUID uuid`
`name 12 java.lang.String varchar`
`nummer 12 java.lang.String varchar`
`gemeinde 2003 java.sql.Array _xp_gemeinde`
`externereferenz 2003 java.sql.Array _xp_spezexternereferenzauslegung`
`inkrafttretensdatum 91 java.sql.Date date`
`auslegungsstartdatum 2003 java.sql.Array _date`
`auslegungsenddatum 2003 java.sql.Array _date`
`rechtsstand 12 java.lang.String bp_rechtsstand`
`planart 2003 java.sql.Array _bp_planart`
`raeumlichergeltungsbereich 1111 org.postgis.jts.JtsGeometry geometry`
`konvertierung_id 4 java.lang.Integer int4`
`internalid 12 java.lang.String varchar`
`aendert 2003 java.sql.Array _xp_verbundenerplan`
`wurdegeaendertvon 2003 java.sql.Array _xp_verbundenerplan`
`status 2002 java.lang.String bp_status`
`verfahren 12 java.lang.String bp_verfahren`
`untergangsdatum 91 java.sql.Date date`
`genehmigungsdatum 91 java.sql.Date date`
`gruenordnungsplan -7 java.lang.Boolean bool`
`ausfertigungsdatum 91 java.sql.Date date`
`durchfuehrungsvertrag -7 java.lang.Boolean bool`
`erschliessungsvertrag -7 java.lang.Boolean bool`
`rechtsverordnungsdatum 91 java.sql.Date date`
`satzungsbeschlussdatum 91 java.sql.Date date`
`staedtebaulichervertrag -7 java.lang.Boolean bool`
`planaufstellendegemeinde 2003 java.sql.Array "xplan_gml"."_xp_planaufstellendegemeinde"`
`veraenderungssperredatum 91 java.sql.Date date`
`aufstellungsbeschlussdatum 91 java.sql.Date date`
`traegerbeteiligungsenddatum 2003 java.sql.Array _date`
`veraenderungssperreenddatum 91 java.sql.Date date`
`traegerbeteiligungsstartdatum 2003 java.sql.Array _date`
`verlaengerungveraenderungssperre 12 java.lang.String "xplan_gml"."xp_verlaengerungveraenderungssperre"`
`veraenderungssperrebeschlussdatum 91 java.sql.Date date`
At the end this leads that the status for instance is not from Type de.gdiservice.bplan.CodeList.
**Driver Version?**
JDBC 42.6.0 and 42.7.5
**Java Version?**
11
**OS Version?**
Windows 10
**PostgreSQL Version?**
"PostgreSQL 16.8 (Debian 16.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit"
[metadata-ok.txt](https://github.com/user-attachments/files/19150262/metadata-ok.txt)
[metadata-withErrors.txt](https://github.com/user-attachments/files/19150261/metadata-withErrors.txt)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-09 21:28 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2025-03-09 21:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
A reproducer would be useful.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 17:05 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-12 17:05 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I struggle now for some days. I not able to find a simple reproducer, a reduced version. Kind of one statement, second statement and .. voila. But without success.
That's why I started to debug. Looks like the problem is in the class org.postgresql.jdbc.TypeInfoCache. It has sth. to do with overwriting in the HashMaps of the TypeInfoClass if an type is used as an Array and as well as not as an Array.
I added a lot Log-Statements to TypeInfoClass. But is hard for me to follow, what triggers the Type-Queries to the database.
I could export the DataBase and checkin my Project (github) and explain how to reproduce the error.
@davecramer what do you think, or can we chat
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 18:57 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2025-03-12 18:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Can you show me the line where this happens. It is easy to copy a line from github
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 19:01 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-12 19:01 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
the Insert PreparedStatment leads that TypeInfoCache.getPGType(String) is called prior TypeInfoCache.getPGType(int)
BAD:
getPGType(""xplan_gml"."bp_status"")
at org.postgresql.jdbc.TypeInfoCache.getPGType(TypeInfoCache.java:542)
at org.postgresql.jdbc.PgPreparedStatement.setPGobject(PgPreparedStatement.java:523)
at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1037)
at de.gdiservice.bplan.BPlanDAO.setSQLParameter(BPlanDAO.java:172)
at de.gdiservice.bplan.BPlanDAO.insert(BPlanDAO.java:246)
at de.gdiservice.bplan.BPlanWriteReadTest.runGeoplex(BPlanWriteReadTest.java:123)
at de.gdiservice.bplan.BPlanWriteReadTest.main(BPlanWriteReadTest.java:164)
getPGType("xplan_gml"."bp_status") => not found in Cache
It set then the HashSet oidToPgName and pgNameToOid
getPGType oidToPgName.put(119193, bp_status)
getPGType pgNameToOid.put(bp_status, 119193)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 19:02 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-12 19:02 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
If read getPGType(int) is called first:
getPGType(119193)
at org.postgresql.jdbc.TypeInfoCache.getPGType(TypeInfoCache.java:582)
at org.postgresql.jdbc.PgResultSet.initSqlType(PgResultSet.java:3224)
at org.postgresql.jdbc.PgResultSet.getSQLType(PgResultSet.java:3213)
at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:204)
at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3072)
at de.gdiservice.bplan.BPlanDAO.createBPlan(BPlanDAO.java:423)
at de.gdiservice.bplan.BPlanDAO.findById(BPlanDAO.java:295)
at de.gdiservice.bplan.BPlanWriteReadTest.runGeoplex(BPlanWriteReadTest.java:110)
at de.gdiservice.bplan.BPlanWriteReadTest.main(BPlanWriteReadTest.java:164)
getPGType(119193)=> not found
Setting
getPGType(119193)=> onPath=false => " pgTypeName=""xplan_gml"."bp_status""
getPGType(119193) onPath=false => pgNameToOid.put(xplan_gml.bp_status, 119193)
getPGType(119193)=> pgNameToOid.put("xplan_gml"."bp_status", 119193)
getPGType(119193)=> pgNameToOid.put(119193, "xplan_gml"."bp_status")
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 19:06 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-12 19:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
the reason ist getPGType(string) setting the values in the HasMaps different the getPGType(int)
As I tried to reduce it I had not used a PreparedStatment to write.
stmt.setObject triggers getPGType(String)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-12 20:20 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-12 20:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
ready for reproduce:
1. CodeList and JdbcTest are Java-Files
2. adjust DB-Parameter in JdbcTest.main
3. run JdbcTest.main with Line 78 `runInsert(con, uuid);` commented/uncommented
if run with the insert statement the value of status is not an instance of CodeList as expected
As I tried to explain: through the insert statement `TypeInfoCache.getPGType(String)` is called and is setting the cache stuff different then by `TypeInfoCache.getPGType(int)`
[CodeList.txt](https://github.com/user-attachments/files/19217150/CodeList.txt)
[JdbcTest.txt](https://github.com/user-attachments/files/19217151/JdbcTest.txt)
[sql.txt](https://github.com/user-attachments/files/19217149/sql.txt)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-13 07:26 ` "rtrier (@rtrier)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: rtrier (@rtrier) @ 2025-03-13 07:26 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Summary**
I have the table
`CREATE TABLE IF NOT EXISTS xplan_gml.jdbctest`
`(`
` gml_id uuid,`
` status xplan_gml.bp_status`
`);`
and registering
`pgconn.addDataType("\"xplan_gml\".\"bp_status\"", CodeList.class); `
if Inserting a record with the PreparedStatement
`"INSERT INTO xplan_gml.jdbctest(gml_id, status) values(?, ?)";`
before querying with
`Select gml_id, status from xplan_gml.jdbctest where gml_id = ?`
then
the type of the status object is not an instance of the class CodeList as expected.
If I run the query without an preceding insert statement the status object is an instance of CodeList.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type
@ 2025-03-13 15:29 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2025-03-13 15:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@rtrier thanks, I'll try to get to this soon. I'm a bit overwhelmed at the moment
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-03-13 15:29 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-09 14:44 [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type "rtrier (@rtrier)" <[email protected]>
2025-03-09 21:28 ` "davecramer (@davecramer)" <[email protected]>
2025-03-12 17:05 ` "rtrier (@rtrier)" <[email protected]>
2025-03-12 18:57 ` "davecramer (@davecramer)" <[email protected]>
2025-03-12 19:01 ` "rtrier (@rtrier)" <[email protected]>
2025-03-12 19:02 ` "rtrier (@rtrier)" <[email protected]>
2025-03-12 19:06 ` "rtrier (@rtrier)" <[email protected]>
2025-03-12 20:20 ` "rtrier (@rtrier)" <[email protected]>
2025-03-13 07:26 ` "rtrier (@rtrier)" <[email protected]>
2025-03-13 15:29 ` "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