I have the following table:
And I have the following trigger:
CREATE TRIGGER check_insertion_to_pushes_table
BEFORE INSERT
ON "Pushes"
FOR EACH ROW
EXECUTE PROCEDURE trg_insert_failed_push();
CREATE or replace FUNCTION trg_insert_failed_push()
RETURNS trigger AS
$func$
BEGIN
IF (NEW."Sent" = false) THEN
IF EXISTS(
SELECT *
FROM "Pushes"
where "Sent" = false
and "CustomerId" = NEW."CustomerId"
and "PushTemplateId" = NEW."PushTemplateId"
)
THEN
RETURN NULL;
END IF;
RETURN NEW;
ELSE
RETURN NEW;
end if;
END
$func$ LANGUAGE plpgsql;
If there is row in the DB where CustomerId
and PushTemplateId
and Sent
are equal to new row and Sent
is false I would like to pass insertion.
And I have the following test to check how it works:
public class Tests
{
private IPushRepository _pushRepository;
[NUnit.Framework.SetUp]
public void Setup()
{
var confBuilder = new ConfigurationBuilder();
var configuration = confBuilder.AddJsonFile("/home/aleksej/projects/makeapppushernet/TestProject/appsettings.LocalToProdDb.json").Build();
_pushRepository = new PushRepository(new ApplicationDbContext(configuration));
}
[Test]
public async Task Test1()
{
var push = new Push
{
CustomerId = 69164,
Sent = false,
PackageId = "com.kek.lol",
Category = "betting",
Advertiser = "Advertiser",
TemplateType = "opened_and_not_registration",
IntervalType = "minutes",
BottomDateTimeBorder = 90,
TopDateTimeBorder = 60,
ClientStartDateTime = DateTime.Now,
FCMResponse = "hello",
CreatedAt = DateTime.Now,
LangCode = "En",
PushBody = "Hello",
PushTitle = "Hello",
PushTemplateId = 15
};
var pushesList = new List<Push>
{
push
};
await _pushRepository.SaveAsync(pushesList);
Assert.Pass();
}
}
If I set false
for Sent
in the test I have the following exception:
Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
If I set true
I have nothing. It just passes insertion.
Ok, with the help of Shay Rojansky's answer I have the following trigger code:
CREATE TRIGGER check_insertion_to_failed_pushes_table
BEFORE INSERT
ON "FailedPushes"
FOR EACH ROW
EXECUTE PROCEDURE trg_insert_failed_push();
CREATE or replace FUNCTION trg_insert_failed_push()
RETURNS trigger AS
$func$
DECLARE
push "FailedPushes"%ROWTYPE;
old_push_id numeric;
BEGIN
old_push_id = (SELECT "FailedPushId"
FROM "FailedPushes"
where "CustomerId" = NEW."CustomerId"
and "PushTemplateId" = NEW."PushTemplateId");
push := new;
IF (old_push_id != 0)
THEN
push."FailedPushId" = old_push_id;
DELETE
FROM "FailedPushes"
where "CustomerId" = NEW."CustomerId"
and "PushTemplateId" = NEW."PushTemplateId";
return push;
END IF;
push."FailedPushId" = (SELECT count(*) FROM "FailedPushes")::numeric + 1;
return push;
END
$func$ LANGUAGE plpgsql;
Maybe not very elegant but it works.
You are in effect configuring PostgreSQL to ignore the INSERT under certain conditions, but EF Core isn't aware of this in any way. When you tell EF Core to add a new row, it expects for that to actually happen in the database. If the entity has any database-generated columns (identity, serial), EF Core also expects to receive the their values for the newly-inserted row (and will populate them back into the entity's CLR instance).
So AFAIK you can't just tell the database to ignore the INSERT and expect everything to work...
See this issue on EF Core upsert support which is somewhat related.