How can I prevent any ActiveRecord::PreparedStatementCacheExpired errors immediately after running `rake db:migrate`?

Question

I am working on a Rails 5.x application, and I use Postgres as my database.

I often run rake db:migrate on my production servers. Sometimes the migration will add a new column to the database, and this causes some controller actions to crash with the following error:

ActiveRecord::PreparedStatementCacheExpired: ERROR: cached plan must not change result type

This is happening in a critical controller action that needs to have zero downtime, so I need to find a way to prevent this crash from ever happening.

Should I catch the ActiveRecord::PreparedStatementCacheExpired error and retry the save? Or should I add some locking to this particular controller action, so that I don't start serving any new requests while a database migration is running?

What would be the best way to prevent this crash from ever happening again?

Board

I usually rescue the error and retry the save since the ActiveRecord::PreparedStatementCacheExpired actually clears the cache on error. The second save attempt should then work. – Phosphorate

Answer 1

I was able to fix this issue in some places by using this retry_on_expired_cache helper:

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  class << self
    # Retry automatically on ActiveRecord::PreparedStatementCacheExpired.
    # (Do not use this for transactions with side-effects unless it is acceptable
    # for these side-effects to occasionally happen twice.)
    def retry_on_expired_cache(*_args)
      retried ||= false
      yield
    rescue ActiveRecord::PreparedStatementCacheExpired
      raise if retried

      retried = true
      retry
    end
  end
end

I would use it like this:

  MyModel.retry_on_expired_cache do
    @my_model.save
  end

Unfortunately this was like playing "whack-a-mole", because this crash just kept happening all over my application during my rolling deploys (I'm not able to restart all the Rails processes at the same time.)

I finally learned that I can turn off prepared_statements to completely avoid this issue. (See this other question and answers on StackOverflow.)

I was worried about the performance penalty, but I found many reports from people who had set prepared_statements: false, and they hadn't noticed any problems. e.g. https://news.ycombinator.com/item?id=7264171

I created a file at config/initializers/disable_prepared_statements.rb:

db_configuration = ActiveRecord::Base.configurations[Rails.env]
db_configuration.merge!('prepared_statements' => false)
ActiveRecord::Base.establish_connection(db_configuration)

This allows me to continue setting the database configuration from the DATABASE_URL env variable, and 'prepared_statements' => false will be injected into the configuration.

This completely solves the ActiveRecord::PreparedStatementCacheExpired errors and makes it much easier to achieve high-availability for my service while still being able to modify the database.

Board

Answer 2

This is solved in Rails 7 by this pull request where an option was added to prepared statements to enumerate the columns when generating those sql statements instead of using table_name.*:

https://github.com/rails/rails/pull/41718

There is no fix for Rails 6.1 and down. Only Rails 7.

Also, this only fixes it if you are adding columns. If you are removing columns then you will want to add the column to ignored_columns in the first deploy of your app like this:

class Student < ApplicationRecord
  self.ignored_columns = [:my_column_name]
end

And then in the next deploy you can drop the column.

Difficile

Thanks! specifically you need to set config.active_record.enumerate_columns_in_select_statements = true in config/application.rb to fix this in rails 7 – Dividend