Technology sharing

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

1. Quaeritur:

• Aliquando cum in Postgres applicationis Rails explicandis, videre potes errorem ActiveRecord ::PreparedStatementCacheExpired. Hoc solum fit cum migrationes in currendo instruere possunt.
• Hoc accidit quod Rails commodum Postgres' praeparatae constitutionis cellae (PreparedStatementCache) pluma ut meliorem efficiendi rationem obtinet. Haec factura per defaltam in cancello est potens.

2. Problema recursu:

• Rspec possumus uti ad hunc errorem

 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. Principium productionis;

• Rails quaesitum est utUser.allper active_record Parse in sql diciturDeinde mitte datorum;Facite primum PRAEPARO Paratae constitutiones, SQL declarationes, parsed, resolvitur, optimized et revocetur.Cum sequiturPraeesset EXSECUTOR imperiumpropositio praeparata et instituenda et exsecutioni mandanda.
• Cancellos servabit verbum interrogationispg_prepared_statementsad faciliorem postero tempore vocas similia dicta.faciatis directe declarationes, pro parsing, examinare, optimizing, vitando duplicationem laboris et efficientiam meliorem.

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, enunciatio praeparata cache infirmabitur si schema mensae mutatur ad reditus proventus afficiendos. Speciatim est operationes DDL ut agros mensae addendo et delens, vel agrorum rationem et longitudinem modificando.

Ut in hoc exemplo, cum SELECTO EXsequens, additis vel delendis agris;pg databaseet mittentcached plan must not change result typeIn cancelloactive_recordHoc errore et mittentActiveRecord::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

• Cum migrationem addendi, minuendi vel modificandi agros in servitio instruere, quaestionis propositio ab utente edita SQL e prompta constitutionis cache statim afferet et directe excute faciet. paratus dicitur cache invalidum erit.pg databaseet mittentcached plan must not change result typeerror
• View active_record source codeexec_cacheMethodum, inveni modum erroris pertractandi cancellos pro pg:
  1. In negotio, directe mittetur raise ActiveRecord::PreparedStatementCacheExpired.new(e.cause.message)
  2. Qui extra rem erit cache @statementsDelere hanc sententiam et attententPost retentationem, propositio sql rursus parsed, resolvitur, optimized erit et exsecutioni mandabitur.prepare_statementdicitur modum in parato 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

• Ergo hic error, qui in transactione apparet, causa transactionis revolvi debet. Nam negotium, significat quod petitio defecit et nos tractare debemus.

4. Solutio:

1. inactivare cache paratus dicitur munus (non commendatae)

Rails6 et supra hanc notam debilitare possunt, si parata sunt dicta in database ad falsum.

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

Rails6 et infra non probatum est. Si superius opus non est, novum fasciculum initializationem creare potes.

# 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

cognoscere;

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

Conclusio: In parvis inceptis, non refert si hoc munus disable, et effectus fere carebit. sic diiudicare potes utrum secundum rei condicionem disiungas.

2. facereselect * fietselect id, nameTales agros specificos in rails7Publica solutionId est

• Set enumerate_columns_in_select_statementa vera in rails7

# 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

• Nulla talis conformatio infra cancellos non est, neglecta_columnis uti potes ad eam consequendam

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

Conclusio: Problema cum hac solutione est quod agros addendo perfecte solvi possunt, sed deletis agris adhuc errores causant. Exempli gratia, deleto nomine agri, si nomen in propositione praeparata id lego, nomen ab utentibus non est; Error nuntiavit

3. Sileo cancellos application

• Cache vita cyclus enuntiationis praeparatae solum in una sessione datorum existit. Si nexus datorum claudatur (remoto applicatione primo nexum claudet et novam connexionem redintegret), originalis enuntiatio praeparata purgabitur ac patebit. SQL petitio post sileo erit sileo.

Conclusio: Reprimendi applicationis servitium temporale 502 Unavailable causa erit. Utique, cum applicationis explicas, etiam servitium sileo debes, et 502 etiam apparebit, ut melius explicandum sit cum nemo ad illud accesserit. media nocte^), ut quam paucissimi errores appareantPreparedStatementCacheExpiredReferre errorem

4. Rewrite transaction methodo

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

• Post rescriptum, mutatur locus ubi res gestae in codice scriptae sunt uti ApplicationRecord.transaction do ... end or *MyModel.transaction

Conclusio: Praecipua nota: Si electronicas mittas, stipes ad APIs, vel alias operationes quae inter se occurrunt cum extra mundum ut in parte transactionis, hoc fortasse aliquas harum operationum interdum bis occurrere potest. Quam ob rem Rails publice retries automatice non facit, sed eam ad applicationes tincidunt relinquit.

>>>>>>> Cum hanc methodum me probare, errores adhuc invenio.

5. Manually dictum paratus purgare cache

 ActiveRecord::Base.connection.clear_cache!
1

5. Final responsum

Non invenire solutionem perfectam