Postgres Upsert with UpdateByProperties not working

[swimmesberger]

When using the function BulkInsertOrUpdate with UpdateByProperties with postgres an error occurs:

 CREATE INDEX CONCURRENTLY cannot run inside a transaction block
       ---> Npgsql.PostgresException (0x80004005): 25001: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

The issue seems to be in PostgreSqlQueryBuilder.CountUniqueConstrain, there is a check only for constraints, but an index can also be marked as unique, producing identical uniqueness behavior.

So I have no explicit unique constraint on the property but a unique index.

A possible workaround is to define the property defined in UpdateByProperties as ef core alternate key (HasAlternateKey) instead of unique index (HasIndex(x => ...).IsUnique()).
This will create a constraint behind the scenes additionally to the unique index.

[swimmesberger]

Maybe the upsert case can even be redesigned. I found that when "CustomSourceTableName" is not defined the call adds an (unnecessary? CREATE TABLE call). Additionally counting the constraints for every call just to determine if a manual index should be created seems also unnecessary maybe we could skip that too when certain configurations are set. In my case I want a "simple" INSERT INTO ... ON CONFLICT ... DO UPDATE SET call without the chatter before and after 😊

[videokojot]

I agree, the library is just mess.. PRs are always welcome