Campaign workflows stuck due to temp table bloat
This article explains why the workflow temporary tables where not removed from the database, leading to overall slowdown of the platform.
Description description
A hybrid customer (the marketing instance on premise) was experiencing issues where its Campaign workflows consistently got stuck due to excessive temporary table usage, leading to high database utilization and workflow interruptions.
The database cleanup technical workflow was running but not dropping the temporary tables as it should.
Resolution resolution
Running the cleanup workflow in the verbose mode didn’t provide a clear root cause and R&D was involved. The customer was asked to run the following commands on their psql database (as the Campaign DB user) and share the full output:
-
Workflow state:
SELECT iWorkflowId, sInternalName, sLabel, iState FROM XtkWorkflow WHERE iWorkflowId IN (id1, id2, id3); -
Connection schema / search_path:
SELECT current_user, current_schema(), current_setting(‘search_path’) AS search_path; -
Workflow work tables per schema:
SELECT schemaname, COUNT AS table_count FROM pg_tables WHERE tablename LIKE ‘wkf%’ GROUP BY schemaname ORDER BY table_count DESC;
After the results were shared it was observed that the customer database setup uses a custom schema ‘XYZ’ which differed from the database login username UserNameABC. Specifically:
current_user = UserNameABC
current_schema() / search_path = XYZ
And all the workflow temporary tables were in schema ‘XYZ’.
In a standard setup, all tables reside in the public schema. The db cleanup workflow lists tables to drop by filtering on the schema derived from the database login name (i.e. UserNameABC) and public. Since tables were in ‘XYZ’ schema, the cleanup never found them and therefore never dropped any workflow tables.
In order to remediate this, the customer was asked to edit the dbSchema attribute to the < dbcnx> element in ServerConf.xml
< dbcnx … dbSchema=“XYZ” …>
Then run nlserver/apache restart and then run the cleanup workflow.