I am using Entity Framework Core with PostgreSQL. I want to override SaveChanges in my DbContext to both commit the EF changes and send a message with Rebus within the same database transaction. The plan is to also use PostgreSQL for transport etc, but I am having trouble merely enlisting Rebus in a transaction scope using the code below.
public override int SaveChanges()
{
using (var tx = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
tx.EnlistRebus();
var result = base.SaveChanges();
//TODO: _bus.Send("something happened");
tx.Complete();
return result;
}
}
Running the above results in PostgresException: 55000: prepared transactions are disabled, which is true, since my PostgreSQL configuration has not enabled it. My question is why Rebus needs to cause a 2-phase commit here. Maybe I'm missing something, though I would hope a 2PC is not needed, given both Entity Framework and Rebus will use the same relational database instance.
The call to EnlistRebus is in the Rebus.TransactionScopes package and does not know what transport implementation is in use and might do the safe thing.
Is there another way to do both the database operation and Rebus send transactionally without 2-phase commit? I can of course use a separate table to store my pending message from SaveChanges and have a separate worker pull from that table and send the message using Rebus. I suspect this approach is the most solid and straightforward.
I am using Rebus 6.3.0, Rebus.PostgreAql 7.1.0, Rebus.TransactionScopes 6.0.0, Npgsql 4.1.3.1, Npgsql.EntityframeworkCore.PostgreSQL 3.1.4 and EF Core 3.1.4.
Rebus.PostgreSQL will actually enlist in the ambient transaction by itself without Rebus.TransactionScopes.
Looking at the Rebus.PostgreSQL repo, I saw there was a PR that seems to solve my issue. Quote from the PR at https://github.com/rebus-org/Rebus.PostgreSql/pull/14:
I first tried getting https://github.com/rebus-org/Rebus.TransactionScopes to work, but that does not seem to do anything using the postgresql transport.
To verify that it does indeed do the message publishing in the ambient transaction, I did the following modification:
public override int SaveChanges()
{
using (var tx = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
TransactionScopeAsyncFlowOption.Enabled))
{
var result = base.SaveChanges();
_bus.Send(new MyMessage { CurrentDateTime = DateTime.Now}).Wait();
if (ShouldCrash)
{
throw new ArgumentException();
}
tx.Complete();
return result;
}
}
If ShouldCrash is set to true, no message is published and no changes are made to the entities. If ShouldCrash is set to false both message publishing and entity change are performed.
I think this works because of the following from the Npgsql docs:
Note that if you open and close connections to the same database inside an ambient transaction, without ever having two connections open at the same time, Npgsql will internally reuse the same connection, avoiding the escalation to a full-blown distributed transaction.