search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.59k stars 227 forks source link

Update Exception: 23505: duplicate key value violates unique contraint #188

Closed medimicgolfer closed 7 years ago

medimicgolfer commented 7 years ago

I am having an issue adding new rows to a table. We are a third party vendor so we are not the only process updating the database table. I found when we try to update the table after another process added a row we get this error the next time we try to save changes.

System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23505: duplicate key value violates unique constraint

I've been dealing with the issue for a couple weeks and I can't figure out what the issues is. The database should be generating the id for the entry.

the code is very straight forward. 

        array_webtask_dictuser newADUser = new array_webtask_dictuser()
        {
            // id set by db
            id = 0,
           // Other values set...

... using (var contextCDB = new ArrayEntities()) { try { contextCDB.array_webtask_dictuser.Add(newADUser); contextCDB.Entry(newADUser).State = System.Data.Entity.EntityState.Added; contextCDB.SaveChanges(); } ...

I just recently change the id's Setter property to private so I am not explicitly setting the id=0. I am now omitting the id field when I create my new user.

I can't figure out the issue. Has anyone had a similar issue before?

medimicgolfer commented 7 years ago

Update:

After doing some digging found the sequence index and table were out of sync. Another process is updating the table. Whenever that process updates the table they throw the index and table out of sync.

pastedimage