Is it OK to deactivate the Auto Flush (sys_auto_flush) record for workflow contexts?

GoBucks · ‎03-31-2017

Is it OK to deactivate the Auto Flush (sys_auto_flush) record for workflow contexts? I've read that older instances have this record. Completed workflow contexts over 180 days old are deleted because of this record. Newer instances do not appear to have this auto flush defined for workflow contexts, so I assume it is OK to set to inactive now?

Why did this exist at one time?

Mwatkins · ‎04-06-2017

Ok, Jeff, I have the answer now.

A long, long time ago, deleting the workflow context history led to an interesting problem with workflows starting afresh, years after the original flow completed. So, the Table Cleaner for Workflow Context was removed in Calgary - I did not realize it was so long ago. This was fine for a while but the flip side was that some heavy workflow customers started to see performance issues around the wf_context, wf_history, and wf_transition_history tables because they were growing so large. So, for Jakarta, we solved the original workflow restart issue and brought the workflow context cleaner back.

You can remove the Cleaner, but those tables will just continue to grow and the usefulness of the data in there is generally short lived. If you delete it, or mark it inactive, you would own the record and wouldn't get our updates to it during upgrades.

So there is the long story.

Hope this helps!

View solution in original post

Luke Morris · ‎03-31-2017

What I find really helpful is that because it's here its in the public domain for others to find.. enjoyed reading this thread!

GoBucks · ‎03-31-2017

Thanks Matthew. I noticed on an OOTB Helsinki dev instance that the wf_context auto flush record did not exist, but on my organization's (old) instance it does exist. So is this one example where a table cleaner solution has moved to a table rotation solution (at least on newer instances)?

I asked initially because when referencing Change Requests that are older than 180 days I noticed their workflow contexts were gone, which lead me to investigating why that was the case, and discovered information on the auto flush wf_context which is set to remove them once completed and older than 15,552,000 seconds (180 days).

I'll note this blog post though, which recommends removing it:

IMPORTANT: Before you build long lifecycle tasks with Workflow...

Mwatkins · ‎03-31-2017

Hm... you are right Jeff. My mistake. I assumed we were still cleaning wf_context through Table Cleaner but that has apparently changed. Let me do some more research and get back to you.

Uncle Rob · ‎03-31-2017

New instances don't do the auto-flush. Upgraded old instances do. This causes massive, MASSIVE issues for tool use cases where tasks outlive their workflows.

Mwatkins · ‎03-31-2017

Is there a known problem for these issues Robert? If so, maybe that explains why new instances do not have the sys_auto_flush record for wf_context. If not, we should definitely create one.

I've looked at two demo instances running Geneva and Istanbul and have found that both are missing the sys_auto_flush record for wf_context. However, I have also checked in our code base and the sys_auto_flush record is still there. And I checked our nightly demo (currently running J release) and the sys_auto_flush record is there. So, perhaps the missing records are an unintentional oversight or perhaps they have been added back in J release... the plot thickens.