[YSQL] Feature Support - Transaction Control Commands

[nocaway]

Jira Link: DB-1016
The following transaction control commands are not yet supported.

• PREPARE
• COMMIT PREPARED
• ROLLBACK PREPARED
• DEFERRABLE transaction

As well as savepoint related commands: #9219

[jaki]

DEFERRABLE is covered in issue #2161.

[andrew-s]

I'd like to see this, my particular use case is for automated migrations.

[danports]

Would be nice to have this for pgloader, which uses SAVEPOINT:

Database error 0A000: SAVEPOINT <transaction> not supported yet
>        HINT: See https://github.com/YugaByte/yugabyte-db/issues/1125. Click '+' on the description to raise its priority
>        QUERY: savepoint pgloader;

[ddorian]

SAVEPOINT is also used in Elixir ecto_sql for database migrations. Mentioned in slack.

[Amandeepsinghghai]

package yb

import (
	"context"
	"database/sql"

	"github.com/lib/pq"
)

// https://github.com/lib/pq/blob/master/error.go#L237
const serializationFailureErrorCode string = "40001"

// ExecuteTx runs fn inside a transaction and retries it as needed.
// On non-retryable failures, the transaction is aborted and rolled back;
// On success, the transaction is committed.
func ExecuteTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) (e error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	defer func() {
		if err == nil {
			_ = tx.Commit()
		} else {
			_ = tx.Rollback()
		}
	}()

	for {
		err = fn(tx)
		if err == nil || !isErrRetryable(err) {
			return err
		}

		// Use SAVEPOINT AND RELEASE SAVEPOINT when https://github.com/YugaByte/yugabyte-db/issues/1125 is resolved
		err = tx.Rollback()
		if err != nil {
			return err
		}
	}
}

// An error has a proximate cause if it's type is compatible with Go's errors.Unwrap()
// and if possible, return the original cause of the error.
func errorCause(err error) error {
	for err != nil {
		if c, ok := err.(interface{ Unwrap() error }); ok {
			err = c.Unwrap()
		} else {
			break
		}
	}

	return err
}

func isErrRetryable(err error) bool {
	switch e := errorCause(err).(type) {
	case *pq.Error:
		return string(e.Code) == serializationFailureErrorCode
	default:
		return false
	}
}

This is the golang code I ended up writing for transactions. Since SAVEPOINT is not yet there in YB, I have to retry the transaction from scratch(if it is a retryable error) instead of a SAVEPOINT.

[marcusbooyah]

SAVEPOINT is also used by Harbor

[davidkellis]

+1'ed due to the Sequel (http://sequel.jeremyevans.net/) library's use of savepoints

[venkatramanm]

+1'ed SavePoint is crucial for my application.

[skcc321]

+1 SAVEPOINT is widely used in RoR test suites as transactional_fixtures or database_cleaner.strategy :transaction. So right now I have no confidence in Yugabyte compatibility with my legacy application (postgresql). As a workaround I can use strategy :deletion but that slows test suites execution time.

[cyucelen]

I also need this to use txdb for my tests.

[dzirg44]

https://mikro-orm.io/ migrations

[filipeaguiars]

+1'ed SavePoint is crucial for my application.

05-11:53:23.642 | main | INFO | Created new number range with id 1 for seqName NumberRangeAssignment - (AbstractNumberRange.java:480)
Error: org.postgresql.util.PSQLException: ERROR: SAVEPOINT not supported yet
Hint: See #1125. Click '+' on the description to raise its priority
Position: 1
java.lang.IllegalStateException: org.postgresql.util.PSQLException: ERROR: SAVEPOINT not supported yet
Hint: See #1125. Click '+' on the description to raise its priority
Position: 1

[trfi]

I using Postgraphile and when I add data to yugabyte database see error: ROLLBACK <transaction> not supported yet

[ddorian]

@trfi please check the logs of Postgraphile or increase the verbosity of debug log to get more info regarding the error.

[jannh]

SAVEPOINT would also be very helpful for migrations and unittests in Django

[kulminaator]

This blocks people from using amazon's database migration tool properly to replicate from postgresql to yugabytedb

[kmuthukk]

@kulminaator -- which specific statement or statements are you referring to? Please note - we recently added support for savepoints and rollback to savepoints.

CC: @robertsami