BulkInsertOrUpdateAsync fails for Postgres when unique index required

[tejssidhu]

First of thank you for all the great work you guys do on this library.

So the issue I've experienced is using the BulkInsertOrUpdateAsync extension method and supplying update properties in the BulkConfig. When executed the action fails with the below error:

Exception data:
    Severity: ERROR
    SqlState: 42704
    MessageText: index "<indexName>" does not exist
    File: tablecmds.c
    Line: 1299
    Routine: DropErrorMsgNonExistent

I've created the below small program that replicates the issue:

using EFCore.BulkExtensions;
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

namespace EFBulkExtensionsPostgresIssue
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<BloggingContext>();

            var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING");
            optionsBuilder
                .UseNpgsql(connectionString);
            using var context = new BloggingContext(optionsBuilder.Options);

            var canConnect = await context.Database.CanConnectAsync();

            if (!canConnect)
            {
                Console.WriteLine("Cannot connect to database");
                return;
            }

            var items = new List<BlogAggregation>();
            Random rnd = new();

            for (var i = 0; i < 20; i++)
            {
                items.Add(new BlogAggregation
                {
                    Id = Guid.NewGuid(),
                    AggregationTypeId = 1,
                    CreatedAt = DateTime.UtcNow,
                    Value = rnd.Next(),
                    AggregationDate = DateTime.UtcNow.AddDays(i * -1)
                });
            }

            var bulkConfig = new BulkConfig
            {
                UpdateByProperties = [nameof(BlogAggregation.AggregationTypeId), nameof(BlogAggregation.AggregationDate)],
                PropertiesToIncludeOnUpdate = [nameof(BlogAggregation.Value), nameof(BlogAggregation.CreatedAt)]
            };

            await context.BulkInsertOrUpdateAsync(items, bulkConfig);
        }
    }

    public class BlogAggregation
    {
        [Key]
        public Guid Id { get; init; }
        public int AggregationTypeId { get; init; }
        public int Value { get; init; }
        public DateTime CreatedAt { get; init; }
        public DateTime AggregationDate { get; init; }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<BlogAggregation> BlogAggregations { get; set; } = null!;

        public BloggingContext(DbContextOptions<BloggingContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("MySchema");
        }
    }
}

Using this I've diagnosed the issue and the two below are contributing:

• index name lengths are limited to 64 chars in Postgres
• the schema name is missing when the index is attempted to be deleted

I've also got a solution for this and would love to raise a PR but sharing here first as per the contributing guidelines.

[borisdj]

Glad it's useful.
Sure make a PR, add also a test for the issue.

[vcluopeng]

I find it interesting that he still creates a unique index when the table's unique index exists. In the case of very large data volumes, the cost of creating unique indexes is huge. Is it possible to disable the automatic creation of unique indexes?

[vcluopeng]

I found out that it is the following SQL, which does not find the unique index that already exists.
I tried to fix this problem, but I am not familiar with SQL.

SELECT COUNT(distinct c.conname)

                  FROM pg_catalog.pg_namespace nr,

                      pg_catalog.pg_class r,

                      pg_catalog.pg_attribute a,

                      pg_catalog.pg_namespace nc,

                      pg_catalog.pg_constraint c

                  WHERE nr.oid = r.relnamespace

                  AND r.oid = a.attrelid

                  AND nc.oid = c.connamespace

                  AND r.oid =

                      CASE c.contype

                          WHEN 'f'::"char" THEN c.confrelid

                      ELSE c.conrelid

                          END

                      AND (a.attnum = ANY (

                          CASE c.contype

                      WHEN 'f'::"char" THEN c.confkey

                          ELSE c.conkey

                          END))

                      AND NOT a.attisdropped

                      AND (c.contype = ANY (ARRAY ['p'::"char", 'u'::"char"]))

                      AND (r.relkind = ANY (ARRAY ['r'::"char", 'p'::"char"])) AND r.relname = 'supplier_room_rate_analyzes' AND nr.nspname = 'public' AND a.attname IN('SupplierId','HotelCode','RoomId')

[tejssidhu]

@borisdj, do I need to be added as a contributor? I don't have the option to create a branch.

[borisdj]

Hi, no, you need to create a Fork, then ones you do a commit, make a PR.

[borisdj]

PR merged, thx for contrib.
Will be publish soon with next nuget version.