Subject: [pgjdbc/pgjdbc] issue #3562: After insert with PreparedStatement into table with complex types querying leads to resultset with incorrect type

From: "rtrier (@rtrier)" <[email-masked]>
Date: Sun, 09 Mar 2025 14:44:26 +0000

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)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 09 Mar 2025 21:28:27 +0000

A reproducer would be useful.

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Wed, 12 Mar 2025 17:05:25 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 12 Mar 2025 18:57:24 +0000

Can you show me the line where this happens. It is easy to copy a line from github

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Wed, 12 Mar 2025 19:01:18 +0000

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)

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Wed, 12 Mar 2025 19:02:54 +0000

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")

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Wed, 12 Mar 2025 19:06:37 +0000

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)

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Wed, 12 Mar 2025 20:20:14 +0000

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)

---

From: "rtrier (@rtrier)" <[email-masked]>
Date: Thu, 13 Mar 2025 07:26:58 +0000

**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.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 13 Mar 2025 15:29:01 +0000

@rtrier thanks, I'll try to get to this soon. I'm a bit overwhelmed at the moment