“ERROR: 57014: canceling statement due to user request” Npgsql 2.2.5

[gyosifov]

In the QueryMultiple method when not reading all of the refcursors of a PostgreSQL function the Dispose method of GridReader is invoked. Since the reader is not closed, command.Cancel() is called. (https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs#L4497)

Reading the user manual of Npgsql (http://www.npgsql.org/doc/manual-2.2.html) regarding the Cancel method it 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.)"

This causes code like this to throw exception if the first refcursor is empty:

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

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

    return client;
}

Can you confirm if this is the desired result or is it an issue? Thank you!

[horse315]

Hello! I've caught same error (57014) yesterday with latest Dapper 1.42 and Npgsql 2.2.5. After upgrading npgsql to 3.0.2 the problem has gone.

[gyosifov]

Hello, @horse315!

Unfortunately the migration from 2.2 to 3.0 in Npgsql is not seamless.

"Version 3.0 represents a near-total rewrite of Npgsql ... This means that you cannot expect to drop 3.0 as a replacement to 2.2 and expect things to work - upgrade cautiously and test extensively before deploying anything to production." (more can be found here http://www.npgsql.org/doc/migration-3.0.html)

I believe that the fix for this issue is the reason you are not experiencing it in 3.0.2 npgsql/npgsql#615.

[horse315]

@gyosifov
Yes, I decided to upgrade after reading this thread npgsql/npgsql#615. After that 50% of test failed 😓. It took us about hour and a half to find bug in our queries. Hope you'll find your solution too.

[gyosifov]

Thank you @horse315 !

I have described in more detail my problem and two possible ways to get around it here - http://stackoverflow.com/questions/32632926/error-57014-canceling-statement-due-to-user-request-npgsql

But since I cannot say what is the right way of dealing with it I need confirmation from the dapper team that this is the way Dapper should behave if not all refcursors have been read, when using Npgsql. 👍

[mgravell]

This is a tricky one, not least because I'm not an expert with that
provider. It is always very vexing to library authors when providers
implement things in broken ways, where there is no single approach that can
work on all providers (and sometimes no way of making it work properly
without identifying and special-casing specific vendors).

On Fri, 25 Sep 2015 18:01 gyosifov [email protected] wrote:

Thank you @horse315 https://github.com/horse315 !

I have described in more detail my problem and two possible ways to get
around it here -
http://stackoverflow.com/questions/32632926/error-57014-canceling-statement-due-to-user-request-npgsql

But since I cannot say what is the right way of dealing with it I need
confirmation from the dapper team that this is the way Dapper should behave
if not all refcursors have been read, when using Npgsql. [image: 👍]

—
Reply to this email directly or view it on GitHub
#344 (comment)
.

[NickCraver]

My inclination is to close this out - simply because npgsql being so radically different between v2 and v3, it seems like we need to support v3 and the problems with v2 are just unfortunate. The complication of supporting both even plans into our test suites - testing both actually requires quite a bit of duplication, which seems like a short-term win for no long-term gain as people move off of v2.

Thoughts here? I'm not as familiar with the ecosystem and I'm not sure what the split is on v2 vs. v3 userbase is.

[gyosifov]

It is true that there are a lot of differences between v2 and v3 but this is not Dapper's fault. The point here is (by my opinion): is there enough justification for calling Cancel command in the Dispose method or is this a potential bug?

From the only feedback I got from my question in SO, it seems that there are diverse opinions.
http://stackoverflow.com/questions/32632926/error-57014-canceling-statement-due-to-user-request-npgsql

[mgravell]

Action for myself: I need to audit when cancel is/isn't called, and make
sure it is as sensible and consistent as can be.
On 27 Nov 2015 3:27 p.m., "gyosifov" [email protected] wrote:

It is true that there are a lot of differences between v2 and v3 but this
is not Dapper's fault. The point here is (by my opinion): is there enough
justification for calling Cancel command in the Dispose method or is this a
potential bug?

From the only feedback I got from my question in SO, it seems that there
are diverse opinions.

http://stackoverflow.com/questions/32632926/error-57014-canceling-statement-due-to-user-request-npgsql

—
Reply to this email directly or view it on GitHub
#344 (comment)
.

[NickCraver]

I'm going to close this specific issue out, but @gyosifov I've created the follow-up that's much easier to track on .Cancel() specifically (and assigned it to @mgravell as a todo action).