"ERROR: 57014: canceling statement due to user request" Npgsql

Question

I am having this phantom problem in my application where one in every 5 request on a specific page (on an ASP.NET MVC application) throws this error:

Npgsql.NpgsqlException: ERROR: 57014: canceling statement due to user request
   at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRow(Boolean clearPending)
   at Npgsql.ForwardsOnlyDataReader.Read()
   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
   ...

On the npgsql github page I found the following bug report: 615

It says there:

Regardless of what exactly is happening with Dapper, there's definitely a race condition when cancelling commands. Part of this is by design, because of PostgreSQL: cancel requests are totally "asynchronous" (they're delivered via an unrelated socket, not as part of the connection to be cancelled), and you can't restrict the cancellation to take effect only on a specific command. In other words, if you want to cancel command A, by the time your cancellation is delivered command B may already be in progress and it will be cancelled instead.

Although they have made "changes to hopefully make cancellations much safer" in Npgsql 3.0.2 my current code is incompatible with this version because the need of migration described here.

My current workaround (stupid): I have commented the code in Dapper that says command.Cancel(); and the problem seems to be gone.

if (reader != null)
                {
                    if (!reader.IsClosed && command != null)
                    {
                        //command.Cancel();
                    }
                    reader.Dispose();
                    reader = null;
                }

Is there a better solution to the problem? And secondly what am I loosing with the current fix (except that I have to remember the change every time I update Dapper)?

Configuration: NET45, Npgsql 2.2.5, Postgresql 9.3

Answer 1

I found why my code didn't dispose the reader, resulting in calling command.Cancel(). This only happens with QueryMultiple method when not every refcursor is read.

Changing the code from:

using (var multipleResults = connection.QueryMultiple("schema.getuserbysocialsecurity", new { socialSecurityNumber }))
{
    var client = multipleResults.Read<Client>().SingleOrDefault();

    if (client != null)
    {
        client.Address = multipleResults.Read<Address>().Single();
    }

    return client;
}

To:

using (var multipleResults = connection.QueryMultiple("schema.getuserbysocialsecurity", new { socialSecurityNumber }))
{
    var client = multipleResults.Read<Client>().SingleOrDefault();
    var address = multipleResults.Read<Address>().SingleOrDefault();

    if (client != null)
    {
        client.Address = address;
    }

    return client;
}

This fixed the issue and now the reader is properly disposed and command.Cancel() is not invoked.

Hope this helps anyone else!

UPDATE

The npgsql docs for version 2.2 states:

Npgsql is able to ask the server to cancel commands in progress. To do this, call the NpgsqlCommand’s Cancel method. Note that another thread must handle the request as the main thread will be blocked waiting for command to finish. Also, the main thread will raise an exception as a result of user cancellation. (The error code is 57014.)

I have also posted an issue on the Dapper github page.