開発者エリア/よくある問題

Elasticsearchのトリガーが正しくドロップされない Elastic search triggers not dropped properly

At the moment we use DB triggers for elastic search updates, but we're looking into a better alternative. While we're working on it, some people might have this problem. 現時点では私たちはElasticsearchの更新にDBトリガーを使用していますが、より良い代替手段を検討中です。私たちが代替手段の検討に取り組んでいる間、何人かの人はこの問題に直面するかもしれません。

問題 PROBLEM

DB postgres. Having elastic search set up on the site. While running cron, we have an error that looks like this: -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.

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