[Feature] Add a way to test cancellation where the query still completes

[cleroux]

Hi! Thank you for this useful library!

With regard to cancelling queries in Postgres, through context cancellation or other mechanism like pg_cancel_backend(), Postgres provides no guarantee that the query will actually be cancelled, and therefore might still complete normally.

From Postgres docs:

The cancellation signal might or might not have any effect — for example, if it arrives after the backend has finished processing the query, then it will have no effect. If the cancellation is effective, it results in the current command being terminated early with an error message.

At the moment, sqlmock will always produce an error if we cancel the context passed into it.

In a highly concurrent system that supports query cancellation, we have observed this edge case happening often enough that we would like to test for it. So it would be nice if we could set up this scenario where the context is cancelled but sqlmock does not produce an error.

I hope to hear your opinion on this. If this sounds reasonable, I may be able to submit a PR. Though I admit I have not dived into the implementation of sqlmock to know how complex this might be.

Proposal

Perhaps something like .WillCompleteOnCancel().WillReturnRows(rows)

Use-cases

I would like to be able to test the case where the query might complete without error when the context is cancelled.

[diegommm]

Hi @cleroux! Thank you for your request. Just read and then re-read your submission, and I suspect you may be confusing a couple of things, but make sure to correct me if I'm wrong. Here are some pointers:

1. sqlmock is a valid /database/sql/driver that emulates different conditions from a database driver standpoint.
2. It does not emulate the behaviour of a database.
3. If you use any driver, included any of the postgres ones (either the old pq or the new pgx), you will always get an error if your context is canceled.
4. The above does not mean that the database has performed or not any actual cancellation of work, it only means that the request to the database has been cancelled. The underlying driver will always return an error for a cancelled context, and that's the way a Go program is expected to behave.
5. In order to have the highest possible guarantees, you may have to resort to database specific mechanisms, like querying system tables to know if some work has been cancelled or not, or ultimately verifying the state of your data.
6. That is because Go contexts do not represent any form of transactional operation, but rather carry cancellation signals. A program is free to ignore a cancellation signal or even not be able to act upon it.

To put it another way, try execute a query with a cancelled context using the real pq or pgx drivers. Also try sending a non-cancelled context but somehow make sure that you cancel it before it returns. Both cases using either driver should return the same context cancellation error, otherwise it's a bug in the driver.

To learn more what the Go context package can and can't do for you make sure to read:

• https://pkg.go.dev/context
• https://go.dev/blog/context