[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: https://github.com/yugabyte/yugabyte-db/issues/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 https://github.com/YugaByte/yugabyte-db/issues/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 https://github.com/YugaByte/yugabyte-db/issues/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