pgjdbc
TypeInfoCache should be clearable/resetable
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();
}
}
}
}
In case anyone else runs into the same issue, here is the code I'm using to clear the cache:
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:
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;
}
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.
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.
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.
Thanks for linking some example code. I'll take a look as soon as I find some time.
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?
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.
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.
