Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. Question:

• Sometimes when deploying a Rails application on Postgres, you may see an ActiveRecord::PreparedStatementCacheExpired error. This only happens when you run migrations in your deployment.
• This happens because Rails uses Postgres' PreparedStatementCache feature to improve performance. This feature is enabled by default in Rails.

2. Problem reproduction:

• We can reproduce this error using rspec

 it 'not raise ActiveRecord::PreparedStatementCacheExpired' do
    create(:user)
    User.first
    User.find_by_sql('ALTER TABLE users ADD new_metric_column integer;')
    ActiveRecord::Base.transaction { User.first }
  end
1
2
3
4
5
6

3. The principle of generation:

• Rails query statements such asUser.allActive_record Parse into sql statementThen, send it to the database.Execute PREPARE firstPrepared statements, sql statements will be parsed, analyzed, optimized and rewritten.Issue an EXECUTE commandWhen the prepared statement is executed, it is planned and executed.
• Rails will save the query statement topg_prepared_statementsTo facilitate the next call to the same statementDirectly execute statements without having to parse, analyze, and optimize them, thus avoiding duplication of work and improving efficiency.

User.first
User.all
# 执行上面的2个查询后，用connection.instance_variable_get(:@statements)就可以看到缓存的准备语句
ActiveRecord::Base.connection.instance_variable_get(:@statements)
==> <ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::StatementPool:0x00000001086b13c8 
@cache={78368=>{""$user", public-SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 
$1"=>"a7", ""$user", public-SELECT "users".* FROM "users" /* loading for inspect */ LIMIT $1"=>"a8"}},
@statement_limit=1000, @connection=#<PG::Connection:0x00000001086b31a0>, @counter=8>

# 这个也可以看到，会在数据库中去查询
ActiveRecord::Base.connection.execute('select * from pg_prepared_statements').values
(0.5ms) select * from pg_prepared_statements
==> [["a7", "SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1", "2024-07-
11T07:03:06.891+00:00", "{bigint}", false], ["a8", "SELECT "users".* FROM "users" /* loading for inspect 
*/ LIMIT $1", "2024-07-11T07:04:47.772+00:00", "{bigint}", false]]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• In Postgres, if the table schema is changed in a way that affects the results returned, the prepared statement cache will be invalidated. Specifically, this means adding or deleting fields from a table, or modifying the type or length of a field.

As in the following example, when you execute SELECT after adding or deleting a field,pg databaseIt will throwcached plan must not change result type, in railsactive_recordGet this error and then throwActiveRecord::PreparedStatementCacheExpired

ALTER TABLE users ADD COLUMN new_column integer;
ALTER TABLE users DROP COLUMN old_column;
添加或删除列，然后执行 SELECT *
删除 old_column 列然后执行 SELECT users.old_column
1
2
3
4

• When running migrations to add, delete, or modify fields in the deployment service, the query statements issued by the user will be directly executed from the prepared statement cache. However, because the table structure has changed, the prepared statement cache becomes invalid.pg databaseIt will throwcached plan must not change result typemistake
• View the active_record source codeexec_cacheMethod, and found that rails handles this error of pg as follows:
  1. In the transaction, it will be thrown directly raise ActiveRecord::PreparedStatementCacheExpired.new(e.cause.message)
  2. The cache @statements will be cached outside the transactionDelete this sentence and tryAfter retrying, the SQL statement will be parsed, analyzed, optimized and executed again.prepare_statementMethod into the prepared statement cache

module ActiveRecord
  module ConnectionHandling
    def exec_cache(sql, name, binds)
      materialize_transactions
      mark_transaction_written_if_write(sql)
      update_typemap_for_default_timezone

      stmt_key = prepare_statement(sql, binds)
      type_casted_binds = type_casted_binds(binds)

      log(sql, name, binds, type_casted_binds, stmt_key) do
        ActiveSupport::Dependencies.interlock.permit_concurrent_loads do
          @connection.exec_prepared(stmt_key, type_casted_binds)
        end
      end
    rescue ActiveRecord::StatementInvalid => e
      raise unless is_cached_plan_failure?(e)

      # Nothing we can do if we are in a transaction because all commands
      # will raise InFailedSQLTransaction
      if in_transaction?
        raise ActiveRecord::PreparedStatementCacheExpired.new(e.cause.message)
      else
        @lock.synchronize do
          # outside of transactions we can simply flush this query and retry
          @statements.delete sql_key(sql)
        end
        retry
      end
    end
  end
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

• Therefore, this error in the transaction will cause the transaction to roll back, which means the request failed for the business, and we need to handle it ourselves.

4. Solution:

1. Disable cached prepared statements (not recommended)

Rails6 and above can disable this feature by setting prepared_statements in the database to false

default: &default
  adapter: postgresql
  encoding: unicode
  prepared_statements: false
1
2
3
4

Rails 6 and below have not been tested. If the above does not work, you can try to create a new initialization file

# 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)
1
2
3
4

verify:

User.all
ActiveRecord::Base.connection.execute('select * from pg_prepared_statements').values
==> []
1
2
3

Conclusion: It doesn't matter if you disable this feature in a small project, as it has almost no impact on performance. However, in a large project, the more users there are and the more complex the query statements are, the greater the benefit this feature will bring. Therefore, you can decide whether to disable it based on the actual situation.

2. Makeselect * becomesselect id, nameSuch specific fields, in rails7Official SolutionThat's it

• In Rails 7, enumerate_columns_in_select_statements is set to true

# config/application.rb
module MyApp
  class Application < Rails::Application
    config.active_record.enumerate_columns_in_select_statements = true
  end
end
1
2
3
4
5
6

• Rails7 and below do not have this configuration, you can use ignored_columns to achieve

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  #__fake_column__是自定义的，不要是某个表中的字段就行，如果是[:id],那么 User.all就会被解析为select name from users,没有id了
  self.ignored_columns = [:__fake_column__] 
end
1
2
3
4
5

Conclusion: The problem with this solution is that adding fields can solve the problem perfectly, but deleting fields will still cause errors. For example, after deleting the name field, the name in the prepared statement select id, name from users no longer exists, and an error will be reported. The official solution of rails7 also has this problem.

3. Restart the rails application

• The life cycle of the prepared statement cache only exists in a database session. If you close the database connection (restarting the application will close the original connection and re-establish a new connection), the original prepared statement cache will be cleared, and the SQL request after the restart will re-cache the prepared statements and get the data normally.

Conclusion: When you restart the application, the service will be temporarily unavailable, and of course, when you deploy the application, you also need to restart the service, and 502 will appear, so it is best to deploy it when no one is accessing it (in the middle of the night?), so that it will appear as little as possible.PreparedStatementCacheExpiredReport an error

4. Rewrite transaction method

class ApplicationRecord < ActiveRecord::Base
  class << self
    def transaction(*args, &block)
      retried ||= false
      super
    rescue ActiveRecord::PreparedStatementCacheExpired
      if retried
        raise
      else
        retried = true
        retry
      end
    end
  end
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• After rewriting, the place where the transaction is written in the code is changed to use ApplicationRecord.transaction do ... end orMyModel.transaction

Conclusion: Important: If you are sending emails, posting to an API, or performing other operations that interact with the outside world in your transaction, this may cause some of these operations to happen twice occasionally. This is why Rails does not officially perform retries automatically, but leaves it to the application developer.

>>>>>>>I tested this method myself and it still reported an error

5. Manually clear the prepared statement cache

 ActiveRecord::Base.connection.clear_cache!
1

5. Final Answer

No perfect solution has been found