error: 42883: function uuid_generate_v4() does not exist

[roji]

From @raditch on October 20, 2015 10:26

Maybe to fix it NpgsqlMigrationSqlGenerator can call create extension "uuid-ossp" somewhere after create schema?

Minimal test to reproduce:

public class Foo
{
    // if comment out line below - the test works
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public Guid Id { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("public");
        base.OnModelCreating(modelBuilder);
    }
}

[TestClass]
public class PgFixture
{
    [TestMethod]
    public void GuidIdentityColumnTest()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
        using (var context = new MyContext())
        {
            context.Foos.Add(new Foo());
        }
    }
}

Copied from original issue: npgsql/npgsql#829

[roji]

Yeah, I'm aware of this issue... I'm not sure how OK it is for the EF provider to implicitly add an extension as you suggest...

Note that you you should be able to easily work around this by defining your own database initializer (inheriting DropCreateDatabaseAlways or any other) and sending the SQL yourself...

Any comments/views on this from anyone? @Emill?

[roji]

From @raditch on October 21, 2015 22:8

I've tried with databaseinitializers: context.Database.ExecuteSqlCommand(@"create extension ""uuid-ossp"""); // call here is meaningless context.Database.Create(); context.Database.ExecuteSqlCommand(@"create extension ""uuid-ossp"""); // call here is too late as the error occurs during the database creation

Then i've tried to inherit NpgsqlMigrationSqlGenerator (as we can inherit SqlServerMigrationSqlGenerator) to override Generate(CreateTableOperation createTableOperation) method something like this

public class MyMigrationSqlGenerator : NpgsqlMigrationSqlGenerator 
{
    protected override void Generate(CreateTableOperation createTableOperation)
    {
        var schemaName = GetSchemaName(createTableOperation);
        Statement(string.Format("CREATE SCHEMA IF NOT EXISTS {0}", schemaName), false, ";");
        if (createTableOperation.Columns.Any(c => c.Type == PrimitiveTypeKind.Guid))
        {
            Statement(string.Format("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\" SCHEMA {0};", schemaName), false, ";");
        }
        base.Generate(createTableOperation);
    }

But NpgsqlMigrationSqlGenerator is not extendable this way as all methods a private.

[roji]

From @franciscojunior on October 27, 2015 23:41

I remember I discussed this with David Karlas how to get this fixed. One of the problems was that the user would need to compile a postgresql module which provides the function. Also, even with the module compiled, in order to add the extension to the database you need admin access, and this isn't a privilege normal users have.

So, one possible fix, I think, is to compile the module and install it in a database and use that database as the template database npgsql will use for ef database creation. This would be done through the database template connection string parameter. This way, when npgsql creates the database, it will use the database which has the function installed as a template and the function will be available on the new database.

I hope it helps. Please, let me know if you need more information.

[roji]

@franciscojunior, thanks for the info!

I remember that extensions used to be more complicated to install in PostgreSQL. I'm pretty sure that in modern versions the uuid-ossp comes bundled, so things should be simpler.

But I still believe that EF6 shouldn't automatically/implicitly add extensions for the user, if we can avoid it. @raditch, I don't see how inheriting NpgsqlMigrationSqlGenerator would help even if it were protected - EF6 wouldn't use your customized generator (not sure about this).

Have you tried to subclass database initializer such as DropCreateDatabaseAlways (as opposed to trying to inherit NpgsqlMigrationSqlGenerator?

[roji]

From @raditch on October 28, 2015 13:30

@roji I've use customized generator to create non clustered primary keys for sql server https://entityframework.codeplex.com/workitem/2163 If it worked for sql server - why not for postgress

@roji I've tried to subclass any of existing database initializers, but without success

[roji]

From @franciscojunior on October 28, 2015 18:28

You are welcome, @roji. I'm glad to help.

Yes, it is very complicated to install. Or at least it is not so straightforward. I also remember that the documentation says that if you only wants uuid generation support, which is our case, it is better to use another function: gen_random_uuid from pgcrypto module. http://www.postgresql.org/docs/9.4/static/uuid-ossp.html

Currently, npgsql doesn't have support for this method, but i think we could add it if it is easier to install or become standard.

I also agree with you that npgsql shouldn't add extensions for the user. That's why we commented out the lines which do that.

@raditch, did you try to use the template connection string parameter in your ef connection?

[roji]

From @raditch on October 29, 2015 6:45

@franciscojunior Not yet. Where is the best place for it? (in the unit test above)

[roji]

From @franciscojunior on October 30, 2015 14:12

@raditich you should add this parameter to your connection string. The same place you specify which server to connect to and which ef database to use. Normally this information is what you pass when creating your ef context.

I hope it helps.

[roji]

From @raditch on October 30, 2015 16:12

@franciscojunior To create database with EF code first, some must have another database with necessary extension to use it as a template?

[roji]

From @the-jiveman on December 22, 2015 19:40

Hi. Just started using Npgsql with Code First (love it!) and still learning my way around Postgres, but I just wanted to say that I also ran into the issue with _uuid_generate_v4() does not exist_ error message after installing latest PostgreSQL (9.4.5). I would also join in recommending that Npgsql add support for _gen_random_uuid_ since that is PostgreSQL's formal recommendation now.

Keep up the good work!

[roji]

Note: a similar issue exists for PostGIS

[colotiline]

Hi.

Solved this issue by executing a custom SQL query in the OnModelCreating.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{           
    InitializePostgresUuidExtension();

    base.OnModelCreating(modelBuilder);
}

private void InitializePostgresUuidExtension()
{
    using (var npgsqlConnection = new NpgsqlConnection(Database.Connection.ConnectionString))
    {
        npgsqlConnection.Open();

        using (var npgsqlCommand = npgsqlConnection.CreateCommand())
        {
            npgsqlCommand.CommandText = "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";";
            npgsqlCommand.ExecuteNonQuery();
        }
    }
}

I didn't find any cons for this solution. What do you think? Are there any cons for it?

[rwasef1830]

@colotiline In most cases, you db user will not have permission to issue CREATE EXTENSION.

[michalpaukert]

How about this one?

public partial class EnableExtension : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

[alex-sherman]

The above comment worked for me, but I found it didn't work in tests where I was delete/creating the database with EnsureCreatedAsync(). Instead using MigrateAsync() fixed it there.