Sweep job fails to cleanup REPLICATION executions (foreign key constraint violation)

[dkulchinsky]

Expected behavior and actual behavior:

appears to be similar to #19494, the OP there dropped all GARBAGE_COLLECTION related records from execution and task table, wondering if this is the only path to resolve this?

EXECUTION_SWEEP job is failing after upgrade from v2.7.3 to v2.8.5

2024-06-13T14:00:01Z [ERROR] [/jobservice/runner/redis.go:122]: Job 'EXECUTION_SWEEP:edb2842760a49ae7db6bf240' exit with error: run error: {"errors":[{"code":"UNKNOWN","message":"failed to delete executions: ERROR: update or delete on table \"execution\" violates foreign key constraint \"task_execution_id_fkey\" on table \"task\" (SQLSTATE 23503)"}]}

Full Job log suggests that clean up of REPLICATION execution is failing:

coredb=# select content from job_log where job_uuid='edb2842760a49ae7db6bf240';
                                                                                                                                        content
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:91]: start to run sweep job                                                                                                                                                                                                      +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [EXECUTION_SWEEP] start to sweep, retain latest 10 executions                                                                                                                                                              +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [EXECUTION_SWEEP] listed 1 candidate executions for sweep                                                                                                                                                                  +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [EXECUTION_SWEEP] end to sweep, 1 executions were deleted in total, elapsed time: 18.715387ms                                                                                                                              +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [SCAN_ALL] start to sweep, retain latest 5 executions                                                                                                                                                                      +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [SCAN_ALL] listed 0 candidate executions for sweep                                                                                                                                                                         +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [SCAN_ALL] end to sweep, 0 executions were deleted in total, elapsed time: 4.98706ms                                                                                                                                       +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [SCAN_DATA_EXPORT] start to sweep, retain latest 50 executions                                                                                                                                                             +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [SCAN_DATA_EXPORT] listed 0 candidate executions for sweep                                                                                                                                                                 +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [SCAN_DATA_EXPORT] end to sweep, 0 executions were deleted in total, elapsed time: 1.191439ms                                                                                                                              +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [SYSTEM_ARTIFACT_CLEANUP] start to sweep, retain latest 50 executions                                                                                                                                                      +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [SYSTEM_ARTIFACT_CLEANUP] listed 0 candidate executions for sweep                                                                                                                                                          +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [SYSTEM_ARTIFACT_CLEANUP] end to sweep, 0 executions were deleted in total, elapsed time: 5.171136ms                                                                                                                       +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [WEBHOOK] start to sweep, retain latest 50 executions                                                                                                                                                                      +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [WEBHOOK] listed 0 candidate executions for sweep                                                                                                                                                                          +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [WEBHOOK] end to sweep, 0 executions were deleted in total, elapsed time: 2.375106ms                                                                                                                                       +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [GARBAGE_COLLECTION] start to sweep, retain latest 50 executions                                                                                                                                                           +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [GARBAGE_COLLECTION] listed 0 candidate executions for sweep                                                                                                                                                               +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [GARBAGE_COLLECTION] end to sweep, 0 executions were deleted in total, elapsed time: 3.933876ms                                                                                                                            +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [P2P_PREHEAT] start to sweep, retain latest 50 executions                                                                                                                                                                  +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [P2P_PREHEAT] listed 0 candidate executions for sweep                                                                                                                                                                      +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [P2P_PREHEAT] end to sweep, 0 executions were deleted in total, elapsed time: 1.203899ms                                                                                                                                   +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [PURGE_AUDIT_LOG] start to sweep, retain latest 10 executions                                                                                                                                                              +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:160]: [PURGE_AUDIT_LOG] listed 0 candidate executions for sweep                                                                                                                                                                  +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:180]: [PURGE_AUDIT_LOG] end to sweep, 0 executions were deleted in total, elapsed time: 3.830574ms                                                                                                                               +
 2024-06-13T14:00:00Z [INFO] [/pkg/task/sweep_job.go:150]: [REPLICATION] start to sweep, retain latest 50 executions                                                                                                                                                                  +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:160]: [REPLICATION] listed 608 candidate executions for sweep                                                                                                                                                                    +
 2024-06-13T14:00:01Z [ERROR] [/pkg/task/sweep_job.go:175]: [REPLICATION] failed to batch clean candidates, error: failed to delete executions: ERROR: update or delete on table "execution" violates foreign key constraint "task_execution_id_fkey" on table "task" (SQLSTATE 23503)+
 2024-06-13T14:00:01Z [ERROR] [/pkg/task/sweep_job.go:110]: [REPLICATION] failed to run sweep, error: failed to delete executions: ERROR: update or delete on table "execution" violates foreign key constraint "task_execution_id_fkey" on table "task" (SQLSTATE 23503)             +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:150]: [RETENTION] start to sweep, retain latest 50 executions                                                                                                                                                                    +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:160]: [RETENTION] listed 0 candidate executions for sweep                                                                                                                                                                        +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:180]: [RETENTION] end to sweep, 0 executions were deleted in total, elapsed time: 21.294104ms                                                                                                                                    +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:150]: [SLACK] start to sweep, retain latest 50 executions                                                                                                                                                                        +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:160]: [SLACK] listed 0 candidate executions for sweep                                                                                                                                                                            +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:180]: [SLACK] end to sweep, 0 executions were deleted in total, elapsed time: 1.200016ms                                                                                                                                         +
 2024-06-13T14:00:01Z [INFO] [/pkg/task/sweep_job.go:115]: end to run sweep job                                                                                                                                                                                                       +

Steps to reproduce the problem:
Unsure, we have upgraded from v2.7.3 to v2.8.5 and started observing the EXECUTION_SWEEP failing since.

Versions:
Please specify the versions of following systems.

• harbor version: 2.8.5
• docker engine version: N/A
• docker-compose version: N/A

Additional context:

[chlins]

Closed as resolved by #20603.