What Is The Proper Way To Roll Back Multiple Transactions in Scala Skunk?

Question

I have the following scenario:

• I want to delete a row
• There may be a number of rows in the same table that reference that row. If a row is deleted, all the other rows that reference is need to be updated with either a different value or null to avoid "dangling pointers" in the DB
• If either the delete or any of the updates go wrong for whatever reason, I need to rollback the DB to the original state

First and foremost, Postgres doesn't support nested transactions

My initial take was something the lines of this, using checkpoints

      postgres.use { s =>
            s.prepare(updateGenreParent).flatMap { ps =>
              s.transaction.use { t =>
                me.headOption match {
                  case None => throw UnknownGenreError
                  case Some(dg) =>
                    dg.parent match {
                      case None => ???
                      case Some(p) => // Node is middle, realign the tree
                        val savepoint = t.savepoint
                        myChildren
                          .traverse(c => {
                            for {
                              _  <- Logger[F].println("Updating parent for " + c.uuid + " With " + p)
                              sp <- savepoint
                              _ <- ps.execute(Some(GenreParentId(p.value)) ~ c.toplevel ~ c.uuid).recoverWith {
                                case e: Exception =>
                                  Logger[F].println(e) *> t.rollback(sp)
                              }
                            } yield ()
                          })
                          .flatMap(r => {
                            s.prepare(deleteById).flatMap { ps2 =>
                              savepoint.flatMap(
                                sp =>
                                  ps2.execute(id).as(id).recoverWith {
                                    case e: Exception =>
                                      Logger[F].println(e) *> t.rollback(sp) *> ApplicativeError[F, Throwable]
                                        .raiseError(e)
                                  }
                              )
                            }
                          })
                    }
                }
              }
            }
          }
        }

I can confirm this updates and deletes as needed however I have no proof that the rollback does what it is supposed to. In the Skunk documentation, the basic rollback is demonstrated using constraints which isn't a great fit for this table or a delete query. I would somehow need to make postgres unreachable after the updates but before the delete.

In the Typelevel discord, it was suggested that having nested prepare/transaction statements is not a good idea, as it interleaves unrelated logic, which can cause some trouble. So essentially,

 s.prepare(updateGenreParent).flatMap { ps =>
    s.transaction.use { t =>
      val savepoint = t.savepoint
      ....... 
      s.prepare(deleteById).flatMap { ps2 => .... restore(savepoint)... }
    }
 }

Is not recommended. Normally one would chain the transactions with flatMap, but that results in the delete query losing track of the original savepoint.

So my question is what is the right way of performing multiple rollbacks like this in Scala + Cats + Skunk ?

The only other option I can think of is keeping track of all the changes that have been made and creating a retry loop with something like Defer to manually update all the rows to their original state. This of course creates a small loop as now you have to worry about those transactions failing and so on.

EDIT: Postgres logs from the query

2023-05-17 23:05:10.535 EDT [46120] LOG:  statement: RESET ALL
2023-05-17 23:05:10.716 EDT [46120] LOG:  statement: BEGIN
2023-05-17 23:05:10.799 EDT [46120] LOG:  statement: SAVEPOINT savepoint_6
2023-05-17 23:05:10.882 EDT [46120] LOG:  execute statement_5/portal_7:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.882 EDT [46120] DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = 'b82fb0c0-a8d4-434e-849b-f026ff198713'
2023-05-17 23:05:10.947 EDT [46120] LOG:  execute statement_5/portal_9:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.947 EDT [46120] DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = 'b82fb0c0-a8d4-434e-849b-f026ff198713'
2023-05-17 23:05:10.977 EDT [46120] LOG:  execute statement_5/portal_11:
              UPDATE genres
              SET parent_id=$1, top_level=$2
              WHERE uuid=$3

2023-05-17 23:05:10.977 EDT [46120] DETAIL:  parameters: $1 = 'b82fb0c0-a8d4-434e-849b-f026ff198713', $2 = 'b82fb0c0-a8d4-434e-849b-f026ff198713', $3 = 'e9bce582-d8e8-485c-a01d-04cee2cd26ca'
2023-05-17 23:05:11.082 EDT [46120] LOG:  execute statement_12/portal_14: DELETE FROM genres WHERE uuid = $1
2023-05-17 23:05:11.082 EDT [46120] DETAIL:  parameters: $1 = '467005e0-84fd-4729-a5cc-6fe1cd75787b'
2023-05-17 23:05:11.098 EDT [46120] LOG:  statement: COMMIT
2023-05-17 23:05:11.130 EDT [46120] LOG:  statement: UNLISTEN *
2023-05-17 23:05:11.146 EDT [46120] LOG:  statement: RESET ALL