Non-empty project cannot be deleted due to foreign key constraints in the database

[csanadtelbisz]

SysML v2 API and Services version: 2022-05 (on Jun 17, 2022)

Steps to reproduce:

• Using the api client, create a non-empty project (e.g. an empty commit).
• Send a request to delete this project.

The following simple project reproduces the problem: api-client-bug-report.zip (the project uses the SysML v2 API Java Client). Essential part:

val projectApi = ProjectApi(defaultClient)
val commitApi = CommitApi(defaultClient)
//...
val project = projectApi.postProject(Project().apply {
    name = "demo project"
})
commitApi.postCommitByProject(project.atId, Commit(), null) // An empty commit is added
projectApi.deleteProjectById(project.atId) // Exception: Internal Server Error (update or delete on table "project" violates foreign key constraint)

Expected output: the project is deleted from the database with all of its contents (commits, branches, elements, etc.).

Actual output:
on the demo project side: ApiException: Internal Server Error
in the pilot implementation server:

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[RollbackException: Error while committing the transaction]]
        at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:351)
        at play.api.http.HttpErrorHandlerExceptions.throwableToUsefulException(HttpErrorHandler.scala)
        at play.http.JsonHttpErrorHandler.throwableToUsefulException(JsonHttpErrorHandler.java:93)
        at play.http.JsonHttpErrorHandler.onServerError(JsonHttpErrorHandler.java:67)
        at play.core.j.JavaHttpErrorHandlerAdapter.$anonfun$onServerError$1(JavaHttpErrorHandlerAdapter.scala:24)
        at play.core.j.JavaHelpers.$anonfun$invokeWithContext$1(JavaHelpers.scala:290)
        at play.core.j.JavaHelpers.withContext(JavaHelpers.scala:302)
        at play.core.j.JavaHelpers.withContext$(JavaHelpers.scala:298)
        at play.core.j.JavaHelpers$.withContext(JavaHelpers.scala:311)
        at play.core.j.JavaHelpers.invokeWithContext(JavaHelpers.scala:289)
Caused by: javax.persistence.RollbackException: Error while committing the transaction
        at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:81)
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:107)
        at dao.impl.jpa.JpaDao.lambda$deleteById$2(JpaDao.java:91)
        at jpa.manager.impl.HibernateManager.transact(HibernateManager.java:58)
        at dao.impl.jpa.JpaDao.lambda$deleteById$3(JpaDao.java:86)
        at java.base/java.util.Optional.map(Optional.java:265)
        at dao.impl.jpa.JpaDao.deleteById(JpaDao.java:86)
        at services.BaseService.deleteById(BaseService.java:61)
        at controllers.ProjectController.deleteProjectById(ProjectController.java:79)
        at router.Routes$$anonfun$routes$1.$anonfun$applyOrElse$17(Routes.scala:819)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
        at org.hibernate.internal.ExceptionConverterImpl.convertCommitException(ExceptionConverterImpl.java:65)
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:107)
        at dao.impl.jpa.JpaDao.lambda$deleteById$2(JpaDao.java:91)
        at jpa.manager.impl.HibernateManager.transact(HibernateManager.java:58)
        at dao.impl.jpa.JpaDao.lambda$deleteById$3(JpaDao.java:86)
        at java.base/java.util.Optional.map(Optional.java:265)
        at dao.impl.jpa.JpaDao.deleteById(JpaDao.java:86)
        at services.BaseService.deleteById(BaseService.java:61)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3481)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3738)
        at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:99)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "project" violates foreign key constraint "fkga25en2ho7o502qrcucb6vgtc" on table "commit"
  Detail: Key (id)=(9810543c-d38c-46e5-b3db-b55b787aaf4a) is still referenced from table "commit".
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3481)