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

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.

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