pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
10+ messages / 4 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-11-30 18:41 "beikov (@beikov)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: beikov (@beikov) @ 2023-11-30 18:41 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**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();
}
}
}
}
```
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-11-30 19:19 ` "beikov (@beikov)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: beikov (@beikov) @ 2023-11-30 19:19 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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;
}
```
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-01 11:57 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2023-12-01 11:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Care to provide a PR ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-01 12:03 ` "beikov (@beikov)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: beikov (@beikov) @ 2023-12-01 12:03 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-01 12:08 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2023-12-01 12:08 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-01 12:13 ` "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2023-12-01 12:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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-fb626514a44e1fd93551464de0ba369def2b0513a7232ce...
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-04 09:29 ` "beikov (@beikov)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: beikov (@beikov) @ 2023-12-04 09:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thanks for linking some example code. I'll take a look as soon as I find some time.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-06 08:47 ` "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2023-12-06 08:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
```
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`?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2023-12-06 08:49 ` "beikov (@beikov)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: beikov (@beikov) @ 2023-12-06 08:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable
@ 2025-06-30 13:46 ` "Sanne (@Sanne)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: Sanne (@Sanne) @ 2025-06-30 13:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-06-30 13:46 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-30 18:41 [pgjdbc/pgjdbc] issue #3049: TypeInfoCache should be clearable/resetable "beikov (@beikov)" <[email protected]>
2023-11-30 19:19 ` "beikov (@beikov)" <[email protected]>
2023-12-01 11:57 ` "davecramer (@davecramer)" <[email protected]>
2023-12-01 12:03 ` "beikov (@beikov)" <[email protected]>
2023-12-01 12:08 ` "davecramer (@davecramer)" <[email protected]>
2023-12-01 12:13 ` "vlsi (@vlsi)" <[email protected]>
2023-12-04 09:29 ` "beikov (@beikov)" <[email protected]>
2023-12-06 08:47 ` "vlsi (@vlsi)" <[email protected]>
2023-12-06 08:49 ` "beikov (@beikov)" <[email protected]>
2025-06-30 13:46 ` "Sanne (@Sanne)" <[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