In our SQL Server integration tests, we wrap tests in a TransactionScope which we then rollback after each test to keep the database in a consistent state.
Using Postgres (or possibly specifically Npgsql), this doesn't appear to be possible, as selects outside of an individual connection cannot read uncommitted data (even when within the uncommitted transactionscope).
The basic scenario is simplified below:
[Test]
public void ImplicitEnlist()
{
var connectionString = ConnectionString + ";enlist=true";
using (var scope = new TransactionScope())
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
Assert.That(conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES('test')"), Is.EqualTo(1));
}
using (var conn = new NpgsqlConnection(connectionString))
{
// -> this is false
Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(1));
}
scope.Rollback();
}
}
Can anyone share how folks are approaching this with Postgres databases?
