I have just encountered some pain related to what is referred to as query caching in Rails 2.0. The database I am dealing with in staging and production is Microsoft SQL Server (I sure hope that Sybase is still making some sort of money off Microsoft for providing them with a leg up on database server development).
The first problem I ran into was with an application that worked absolutely-just-fine-thank-you-very-much under Rails 1.2. I updated it to Rails 2.0 to take advantage of multiple view paths, ran all my tests, made adjustments, ran all my tests again, and everything looked good — against the local MySQL database server I use for development and testing.
Upon deploying the application to staging (with MS SQL Server as the database), I started to encounter some really weird behavior related to the data being inserted into the database. After some snooping, I discovered something new in the log file. It was a SQL statement that was prefixed with the word CACHE. When I noticed the SQL statement that was ostensibly hitting cache instead of the database, then the weird behavior started to make sense:
CACHE (0.000000) SELECT @@IDENTITY AS Ident
This is the SQL statement you issue in order to find out the value of the ID that was assigned to the most recent INSERT statement. So, even though each INSERT is supposed to clear cache, I was seeing this particular statement hitting cache and thus returning a stale and incorrect value for the most recent INSERT. This wasn’t working out so well for creating new rows for my has_many relationships.
Request #0 for query caching: Improve the documentation and provide more example code. I am confused over whether or not hitting cache for the above SELECT statement directly after an INSERT is a defect or a feature.
Request #1 for query caching: Allow a connection adapter to specify statements, perhaps using regular expressions, that should NOT be cached EVER.
So, I discovered that there was support for explicitly turning off query caching for a particular code block, but I was unclear on specifically how it should be done:
Model.uncached do...
Model.uncached do, e. g. Order.uncached do....
I finally decided the most effective would be to hit up the connection directly:
ActiveRecord::Base.connection.uncached do...
This solved the problem for creating the one set of relationships. I thought I was done. Then stuff elsewhere starting acting weird. Sure enough, additional investigation of the logs yielded my old friend and troublemaker:
CACHE (0.000000) SELECT @@IDENTITY AS Ident
So, given the time pressure, it seemed like the most effective treatment for this problem would be simply to extinguish query caching for the entire application. This would be an acceptable solution. Unfortunately, there is apparently no easy way to do this. I tried creating an initializer that literally disabled query caching in my connection:
ActiveRecord::Base.connection.query_cache_enabled = false
Unfortunately, I was foiled by ActionController which goes ahead and runs all actions inside of a block passed into ActiveRecord::Base.cache. Consequently, my final solution was simply to disinclude SqlCache from my controllers directly at the beginning of action_controller/caching.rb (line 18 for those of you keeping score).
Request #2 for query caching: Make it dead simple to enable/disable query caching for the entire application without affecting any other caching Rails is doing.
This cache fun made me think that, although this is a unique case where a specific SQL statement should NOT EVER be cached, there are situations where you probably want to fine tune caching on a per-model basis. A simple and ubiquitous example is one where your Rails app is not the actual source of data, but it does display the data. In this case, you never want to cache your query results — you always want them going to the database. I remember from days of old with JBoss that they had several caching strategies per entity bean. Perhaps we can learn something from either that or whatever the newer thinking is for caching that comes along for the ride with Hibernate / EJB 3.0.
Request #3 for query caching: Allow query caching to be enabled/disabled on a per-model basis.
If you have gotten this far, I’m sure you’re thinking, “Great, Michael; what are you going to do about it?” If anyone can point me to the appropriate person on the Rails team — core or extended — whom I should work with on these issues, I am happy to contribute.