[YSQL] Feature Support - Transaction Control Commands
nocaway opened this issue · 17 comments
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.
https://mikro-orm.io/ migrations
+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
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.
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