Postgres ENUM type not visible to dapper on first run

Question

I'm having a strange issue with Postgres enums, where I'm getting an error trying to query a table with an ENUM, but only if the CREATE commands were run during the same session. I have no idea why this is happening, and it makes running migration scripts quite difficult, as I now have to run the application twice before everything works.

Thinking it is some kind of caching issue I tried turning off the connection pool, but that doesn't seem to work either.

It was quite easy to reproduce, so I made a code sample:

using Dapper;
using Npgsql;

var connection = new NpgsqlConnection("Host=127.0.0.1;Port=5432;User ID=postgres;Password=password;Database=messaging;Include Error Detail=true;");
await connection.OpenAsync();

var tableCommand = new NpgsqlCommand()
{
    Connection = connection,
    CommandText = """
        CREATE TYPE message_type AS ENUM ('sms', 'email');
        CREATE TABLE message (
            id SERIAL PRIMARY KEY,
            type message_type NOT NULL
        );
        """
};

// Comment this line on a second run
await tableCommand.ExecuteNonQueryAsync();

var queryCommand = new NpgsqlCommand()
{
    Connection = connection,
    CommandText = """
        SELECT id, type
        FROM message;
        """
};

var reader = await queryCommand.ExecuteReaderAsync();
// The offending line
var parser = reader.GetRowParser<Message>();

class Message
{
    public int Id { get; set; }
    public string? Type { get; set; }
}

This produces the following error on the first run:

System.InvalidCastException: 'Reading as 'System.Object' is not supported for fields having DataTypeName '.<unknown>''

Inner Exception
ArgumentException: A PostgreSQL type with the oid '0' was not found in the current database info

On a second run (when commenting out the create command) I do not get any errors and everything works as expected. It is almost as if it cannot see that the enum exists on the first run, but the second time everything is fine because it is already there when the connection is made.

I am using the latest version (at the time of writing) of everything:

• Postgres 16.1
• .NET 8
• Dapper 2.1.24
• Npgsql 8.0.1

Answer 1

You must use await connection.ReloadTypesAsync(); to reload new types created in your command.

This is a sample, that use Dapper instead of commands, but you could simply insert the reload types command in your code:

await connection.ExecuteAsync("""
    CREATE TYPE message_type AS ENUM ('sms', 'email');
    CREATE TABLE message (
        id SERIAL PRIMARY KEY,
        type message_type NOT NULL
    );
    """);

await connection.ReloadTypesAsync(); // add this to your code

var parser = await connection.QueryAsync<Message>("""
       SELECT id, type
       FROM message;
       """);  

In the NpgsqlConnection is documented that this method refresh the cache of the types loaded in the connection.