Deal properly with T_WEBUI_ViewSelection[Line]

[metas-dh]

Follow-Up for T_WEBUI_ViewSelection[Line] shall be truncated and not deleted #671

Is this a bug or feature request?

FR

What is the expected or desired behavior?

• we can't assume to have a downtime in which the truncate could be performed, so we are back at delete..we need to identify old records and remove them, on a regular base.
  • e.g. if the session of the user for which the selection was made ends
• if we clean upon a regular basis, the table will stop growing at a certain point. It's true that disk space is not returned to the OS on the system, but given a proper vacuum setup (1. we need it anyways; 2. even with the default seup this holds true), the table's disk usage won't grow boundless, because disk space will be reused.
• A solution is not the truncate, but regular restarts. Anyways you need those restarts right now. the only difference wrt the disk usage is that with truncate, you get a sawtooth pattern and with delete, a "flat line" which is aproximately at the sawtooth's maximum

[teosarca]

Implemented properly as follows:

• introduced T_WEBUI_ViewSelection_ToDelete table. In this table we are adding the UUIDs which needs to be deleted from T_WEBUI_ViewSelection and T_WEBUI_ViewSelectionLine.
• we are starting a scheduled task which removes those UUIDs from T_WEBUI_ViewSelection and T_WEBUI_ViewSelectionLine.

To configure the interval on which we are running the scheduled task, pls set in application.properties:

metasfresh.view.clearViewSelectionsRateInSeconds=1800

(1800=30min it's the default btw)

[teosarca]

As a follow-up, if this task performs well we shall think about removing the AD_Scheduler entry which is truncating the T_WEBUI_ViewSelection[Line] each night, i.e. this commit https://github.com/metasfresh/metasfresh/commit/aa1eb9cd34a89ed9af6de228f92431597bdb63b2

[metasnw]

@teosarca I think we should still leave the scheduler as we are not doing auto vacuum full and the truncate release disk space which the delete is not doing. at the end the real solution is to have this data in memory and not persisted in db in my humble opinion

[metas-ts]

@teosarca @metasnw i also think we should remove the schedule..

I don't see the need to to release that disk space. From my POV, with the scheduler all we gain is a saw tooth pattern in disk space usage, but we still need to provide the max (i.e. the saw tooth's peek) at all times. Plus, the scheduled task adds FUD, even if only comparably little.

[teosarca]

@metasnw fully agree with @metas-ts . More if it happens for a user to work at the time cleanup is scheduled, he will lose all his views and the frontend will behave weird....

[metasnw]

the problem we need to solve: this is needed to keep the disk usage on systems down with 100K records and high role limits. Where I am afraid that one day can produce several gb of used disk space a day.

suggestion: we will test the new version on high data systems and see if the new delete mechanism will prevent high disk space. If it does we can take out the scheduler. If it does not we keep it but put it to once a week.