Actions

Difference between revisions of "開発者エリア/よくある問題"

From Mahara Wiki

Line 6: Line 6:
 
DB postgres.
 
DB postgres.
 
データベース: PostgreSQL
 
データベース: PostgreSQL
Having elastic search set up on the site.
 
 
サイトでのElasticsearch設定中
 
サイトでのElasticsearch設定中
While running cron, we have an error that looks like this:
 
 
cronの実行中に以下のようなエラーメッセージが表示されます:
 
cronの実行中に以下のようなエラーメッセージが表示されます:
 
-5: ERROR:  trigger "search_elasticsearch_interaction_forum_post" for relation "interaction_forum_post" already exists] in EXECUTE("CREATE TRIGGER "search_elasticsearch_interaction_forum_post" BEFORE INSERT OR UPDATE OR DELETE ON "interaction_forum_post"
 
-5: ERROR:  trigger "search_elasticsearch_interaction_forum_post" for relation "interaction_forum_post" already exists] in EXECUTE("CREATE TRIGGER "search_elasticsearch_interaction_forum_post" BEFORE INSERT OR UPDATE OR DELETE ON "interaction_forum_post"
  
 
This means the cron got stuck while trying to drop/re-create the triggers.
 
This means the cron got stuck while trying to drop/re-create the triggers.
 +
これはcronがトリガをドロップ/再作成しようとしてスタックしたことを意味します。
  
 
==== 解決方法 SOLUTION ====
 
==== 解決方法 SOLUTION ====

Revision as of 13:27, 9 January 2022

Elasticsearchのトリガーが正しくドロップされない

現時点では私たちはElasticsearchの更新にDBトリガーを使用していますが、より良い代替手段を検討中です。私たちが代替手段の検討に取り組んでいる間、何人かの人はこの問題に直面するかもしれません。

問題 PROBLEM

DB postgres. データベース: PostgreSQL サイトでのElasticsearch設定中 cronの実行中に以下のようなエラーメッセージが表示されます: -5: ERROR: trigger "search_elasticsearch_interaction_forum_post" for relation "interaction_forum_post" already exists] in EXECUTE("CREATE TRIGGER "search_elasticsearch_interaction_forum_post" BEFORE INSERT OR UPDATE OR DELETE ON "interaction_forum_post"

This means the cron got stuck while trying to drop/re-create the triggers. これはcronがトリガをドロップ/再作成しようとしてスタックしたことを意味します。

解決方法 SOLUTION

We need to manually remove the trigger that was not dropped. And also check if we need to delete the record in pg_depend table.

  1. search for the trigger name in pg_trigger table
select oid,tgname,tgfoid from pg_trigger where tgname ='search_elasticsearch_interaction_forum_post';
  1. with that result, if there's a record in pg_depend where pg_depend.refobjid=pg_trigger.tgfoid and pg_depend.objid=pg_trigger.oid, then delete it
  2. delete the trigger with name 'search_elasticsearch_interaction_forum_post'
This is how the tables should look like when everything's ok:
select oid,tgname,tgfoid from pg_trigger where tgname like '%elastic%';
  oid    |                   tgname                    |  tgfoid  
14471173 | search_elasticsearch_usr                    | 14471170
14471174 | search_elasticsearch_interaction_instance   | 14471170
14471175 | search_elasticsearch_interaction_forum_post | 14471170
14471176 | search_elasticsearch_group                  | 14471170
14471177 | search_elasticsearch_view                   | 14471170
14471178 | search_elasticsearch_artefact               | 14471171
14471179 | search_elasticsearch_view_artefact          | 14471172
14471180 | search_elasticsearch_block_instance         | 14471170
14471181 | search_elasticsearch_collection             | 14471170
select objid,refobjid from pg_depend where refobjid in (14471170,14471171,14471172);
  objid  | refobjid  
14471173 | 14471170 
14471174 | 14471170 
14471175 | 14471170 
14471176 | 14471170 
14471177 | 14471170 
14471178 | 14471171 
14471179 | 14471172 
14471180 | 14471170 
14471181 | 14471170 
select oid,proname from pg_proc where proname like '%elas%';
  oid    |                   proname                   
14471170 | search_elasticsearch_queue_trigger
14471171 | search_elasticsearch_queue_artefact_trigger
14471172 | search_elasticsearch_queue2_trigger