BUG - Delete Samples Broken in QA and Prod

[Buwujiu]

Detailed Description

The delete samples endpoint in QA and Prod are broken.

Steps to Reproduce

QA:

Go to DELETE /studies/{studyId}/samples/{ids} endpoint in QA for the following study/analysis/sample

studyId: TEST-INTL
analysisId: 9ec36cb8-31d4-4def-836c-b831d47def90
sampleId: SA622751
songURL: https://submission-song.rdpc-qa.cancercollaboratory.org/

step 1: suppress the analysis in submission-song by call: PUT /studies/{studyId}/analysis/suppress/{id} ---- Complete
step 2: delete the sample by call: DELETE /studies/{studyId}/samples/{ids}. ----- Failed with 500 error:

2022-09-01 14:29:35,506 [http-nio-8080-exec-7] ERROR b.o.s.s.e.ServerExceptionHandler - {
  "errorId" : "unknown.error",
  "httpStatusCode" : 500,
  "httpStatusName" : "INTERNAL_SERVER_ERROR",
  "requestUrl" : "https://submission-song.rdpc-qa.cancercollaboratory.org/studies/TEST-INTL/samples/SA622751",
  "datetime" : "2022-09-01T14:29:35.504Z[UTC]",
  "timestamp" : 1662042575504,
  "message" : "Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [sampleset_sample_id_fkey]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
  "debugMessage" : "[ROOT_CAUSE] -> org.postgresql.util.PSQLException: ERROR: update or delete on table \"sample\" violates foreign key constraint \"sampleset_sample_id_fkey\" on table \"sampleset\"\n  Detail: Key (id)=(SA622751) is still referenced from table \"sampleset\"."

Prod:

I also tested the DELETE /studies/{studyId}/samples/{ids} endpoint in Prod for the following study/sample

studyId: TEST-INTL
sampleId: SA611445
songURL: https://song.rdpc.cancercollaboratory.org/

Steps to re-produce:

1. Call endpoint: GET /studies/TEST-INTL/samples/SA611445 or URL:

https://song.rdpc.cancercollaboratory.org/studies/TEST-INTL/samples/SA611445

Here is the returned response:

{
"sampleId": "SA611445",
"specimenId": "SP211445",
"submitterSampleId": "donor1_sample1_id",
"matchedNormalSubmitterSampleId": "donor1_sample2_id",
"sampleType": "Total RNA"
}

2. delete the sample by call: DELETE /studies/{studyId}/samples/{ids}. ----- Failed with 500 error:

"errorId" : "unknown.error",
  "httpStatusCode" : 500,
  "httpStatusName" : "INTERNAL_SERVER_ERROR",
  "requestUrl" : "https://song.rdpc.cancercollaboratory.org/studies/TEST-INTL/samples/SA611445",
  "datetime" : "2022-09-01T14:33:04.752Z[UTC]",
  "timestamp" : 1662042784752,
  "message" : "Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [sampleset_sample_id_fkey]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
  "debugMessage" : "[ROOT_CAUSE] -> org.postgresql.util.PSQLException: ERROR: update or delete on table \"sample\" violates foreign key constraint \"sampleset_sample_id_fkey\" on table \"sampleset\"\n  Detail: Key (id)=(SA611445) is still referenced from table \"sampleset\".",

[UmmulkiramR]

For the samples to be deleted successfully we need to also delete the sample sets which is a mapping between the analyses and the samples.
This can be done but the question is what happens to the analyses after that.
As discussed with @sifavahora @joneubank a suppressed analysis can be published again.
So, if an analysis is suppressed and the sample removed, when we publish that suppressed analysis again it will not have any sample associated with it. We need to understand if that is a valid scenario or do we need to put in place some additional checks to avoid this.

Link to the document created by @sifavahora for reference: https://wiki.oicr.on.ca/pages/viewpage.action?pageId=207356534

[Deleted user]

closing for now. Deleted samples fix by @hknahal and @joneubank.
Designing long term solution to fix discrepancy issues icgc-argo/roadmap#917