sql: testing & clarification on special restart savepoint

[jseldess]

PR: cockroachdb/cockroach#46194

From release notes:

• CockroachDB still considers the name cockroach_restart special in SAVEPOINTs. A savepoint defined with the name cockroach_restart is a "restart savepoint" and has different semantics than standard savepoints:

  1. Restart savepoints must be opened immediately when the transaction starts. Opening a restart savepoint after other statements have been executed is not allowed. In contrast, standard savepoints can be opened after other statements have already been executed.
  2. After a successful RELEASE, a restart savepoint does not allow further use of the transaction. COMMIT must immediately follow the RELEASE.
  3. Restart savepoints cannot be nested. Issuing SAVEPOINT cockroach_restart two times in a row only creates a single savepoint marker. This can be seen with SHOW SAVEPOINT STATUS. Issuing SAVEPOINT cockroach_restart after ROLLBACK TO SAVEPOINT cockroach_restart reuses the marker instead of creating a new one. In contrast, two SAVEPOINT statements with a standard savepoint name, or a SAVEPOINT statement immediately after a ROLLBACK, create two distinct savepoint markers.

  Note: The session variable force_savepoint_restart still works and causes every savepoint name to become equivalent to cockroach_restart with the special semantics described above. [#46194][#46194]

[awoods187]

savepoint https://airtable.com/tblD3oZPLJgGhCmch/viwOj1Qb7D4gjtemc/recxI0iMnCxdfpS2h

[rmloveland]

Closing as a dupe since the information in this release note is covered in the PR for #5953