DeletedNodeCleanupWorker fails to purge deleted nodes in DB

[luis_alventosa]

Hello,

I am using Alfresco Community - 5.2.0 (re21f2be5-b22) and I am facing some issues with deleted nodes DB purging as stated below.

Has anyone encountered the same issues?

Are there any config parameters to change in alfresco-global.properties or any manual recipe to fix this issue?

Any help would be much appreciated.

Thanks in advance,

Luis Alventosa

2021-03-27 04:49:22,821  ERROR [node.db.DeletedNodeCleanupWorker] [DefaultScheduler_Worker-10] Failed to purge nodes.
  If the purgable set is too large for the available DB resources
  then the nodes can be purged manually as well.
  Set log level to WARN for this class to get exception log:
   From commit time (ms):    1614150122119
   To commit time (ms):      1614157322119
   Error:
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"
  Detail: Key (id)=(249915836) is still referenced from table "alf_node_properties".
### The error may involve alfresco.node.delete.delete_NodesByTxnCommitTime-Inline
### The error occurred while setting parameters
### SQL: delete from alf_node         where             type_qname_id = ? and             transaction_id <=             (                 select max(txn.id) from alf_transaction txn                 where                     txn.commit_time_ms >= ? and                     txn.commit_time_ms < ?             )
### Cause: org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"
  Detail: Key (id)=(249915836) is still referenced from table "alf_node_properties".
; SQL []; ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"
  Detail: Key (id)=(249915836) is still referenced from table "alf_node_properties".; nested exception is org.postgresql.util.PSQLException: ERROR: update or delete on table "alf_node" violates foreign key constraint "fk_alf_nprop_n" on table "alf_node_properties"
  Detail: Key (id)=(249915836) is still referenced from table "alf_node_properties".

[afaust · Accepted solution]

Under a specific constellation involving concurrent deletion + cretion/change on other nodes, previous versions of Alfresco could run into issues where it tried to purge non-deleted nodes, which fails because of those foreign key constraints. This should be fixed as part of REPO-3493 for ACS 6.1. It can be worked around by manually purging deleted nodes via SQL - once all currently deleted nodes have been purged, ACS should continue normally with regular purging (at least until the specific constellation is triggered again).