Concourse: Destroy pipelines yields 500 error

[drahnr]

Summary

Attempting to destroy a pipeline yields an error 500

Steps to reproduce

# fly -t spearow destroy-pipeline --team spearow -p juice
!!! this will remove all data for pipeline `juice`

are you sure? [yN]: y
error: Unexpected Response
Status: 500 Internal Server Error
Body:


# fly -t spearow login -n spearow
logging in to team 'spearow'

navigate to the following URL in your browser:

  https://ci.spearow.io/login?fly_port=46253

or enter token manually (input hidden): 
target saved

# fly -t spearow destroy-pipeline --team spearow -p juice
!!! this will remove all data for pipeline `juice`

are you sure? [yN]: y
error: Unexpected Response
Status: 500 Internal Server Error
Body:

Expected results

Pipeline destroyed

Actual results

Error 500

Additional context

Reverse proxy

Feb 24 10:44:51  [24/Feb/2021:10:44:51 +0000] "GET /api/v1/info HTTP/1.1" 200 82 "-" "Go-http-client/1.1"
Feb 24 10:44:51  [24/Feb/2021:10:44:51 +0000] "GET /api/v1/workers HTTP/1.1" 200 588 "-" "Go-http-client/1.1"
Feb 24 10:44:51  [24/Feb/2021:10:44:51 +0000] "DELETE /api/v1/teams/spearow/pipelines/juice HTTP/1.1" 500 0 "-" "Go-http-client/1.1"
Feb 24 10:44:53  [24/Feb/2021:10:44:53 +0000] "GET /api/v1/info HTTP/1.1" 200 82 "-" "Go-http-client/1.1"
Feb 24 10:44:53  [24/Feb/2021:10:44:53 +0000] "GET /api/v1/workers HTTP/1.1" 200 588 "-" "Go-http-client/1.1"

relevant tsa log

{"timestamp":"2021-02-24T10:49:57.991534392Z","level":"info","source":"atc","message":"atc.destroying-pipeline.start","data":{"name":"juice","session":"258124"}}
<< snip >>
{"timestamp":"2021-02-24T10:49:58.905670721Z","level":"error","source":"atc","message":"atc.destroying-pipeline.failed","data":{"error":"pq: update or delete on table \"jobs\" violates foreign key constraint \"build_image_resource_caches_job_id_fkey\" on table \"build_image_resource_caches\"","name":"juice","session":"258124"}}

Triaging info

• Concourse version: 7.0.0
• Browser (if applicable): -
• Did this used to work? yes

[taylorsilva]

I don't know enough about this one but @clarafu has done lots of work in this area.

[clarafu]

Oh thanks for submitting this issue! This is indeed a bug and was probably introduced with this migration https://github.com/concourse/concourse/blob/86090499939d89fe384b4a587ac47708288052fe/atc/db/migration/migrations/1609958558_add_job_id_to_build_image_resource_caches.up.sql#L2-L3 where the reference to job_id should be ON DELETE CASCADE. Without the ON DELETE CASCADE it will have ON DELETE NO ACTION (similar to ON DELETE RESTRICT) which will restrict the parent from being deleted if it is referenced by any rows in this table.

[drahnr]

Thanks!