Subject: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable

From: "beikov (@beikov)" <[email-masked]>
Date: Thu, 30 Nov 2023 18:41:37 +0000

**Describe the issue**
Imagine TX1 binding a `PGObject` with type name `mystruct` via `PreparedStatement#setObject()`. This will put the oid of that type into `TypeInfoCache`. When the type gets dropped and a new type with the same name is created, one can never ever bind an object with this type name with this connection, because the oid does not exist anymore. The error I'm seeing is `ERROR: cache lookup failed for type 1234`.

**Driver Version?** 
42.6.0
**Java Version?**
11
**OS Version?**
Linux/Windows
**PostgreSQL Version?**
15
**To Reproduce**
See example.

**Expected behaviour**
Either the driver should handle this situation automatically by retrying the statement after looking up the oid again, or at least the `TypeInfo` or `TypeInfoCache` types should provide a way to clear the cache.

Using the following template code make sure the bug can be replicated in the driver alone.
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class TestNullsFirst {
    public static void main(String []args) throws Exception {

        String url = "jdbc:postgresql://localhost:5432/test";

        Properties props = new Properties();
        props.setProperty("user", "test");
        props.setProperty("password", "test");
        try ( Connection conn = DriverManager.getConnection(url, props) ){
            try ( Statement statement = conn.createStatement() ) {
                statement.execute("create type structType as (v text)");
                statement.execute("create table tbl as (s structType)");
            }
            try ( PreparedStatement statement = conn.prepareStatement( "insert into tbl values (?)" ) ) {
                PGObject o = new PGObject();
                o.setType("structType")
                o.setValue("(\"abc\")")
                statement.setObject( 1, o );
                statement.executeUpdate();
            }
            try ( Statement statement = conn.createStatement() ) {
                statement.execute("drop table tbl");
                statement.execute("drop type structType");
                statement.execute("create type structType as (v text)");
                statement.execute("create table tbl as (s structType)");
            }
            try ( PreparedStatement statement = conn.prepareStatement( "insert into tbl values (?)" ) ) {
                PGObject o = new PGObject();
                o.setType("structType")
                o.setValue("(\"abc\")")
                statement.setObject( 1, o );
                statement.executeUpdate();
            }
        }
    }
}
```

---

From: "beikov (@beikov)" <[email-masked]>
Date: Thu, 30 Nov 2023 19:19:56 +0000

In case anyone else runs into the same issue, here is the code I'm using to clear the cache:

```java
final Class<?> pgConnection = Class.forName( "org.postgresql.jdbc.PgConnection" );
final Object connection = c.unwrap( pgConnection );
final Object typeInfo = pgConnection.getMethod( "getTypeInfo" ).invoke( connection );
final Class<?> typeInfoCacheClass = Class.forName( "org.postgresql.jdbc.TypeInfoCache" );
final Field oidToPgNameField = typeInfoCacheClass.getDeclaredField( "oidToPgName" );
final Field pgNameToOidField = typeInfoCacheClass.getDeclaredField( "pgNameToOid" );
final Field pgNameToSQLTypeField = typeInfoCacheClass.getDeclaredField( "pgNameToSQLType" );
final Field oidToSQLTypeField = typeInfoCacheClass.getDeclaredField( "oidToSQLType" );
oidToPgNameField.setAccessible( true );
pgNameToOidField.setAccessible( true );
pgNameToSQLTypeField.setAccessible( true );
oidToSQLTypeField.setAccessible( true );
//noinspection unchecked
final Map<Integer, String> oidToPgName = (Map<Integer, String>) oidToPgNameField.get( typeInfo );
//noinspection unchecked
final Map<String, Integer> pgNameToOid = (Map<String, Integer>) pgNameToOidField.get( typeInfo );
//noinspection unchecked
final Map<String, Integer> pgNameToSQLType = (Map<String, Integer>) pgNameToSQLTypeField.get( typeInfo );
//noinspection unchecked
final Map<Integer, Integer> oidToSQLType = (Map<Integer, Integer>) oidToSQLTypeField.get( typeInfo );
for ( Iterator<Map.Entry<String, Integer>> iter = pgNameToOid.entrySet().iterator(); iter.hasNext(); ) {
	Map.Entry<String, Integer> entry = iter.next();
	final String typeName = entry.getKey();
	if ( !PGJDBC_STANDARD_TYPE_NAMES.contains( typeName ) ) {
		final Integer oid = entry.getValue();
		oidToPgName.remove( oid );
		oidToSQLType.remove( oid );
		pgNameToSQLType.remove( typeName );
		iter.remove();
	}
}
```

with this utility:

```java
private static final Set<String> PGJDBC_STANDARD_TYPE_NAMES = buildTypeNames( Set.of(
			"int2",
			"int4",
			"oid",
			"int8",
			"money",
			"numeric",
			"float4",
			"float8",
			"char",
			"bpchar",
			"varchar",
			"text",
			"name",
			"bytea",
			"bool",
			"bit",
			"date",
			"time",
			"timetz",
			"timestamp",
			"timestamptz",
			"refcursor",
			"json",
			"jsonb",
			"box",
			"point",
			"uuid",
			"xml"
	) );

	private static Set<String> buildTypeNames(Set<String> baseTypeNames) {
		final HashSet<String> typeNames = new HashSet<>( baseTypeNames.size() * 3 );
		for ( String baseTypeName : baseTypeNames ) {
			typeNames.add( baseTypeName );
			typeNames.add( "_" + baseTypeName );
			typeNames.add( baseTypeName + "[]" );
		}
		return typeNames;
	}
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 01 Dec 2023 11:57:32 +0000

Care to provide a PR ?

---

From: "beikov (@beikov)" <[email-masked]>
Date: Fri, 01 Dec 2023 12:03:56 +0000

First of all I'd like to understand what you think about this problem. Providing a method to clear the type cache is easy enough, but I have no idea how re-attempting statement execution on such an error could work.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 01 Dec 2023 12:08:36 +0000

Fair enough. My first instinct here is to have a way to *NOT* cache an object if we know we are going to change it soon. That said I haven't really given it enough thought.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 01 Dec 2023 12:13:52 +0000

I have not looked into this exact case, however, here's a way re-executing might be implemented: https://github.com/pgjdbc/pgjdbc/pull/451/files#diff-fb626514a44e1fd93551464de0ba369def2b0513a7232ce12d9c3040ea98d211R336

We could probably monitor "drop type" queries, and invalidate the relevant caches automatically. Sure it won't heal all the cases, but it might resolve many issues automatically.

---

From: "beikov (@beikov)" <[email-masked]>
Date: Mon, 04 Dec 2023 09:29:33 +0000

Thanks for linking some example code. I'll take a look as soon as I find some time.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 06 Dec 2023 08:47:48 +0000

```
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="drop type create_drop_struct",oids={})
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=0)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendSync  FE=> Sync
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE ParseComplete [null]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE BindComplete [unnamed]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE NoData
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveCommandStatus  <=BE CommandStatus(DROP TYPE)
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2023-12-06 11:21:34 FINEST QueryExecutorImpl execute   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@26425897, maxRows=0, fetchSize=0, flags=17
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="create type create_drop_struct as (v text)",oids={})
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=0)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendSync  FE=> Sync
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE ParseComplete [null]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE BindComplete [unnamed]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE NoData
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveCommandStatus  <=BE CommandStatus(CREATE TYPE)
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2023-12-06 11:21:34 FINEST QueryExecutorImpl execute   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@73163d48, maxRows=0, fetchSize=0, flags=17
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="create table create_drop_struct_table (s create_drop_struct)",oids={})
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=0)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendSync  FE=> Sync
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE ParseComplete [null]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE BindComplete [unnamed]
2023-12-06 11:21:34 FINEST QueryExecutorImpl processResults  <=BE NoData
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveCommandStatus  <=BE CommandStatus(CREATE TABLE)
2023-12-06 11:21:34 FINEST QueryExecutorImpl receiveRFQ  <=BE ReadyForQuery(I)
2023-12-06 11:21:34 FINEST QueryExecutorImpl execute   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@58c34bb3, maxRows=0, fetchSize=0, flags=21
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendParse  FE=> Parse(stmt=null,query="insert into create_drop_struct_table values ($1)",oids={218711})
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendBind  FE=> Bind(stmt=null,portal=null,$1=<'("abc")'>,type=<unknown:218711>)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendDescribePortal  FE=> Describe(portal=null)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendExecute  FE=> Execute(portal=null,limit=1)
2023-12-06 11:21:34 FINEST QueryExecutorImpl sendSync  FE=> Sync
```

How about invalidating all lazy-loaded type information on every `DROP TYPE`, `CREATE TYPE`, `DROP TABLE`, `CREATE TABLE`?

---

From: "beikov (@beikov)" <[email-masked]>
Date: Wed, 06 Dec 2023 08:49:07 +0000

That's certainly possible, but note that the drop type can happen on other connections as well, so it wouldn't be a full solution.

---

From: "Sanne (@Sanne)" <[email-masked]>
Date: Mon, 30 Jun 2025 13:46:53 +0000

Just wanting to add that if we had a shared cache across all connections similar to #345 the above proposal would be a nice fix.