CardinalityViolation 例外、データベース破損?

最近、GNOME の Discourse 環境で以下のエラーが表示されるようになりました:

/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.4/lib/patches/db/pg.rb:69:in `exec_params'
/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.4/lib/patches/db/pg.rb:69:in `exec_params'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `block (2 levels) in exec_no_cache'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:671:in `block in exec_no_cache'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:718:in `block (2 levels) in log'
/usr/local/lib/ruby/2.6.0/monitor.rb:235:in `mon_synchronize'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:717:in `block in log'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:708:in `log'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:670:in `exec_no_cache'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `execute_and_clear'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `exec_query'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:487:in `select'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:70:in `select_all'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:105:in `block in select_all'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:123:in `block in cache_sql'
/usr/local/lib/ruby/2.6.0/monitor.rb:235:in `mon_synchronize'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:114:in `cache_sql'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:105:in `select_all'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/querying.rb:46:in `find_by_sql'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:810:in `block in exec_queries'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:828:in `skip_query_cache_if_necessary'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:797:in `exec_queries'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:615:in `load'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:250:in `records'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation.rb:245:in `to_ary'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation/finder_methods.rb:528:in `find_nth_with_limit'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation/finder_methods.rb:513:in `find_nth'
/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/relation/finder_methods.rb:120:in `first'
/discourse/app/services/post_alerter.rb:173:in `first_unread_post'
/discourse/app/services/post_alerter.rb:360:in `create_notification'
/discourse/app/services/post_alerter.rb:615:in `block in notify_post_users'
/discourse/app/services/post_alerter.rb:613:in `each'
/discourse/app/services/post_alerter.rb:613:in `notify_post_users'
/discourse/app/services/post_alerter.rb:97:in `after_save_post'
/discourse/app/jobs/regular/post_alert.rb:11:in `execute'
/discourse/app/jobs/base.rb:232:in `block (2 levels) in perform'
/discourse/vendor/bundle/ruby/2.6.0/gems/rails_multisite-2.0.7/lib/rails_multisite/connection_management.rb:63:in `with_connection'
/discourse/app/jobs/base.rb:221:in `block in perform'
/discourse/app/jobs/base.rb:217:in `each'
/discourse/app/jobs/base.rb:217:in `perform'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:196:in `execute_job'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:164:in `block (2 levels) in process'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/middleware/chain.rb:138:in `block in invoke'
/discourse/lib/sidekiq/pausable.rb:138:in `call'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/middleware/chain.rb:140:in `block in invoke'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/middleware/chain.rb:143:in `invoke'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:163:in `block in process'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:136:in `block (6 levels) in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/job_retry.rb:111:in `local'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:135:in `block (5 levels) in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/rails.rb:43:in `block in call'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/execution_wrapper.rb:88:in `wrap'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/reloader.rb:72:in `block in wrap'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/execution_wrapper.rb:88:in `wrap'
/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.1/lib/active_support/reloader.rb:71:in `wrap'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/rails.rb:42:in `call'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:131:in `block (4 levels) in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:257:in `stats'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:126:in `block (3 levels) in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/job_logger.rb:13:in `call'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:125:in `block (2 levels) in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/job_retry.rb:78:in `global'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:124:in `block in dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/logger.rb:10:in `with'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/job_logger.rb:33:in `prepare'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:123:in `dispatch'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:162:in `process'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:78:in `process_one'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/processor.rb:68:in `run'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/util.rb:15:in `watchdog'
/discourse/vendor/bundle/ruby/2.6.0/gems/sidekiq-6.0.4/lib/sidekiq/util.rb:24:in `block in safe_thread'

Env タブの内容:

hostname	[sidekiq-35-2zhwc, sidekiq-35-4999r, sidekiq-35-m4rbw]
process_id	1
application_version	8a89b7e108b9f0c56dfe5506e04275024da5e310
current_db	default
current_hostname	discourse.gnome.org
job	Jobs::PostAlert
problem_db	default
time	日曜日 11:29
	
opts	null
post_id	8065
new_record	true
current_site_id	default

実際のクエリが何なのかがよくわかりません。select id from posts where id = 8065; は 1 件の結果だけを返します。

カスタムイメージを使用しているため、公式のデプロイメントサポート対象外であることを承知していますが、このエラーは「汎用的」なものに見えます。

推測ですが、重複エラーは post_number で発生しており、トピックごとに一意である必要があります。おそらく、そこに破損したインデックスがあるのでしょう。修正としては、問題のある投稿を見つけて番号を振り直す方法が考えられます。

これで、なぜ標準インストールのみをサポートしているのか、少しはご理解いただけたでしょうか?:wink: 私たちにはそれなりの理由があるのですが、あなた個人の「狂気」は、ご自身で完全に責任を持って楽しむ特権となりますね .. :zany_face:

どうもありがとうございます。データベースをさらに詳しく調べてみたところ、明らかに破損していることがわかりました。多くの投稿で post_number や sort_order が重複しており、select * from posts where topic_id = x では表示される ID が、and id = y を追加すると表示されなくなっていました。

@Jeff、あなたの判断が理解できないと言ったことはありません!もしデプロイ固有の問題であれば、助けを求めることはしなかったでしょう。