yugabyte/yugabyte-db

[YSQL] Feature Support - Transaction Control Commands

nocaway opened this issue ยท 17 comments

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 commented

DEFERRABLE is covered in issue #2161.

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

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;

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

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.

SAVEPOINT is also used by Harbor

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

+1'ed SavePoint is crucial for my application.

+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.

I also need this to use txdb for my tests.

+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 commented

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

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

jannh commented

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

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

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

CC: @robertsami