Cascade Delete Entities (contribution from TrekGlobal)

[Carlos Antonio Ruiz Gómez]

Hi community, based on the work of Paul Bowden (Adaxa) and Dirk Niemeyer (Action42), I worked with TrekGlobal to improve the "Cascade Delete Entities" plugin.

Please read documentation about here:

http://wiki.idempiere.org/en/Plugin:_Cascade_Delete

Feedback is welcome.

Regards,

Carlos Ruiz

[Norbert Bede]

Hi Carlos,

thanks for implementing this plugin. We deleted approx 10 tenants on holiday.

env: RDS 32GB server. java EC2 16GB instance.

some notes

1. slow foreign keys. We added indexes where huge data are in big tables like m_transaction, c_orderline etc. this

2. the form is slow - installed on 8.2z selecting all checkboxes from 85 to 250 took a long time, probably something can be optimized in java

3. run in the background - I tested a big tenant but for 1 table approx 300k workflows to delete, after 2 hours i got java transaction timeout

summary: I was able to delete small init tenants or small data, but can't delete tenants with 5 years' data. (approx 30k invoices + related data)

Wondering did anybody deletes successfully huge data with the plugin on AWS RDS or a bare-metal server.

I suppose implementing some changes long term would allow deleting big tenants or orgs' data

1. delete/retention schema (define the same as in checkbox and right order loaded by App Dict/FK)

2. process which does the same as form based on schema and allows deletion data in batches  (with limit/offset and continuous commit)

hope our test results are valuable.

Norbert

[Syed]

Hi Norbert,

FYI: I never tried this plugin yet but I usually use the delete script contributed by Chuck Boecking and I have faced the performance issue for a large volume of transactions.

It is true that foreign keys in the table slow down the deletion process for millions of records. To make it quick, all the triggers in the respective table should be disabled and the same should be enabled back after deleting. You can refer to the following script:

ALTER TABLE AD_WF_EventAudit DISABLE TRIGGER ALL;
ALTER TABLE AD_WF_Process DISABLE TRIGGER ALL;
delete from AD_WF_EventAudit  WHERE ad_client_id=1000000;;
delete from AD_WF_Process WHERE ad_client_id=1000000; ;
ALTER TABLE AD_WF_EventAudit ENABLE TRIGGER ALL;
ALTER TABLE AD_WF_Process ENABLE TRIGGER ALL;

The delete procedure should be written in a way to retain the data integrity since by disabling the triggers in the table we bypass the foreign key validation temporarily.

Regards,

Syed.

[Carlos Antonio Ruiz Gomez]

Hi Norbert,

Thanks for the feedback.

Two things to note:

* the form is intended for usage when the system is online and running

* the form allows also to delete partial data from tenants

When you want to delete a whole tenant, and a window maintenance is possible with the system not running, then I prefer to use this script:

DeleteIdempiereClient_pg

When performance is concerning, then I usually disable/enable all foreign keys before/after running the script.

Disabling foreign keys leaves also the problem that it doesn't help to discover potential cross-tenant data corruption issues.

About your suggestions:

> 1. slow foreign keys. We added indexes where huge data are in big tables like m_transaction, c_orderline etc. this

Yes, indexes on foreign keys improve heavily the delete/update

> 2. the form is slow - installed on 8.2z selecting all checkboxes from 85 to 250 took a long time, probably something can be optimized in java

Haven't tried lately, when I tested was OK, maybe there is room for improvement, AFAIR clicking on a table detects all its children and automatically enable them, so maybe that could be a heavy process.

> 3. run in the background - I tested a big tenant but for 1 table approx 300k workflows to delete, after 2 hours i got java transaction timeout

Yes, we have this transaction timeout limit hardcoded in iDempiere, I think it can be changed via customization.

> summary: I was able to delete small init tenants or small data, but can't delete tenants with 5 years' data. (approx 30k invoices + related data)
> Wondering did anybody deletes successfully huge data with the plugin on AWS RDS or a bare-metal server.

In general for complete tenants I would go with the script mentioned at the beginning of this message.

> I suppose implementing some changes long term would allow deleting big tenants or orgs' data
> 1. delete/retention schema (define the same as in checkbox and right order loaded by App Dict/FK)

Yes, this sounds like a good improvement, also would be good to allow defining it in batches as your next point.

> 2. process which does the same as form based on schema and allows deletion data in batches  (with limit/offset and continuous commit) *

The form allows to delete partial data, indeed originally was intended to delete just transaction data, so you can do it in batches, I have done this, but selecting the batches manually is a bit overwhelming.

Regards,

Carlos Ruiz

En 06/01/23 04:02, Syed escribió: