CODEWITHSUNDEEP
ruby-on-railsrubypostgresqltestingrails-activerecord
Timur Nugmanov
Timur Nugmanov

Reputation: 893

How to test PG::QueryCanceled (due to timeout) error in a Rails app?

On production server I got this error

ActiveRecord::StatementInvalid: PG::QueryCanceled: ERROR: canceling statement due to statement timeout <SQL query here>

In this line:

Contact.where(id: contact_ids_to_delete).delete_all

SQL query was DELETE command with a huge list of ids. It timed out. I came up with a solution which is to delete Contacts in batches:

Contact.where(id: contact_ids_to_delete).in_batches.delete_all

The question is, how do I test my solution? Or what is the common way to test it? Or is there any gem that would make testing it convenient?
I see two possible ways to test it:
1. (Dynamically) set the timeout in test database to a small amount of seconds and create a test in which I generate a lot of Contacts and then try to run my code to delete them.
It seems to be the right way to do it, but it could potentially slow down the tests execution, and setting the timeout dynamically (which would be the ideal way to do it) could be tricky.
2. Test that deletions are in batches.
It could be tricky, because this way I would have to monitor the queries.

Upvotes: 3

Views: 5647

Answers (1)

anothermh
anothermh

Reputation: 10564

This is not an edge case that I would test for because it requires building and running a query that exceeds your database's built-in timeouts; the minimum runtime for this single test would be at least that time.

Even then, you may write a test for this that passes 100% of the time in your test environment but fails 100% of the time in production because of differences between the two environments that you can never fully replicate; for one, your test database is being used by a single concurrent user while your production database will have multiple concurrent users, different available resources, and different active locks. This isn't the type of issue that you write a test for because the test won't ensure it doesn't happen in production. Best practices will do that.

I recommend that you follow the best practices for Rails by using the find_in_batches or find_each methods with the expectation that the database server can successfully act on batches of 1000 records at a time:

Contact.where(id: contact_ids_to_delete).find_in_batches do |contacts|
  contacts.delete_all
end

Or if you prefer:

Contact.where(id: contact_ids_to_delete).find_in_batches(&:delete_all)

You can tweak the batch size with batch_size if you're paranoid about your production database server not being able to act on 1000 records at a time:

Contact.where(id: contact_ids_to_delete).find_in_batches(batch_size: 500) { |contacts| contacts.delete_all }

Upvotes: 5

Related Questions