I use PostgreSQL and EF6 in some project.
I want to reject silently some rows I insert who have duplicated data. The table is very simple, looking like :
CREATE TABLE data
(
table_id PRIMARY KEY
table_element
)
With id_table as a autoincrement key.
In the PostgreSQL side, I use this simple trigger to reject duplicates :
CREATE OR REPLACE FUNCTION checkDuplicate()
RETURNS trigger AS
DECLARE
datacount integer;
BEGIN
SELECT Count(table_id) into datacount
FROM data WHERE table_element = NEW.table_element;
IF (datacount > 0) THEN
RETURN null;
ELSE
RETURN new;
END IF;
END;
This function is setted as trigger with "BEFORE INSERT". When null is returned, PostgreSQL don't insert the row as intended.
However, Entity Framework throws a System.Data.Entity.Infrastructure.DbUpdateException
, with the expectable message A null store-generated value was returned for a non-nullable member 'table_id'
; table_id is obviously never setted because the row was rejected.
I wanted to know how can I tell Entity Framework to juste ignore the row instead of triggering an exception and continue the insert of the next rows.
LOCK TABLE ... IN EXCLUSIVE MODE
the table. It's subject to race conditions that are not solveable without predicate locking. If you useSERIALIZABLE
transactions I think you can cause it to conflict, though I'd want to do some testing and checking to be sure. You'd want to check the isolation level within the trigger and raise an error if it isn'tserializable
to make sure an app didn't accidentally useREAD COMMITTED
isolation. – Craig Ringer Jun 30 at 7:48