How to do prepare transaction (two-phase commit)?

[matthewvalimaki]

I am already using transactions for inserts against one database. But I would like to do cross-database transactions with PREPARE TRANSACTION, see https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html. I looked at documentation for transactions and while it does have Prepare() but I am not confident this does PREPARE TRANSACTION. But if it does how would I do insert into two different databases?

[vmihailenco]

I believe you should use Tx and Exec:

tx, err := db.Begin()
err := tx.Model(&model).Insert()
_, err := tx.Exec("PREPARE TRANSACTION 'foobar'")
tx.Rollback() // or tx.Commit() to free connection associated with transaction

_, err := db.Exec("COMMIT PREPARED 'foobar'")

I have no idea if it really works and probably go-pg should have PrepareTx("foobar") helper.

PS Docs say

PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources. Unless you're writing a transaction manager, you probably shouldn't be using PREPARE TRANSACTION.

[matthewvalimaki]

@vmihailenco thank you for this. I will test this. If helpers are added probably should add one for commit and rollback. Also perhaps a helper for pg_prepared_xacts as well where return value would be slice of a predefined struct?

And yes I am going to have a transaction manager.

[whyamiroot]

I would like to perform transaction, but I'm not writing transaction manager. What should I use? I'm thinking of doing it like this

tx, err := db.Begin()
//error handling
tx.Insert(something)
tx.Exec("Some query")
trErr := tx.Commit()
if trErr != nil {
    tx.Rollback()
}

[vmihailenco]

See https://godoc.org/github.com/go-pg/pg#Tx and https://godoc.org/github.com/go-pg/pg#example-DB-RunInTransaction

[siteshen]

Hello, https://github.com/go-pg/pg/issues/490#issuecomment-287326441 almost works except WARNING: there is no transaction in progress in statement tx.Commit(). According to https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html We should free connection after exec PREPARE TRANSACTION, as tx.Rollback() or tx.Commit() does.

Description PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested.

It works if go-pg expose internal method tx.close:

func (tx *Tx) Close(lastErr error) error {
    tx.mu.Lock()
    defer tx.mu.Unlock()

    return tx.close(lastErr)
}

or better, implement PrepareTransaction:

func (tx *Tx) PrepareTransaction(id string) error {
    tx.mu.Lock()
    defer tx.mu.Unlock()

    _, err := tx.exec("PREPARE TRANSACTION ?", id)
    tx.close(err)
    return err
}

[vmihailenco]

tx.Prepare(id string) looks good to me. Do you want to send a PR with some docs on tx.Prepare API?

[latipovsharif]

Hello guys, is there any progress on this?

[moredure]

+1