Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type

[cthames]

Steps to reproduce

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

Similar issue as above, except no custom types being used. Basically all you need to do is DROP SCHEMA myschema; and then CREATE SCHEMA myschema; and also create the tables while the website is already running and have already made some executions on that schema.

What happened to us was...
While the website was running, a db update was sent out that dropped a schema (data was moved first) and then recreated the schema with all the tables and their changes and data was re-inserted into the newly recreated schema. Afterwards going to the website threw this error Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type and kept throwing that error and took down the website until someone restarted the website.

The issue

Why is Npgsql throwing that error instead of just reloading the types automatically and then retrying again? NpgsqlConnection.ReloadTypes() If anything it should at least attempt to reload the types at least once.

Also the exception is a generic Npgsql.PostgresException and one would need to examine the error message text to determine that the types needed to be reloaded.

This seems something low level that Npgsql should handle out of the box (by automatically ReloadTypes() instead of forcing Npgsql users in implementing a global catch all for an error message text value that has cache lookup failed for type in it as Npgsql could change that error message text in the future.

Exception message: 
Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type 54669
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()

Further technical details

Npgsql version: 4.0.3
PostgreSQL version: 11
Operating system: Windows

[roji]

Similar issue as above, except no custom types being used.

The latter part of the issue talks about reloading types, so I'm not exactly sure whether you're using custom types or not. If you're really not using custom types, then ReloadTypes() shouldn't make any difference, and I'm also not sure how you're encountering that error - can you please submit a small repro that demonstrates the issue?

Why is Npgsql throwing that error instead of just reloading the types automatically and then retrying again?

Dropping a custom type and recreating it is a pretty drastic operation, and (at least up to now) there's been no expectation for the driver to handle such an event transparently. As a general rule, we try not to contain automatic, under-the-hood recovery - as a general rule this tends to be brittle and bloat the driver with complicated behavior.

May I ask in what scenario you're dropping and recreating your custom type, and why you can't simply call ReloadTypes() at that point?

Also the exception is a generic Npgsql.PostgresException and one would need to examine the error message text to determine that the types needed to be reloaded.

That's actually a pretty good answer to why Npgsql doesn't contain automatic logic for internally calling ReloadTypes() - Npgsql isn't any better at understanding PostgreSQL errors than the user is. I imagine this error would be localized to different languages, and we're definitely not going to start handling that. The right way really seems to be for the application to reload its types when it recreates them (or not drop them at all).

[cthames]

There wasn't any CREATE TYPE mytype; going on on in any db, table, or schema.

Maybe hstore is considered a custom type? hstore was used in one or two of the columns of one or more tables, but the hstore extension was created on a different schema and wasn't dropped by the db changes. I'll try to reproduce without hstore to confirm.

The right way really seems to be for the application to reload its types when it recreates them (or not drop them at all).

The application didn't have any changes, it was a website not an app and it was an update to the tables in that schema where the data was transformed and additional columns added/removed/ int to bigint etc. The website did not need to know about the change. The issue happens even if there are no table/column changes.

The steps to reproduce the steps easily by creating a connection and do a select off a table in a schema (let it finish) and then while the connection is still open drop the schema and then recreate the schema with the table and while using the open connection do the same select off of it.

[roji]

Yes, types coming from extensions like hstore are indeed custom types, and behind the scenes they're created with CREATE TYPE just like a user custom type. On possibility here is to avoid dropping the schema where hstore specifically lives; for example, you can install the extension into a special schema (CREATE EXTENSION hstore SCHEMA hstore), and not drop that specific one. This is much healthier than reloading the type every time, etc.

Please confirm that the issue is indeed related to hstore, to make sure there's nothing else going on.

One last suggestion - hstore is considered somewhat deprecated now that PostgreSQL has native support for jsonb. That type is much more flexible both in terms of the data it can hold and the operations/queries you can perform on it. Incidentally it's also built-in, so the issues around dropping the schema would be gone.

[ryan-morris]

I have the same issue with postgis. In our MSTest project we have the following

        /// <summary>
        /// Initializes DB, seeds data, creates service instance
        /// </summary>
        /// <returns></returns>
        [TestInitialize]
        public async Task TestInitialize()
        {
            var context = new SomeContext();

            await context.Database.ExecuteSqlCommandAsync("DROP SCHEMA IF EXISTS public CASCADE;");
            await context.Database.ExecuteSqlCommandAsync("CREATE SCHEMA public;");
            await context.Database.MigrateAsync();
            await context.Seed();
        }

but seeding fails with the cache lookup errors, even trying to manually call ReloadTypes() is flaky at best . I've tried calling it after the drop, create, and migrate but still hit issues when the .Seed() is invoked due to the connection pool reuse.

Our initial migrations contains

            migrationBuilder.AlterDatabase()
                .Annotation("Npgsql:PostgresExtension:cube", ",,")
                .Annotation("Npgsql:PostgresExtension:earthdistance", ",,")
                .Annotation("Npgsql:PostgresExtension:postgis", ",,")
                .Annotation("Npgsql:PostgresExtension:uuid-ossp", ",,");

My solution for the time being is

        /// <summary>
        /// Initializes DB, seeds data, creates service instance
        /// </summary>
        /// <returns></returns>
        [TestInitialize]
        public async Task TestInitialize()
        {
            using (var cleanUpContext = new SomeContext())
            {
                await cleanUpContext.Database.ExecuteSqlCommandAsync("DROP SCHEMA IF EXISTS public CASCADE;");
                await cleanUpContext.Database.ExecuteSqlCommandAsync("CREATE SCHEMA public AUTHORIZATION postgres;");
            }

            using (var migrateContext = new SomeContext())
            {
                await migrateContext.Database.MigrateAsync();
                await migrateContext.Database.OpenConnectionAsync();
                ((Npgsql.NpgsqlConnection)migrateContext.Database.GetDbConnection()).ReloadTypes();
            }

            var context = new new SomeContext();
            await context.Seed();
        }

[roji]

@ryan-morris that makes sense. The Npgsql EF Core provider automatically calls ReloadTypes() when its NpgsqlDatabaseCreator is used; this happens mainly when you call EnsureCreated() from your application. However, if you manually recreate your database and apply migrations, you must call ReloadTypes() yourself - AFAIK there isn't really an extension point in the EF Core migrations mechanism where a ReloadTypes() could be inserted.

In actual production use, it is usually recommended to generate migration SQL scripts and apply them externally, rather than programmatically (in this case there's no issue). For testing what you're doing should be fine, but it's still up to you to manually reload types.

Am closing this issue as an answer has been provided, and no confirmation has been received in the last month.