gin_trgm_ops fails if used for multiple columns

[dtila]

I have 3 columns which I want to create a GIN index using gin_trgm_ops

The EF Core configuration is:

e.HasIndex(e => new { e.Number, e.Title, e.ShortDescription }).HasMethod("GIN").HasOperators("gin_trgm_ops")
This generates the following migration:


 migrationBuilder.CreateIndex(
     name: "IX_ProcedureAssigned_Number_Title_ShortDescription",
     schema: "procurement",
     table: "ProcedureAssigned",
     columns: new[] { "Number", "Title", "ShortDescription" })
     .Annotation("Npgsql:IndexMethod", "GIN")
     .Annotation("Npgsql:IndexOperators", new[] { "gin_trgm_ops" });

However when is applied it generates an invalid syntax:

CREATE INDEX "IX_Table_Column1_Column2_ShortDescription" ON public."Table" USING GIN ("Column1" gin_trgm_ops, "Column2", "ShortDescription");

The command fails since it is not valid Postgres.

I am using the the 8.0.4 with 8.0.7 EF Core

Fix fix is simple to manually create index using SQL, but still let this issue here

[roji]

Sorry it took so long to answer this.

When opening an issue, it's a good idea to at least include the exact error you're getting, as well as the CREATE INDEX you're expecting from SQL - though ideally please always include a minimal, runnable repro.

I tried to reproduce the problem with the below code; the migration generated is the same as what you've posted above, and the error I got was:

data type text has no default operator class for access method "gin"

This is because HasOperators() accepts multiple parameters, oone per indexed column (different columns can have different index operators. If I change the code to:

.HasOperators("gin_trgm_ops", "gin_trgm_ops", "gin_trgm_ops");

... everything works.

I'll go ahead and close this as it was probably the usage error above (and some time has passed). But if I've misunderstood the question, please post back with a repro and I'll reopen.

Code used:

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            // .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresExtension("pg_trgm");

        modelBuilder.Entity<Blog>().HasIndex(e => new { e.Number, e.Title, e.ShortDescription })
            .HasMethod("GIN")
            .HasOperators("gin_trgm_ops");
    }
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Number { get; set; }
    public string Title { get; set; }
    public string ShortDescription { get; set; }
}