Npgsql.PostgresException: XX000: cache lookup failed

ServiceStack.OrmLite PostgreSQL
1

Having the same error as

And the SO answer says to call NpgsqlConnection.ReloadTypes().

While I don’t think in production we would be dropping schemas, it is a possibility especially early on in this new project and if it happens we don’t want the website going down until we restart the website.

How do you see us implementing a global catchall for that specific error to call NpgsqlConnection.ReloadTypes() and then try again? Note: It’s a generic Npgsql.PostgresException exception, so would need to check the error message text.

our AppHost Configure(Container container) method for registering pgsql looks like:

container.Register<IDbConnectionFactory>(c =>
            {
                var connectionFactory = new OrmLiteConnectionFactory();

                // NOTE: Don't use ConfigUtils, use AppSettings instead.
                connectionFactory.RegisterConnection("mydb", new OrmLiteConnectionFactory(AppSettings.GetString("ConnectionStrings:mydb"), PostgreSqlDialect.Provider, true));

                connectionFactory.RegisterConnection("mydb2", new OrmLiteConnectionFactory(AppSettings.GetString("ConnectionStrings:mydb2"), PostgreSqlDialect.Provider, true));

                connectionFactory.RegisterConnection("mydb3", new OrmLiteConnectionFactory(AppSettings.GetString("ConnectionStrings:mydb3"), PostgreSqlDialect.Provider, true));

                return connectionFactory;
            }).ReusedWithin(ReuseScope.Request);

Also, is it still better to ReuseScope.Request?

Here is the stack trace:

{Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type 54669
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1219
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Action`1 commandFilter, Boolean selectIdentity)
   at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass5_0`1.<Insert>b__0(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
   at MyApp.MyService.Any(MyPageDto request) in C:\...\MyService.cs:line 38}
2

No, never use ReuseScope.Request for singletons.

The Exception talks about creating Types, are you creating custom PostgreSQL Types or just tables? The only Global Exception Filter available in OrmLite is:

OrmLiteConfig.ExceptionFilter = (dbCmd, ex) => ....;

If that doesn’t work you can try asking Npgsql team if there’s a hook you can use available, even better if the cache lookup fails they should reload/check before throwing.

3

Hmmm… I thought I saw somewhere on a thread in these forums to use ReuseScope.Request, anyway we’ll, just remove that part.

It isn’t a custom type. Plain old tables. hstore is used once or twice, but that extension is created in another schema, so I don’t think that would be affected by it.

The schema was dropped and re-created while the website was running to do some db changes. We were surprised that we couldn’t get into the website afterwards and had to track down the error.

I’m not completely sure, but I don’t remember this being an issue with older versions of Npgsql, but I could be wrong.

4

Not sure if you’re talking about an entire schema or just a table as you can definitely drop/re-create tables without issue (which is what many of OrmLite’s integration tests do).

You’ll never hear recommendation to use RequestScope from me, It’s unpredictable given it has to be implemented differently in every host and harder to test as only useful in the context of a request. I only use Transient or Singleton scopes and given our dependencies with resources are pooled, there’s no good reason to use RequestScope.

5

Drop a schema DROP SCHEMA IF EXISTS myschema; and then recreate the schema and all the tables will cause the issue while the website has been running.

Similar to https://stackoverflow.com/questions/51086421/npgsql-postgresexception-0x80004005-xx000-cache-lookup-failed-for-type-20785

6

For clarification there should be no .ReusedWithin(...) for registering the IDbConnectionFactory. Remove that part and we should be good to go?

7

Yeah so you are talking about an entire SCHEMA, the solution might then be don’t do it. Drop all the tables individually and re-create them instead.

8

Yes, ServiceStack’s IOC is singleton scope by default.

9

For reference, I’ve created the Npgsql issue Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type · Issue #2366 · npgsql/npgsql · GitHub

I think your right and it should be handled by Npgsql. We’ll see what they say.

We can’t guarantee that someone wouldn’t do that in the future. It is unlikely in production, but someone that doesn’t know better could and then bring down the whole website until it is restarted or we implement a global catchall as you indicated. I see this happening more on a staging server than in production. I suppose the staging server could have a website restart as part of the process. Anyways, we’ll see what Npgsql says.

1 Like