From Mahara Wiki
Revision as of 04:21, 26 December 2021 by Mits (Created page with "=== 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 wor...")
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.
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.
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.
- search for the trigger name in pg_trigger table
select oid,tgname,tgfoid from pg_trigger where tgname ='search_elasticsearch_interaction_forum_post';
- 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
- 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