Yugabyte-db: [YSQL] Feature Support - Transaction Control Commands

[nocaway]

The following transaction control commands are not yet supported.

• SAVEPOINT
• RELEASE SAVEPOINT
• RELEASE
• ROLLBACK
• PREPARE
• COMMIT PREPARED
• ROLLBACK PREPARED
• DEFERRABLE transaction

[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