Postgres, Npgsql and Integration tests using rollbacks

Question

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?

Answer 1

Copy-paste of answer from the github issue:

I really don't think things are supposed to work in the way you think. When you open two connections within the same TransactionScope, you're doing distributed transactions. PostgreSQL has 2 different connections, each with a prepared transaction. Now, this doesn't mean that the two connections are aware of each other, or that the two prepared transactions are somewhat linked. And since they haven't been committed yet, transaction isolation applies and each connection can't see the other one's uncommitted changes.

To summarize, distributed transactions mean that when you call Complete() on your TransactionScope, the two-phase commit protocol will be used to ensure that either both transactions commit, or none. It doesn't guarantee that participating transactions are somehow aware of each other.

While I'm pretty sure this is how things work, I'm not an expert in either distributed transactions or PostgreSQL prepared transactions - so I may be wrong. Am going to close this, but will reopen if you can find any info contrary to what I said.