Does Npgsql provider has support for TransactionScope?

Question

I'm trying to use a TransactionScope with the Npgsql provider. I found in an old question (provider for PostgreSQL in .net with support for TransactionScope) that Npgsql didn't supported it yet. Now, after about 5 years, does Npgsql support TransactionScope? I made a test for myself, using Npgsql 3.0.3 and using the following code:

using (var scope = new TransactionScope())
{
    using(var connection = new Npgsql.NpgsqlConnection("server=localhost;user id=*****;password=*****database=test;CommandTimeout=0"))
    {
        connection.Open();

        var command = new NpgsqlCommand(@"insert into test.table1 values ('{10,20,30}', 2);");
        command.Connection = connection;
        var result = command.ExecuteNonQuery();

        // scope.Complete();  <-- Not committed
    }
}

Anyone can confirm that Npgsql doesn't support TransactionScope?

EDIT #1 After the confirmation of the support of Npgsql to the TransactionScope, I found that you need to be sure to haev Distribuited Transaction enabled in your PostgreSQL configuration, checking the max_prepared_transactions parameter in the postgres.conf file (remember to restart your server).

EDIT #2 I enabled the Distribuited Transaction on my server but now I got an error using the TransactionScope with Npgsql. This is my code:

using (var sourceDbConnection = new NpgsqlConnection(SourceConnectionString))
using (var destinationDbConnection = new NpgsqlConnection(DestinationConnectionString))
using (var scope = new TransactionScope())
    {
        sourceDbConnection.Open();
        destinationDbConnection.Open();

        Logger.Info("Moving data for the {0} table.", TableName.ToUpper());
        var innerStopWatch = new Stopwatch();
        innerStopWatch.Start();

        foreach (var entity in entities)
        {
            var etlEntity = new EtlInfoItem
            {
                MigratedEntityId = category.RowId,
                ProjectId = category.ProjectId,
                ExecutionDatetime = DateTime.Now
            };

            // Insert into the destination database
            var isRowMigrated = InsertEntity(entity, DestinationSchema, destinationDbConnection);

            if (isRowMigrated)
            {
                // Update the ETL migration table
                InsertCompletedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
            }
            else
            {
                // Update the ETL migration table
                InsertFailedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
            }
        }

        Logger.Info("Data moved in {0} sec.", innerStopWatch.Elapsed);

        Logger.Info("Committing transaction to the source database");
                innerStopWatch.Restart();

        scope.Complete();

        innerStopWatch.Stop();
        Logger.Info("Transaction committed in {0} sec.", innerStopWatch.Elapsed);
    }

When the TransactionScope exits from the scope (when exiting the using statement), I get a Null Reference Exception with the following stack trace: Server stack trace: at Npgsql.NpgsqlConnector.Cleanup() at Npgsql.NpgsqlConnector.Break() at Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) at Npgsql.NpgsqlConnector.ReadExpectingT ......... It happens randomly.

Answer 1

Npgsql does support TransactionScope and has done so for quite a while. However, at least for the moment, in order to have your connection participate in the TransactionScope you must either:

1. Include Enlist=true in your connection string, or
2. Call NpgsqlConnection.EnlistTransaction

Take a look at the Npgsql unit tests around this for some examples.