pg-go RunInTransaction not rolling back the transaction

Question

I'm trying to rollback a transaction on my unit tests, between scenarios, to keep the database empty and do not make my tests dirty. So, I'm trying:

for _, test := range tests {
   db := connect()
   _ = db.RunInTransaction(func() error {
      t.Run(test.name, func(t *testing.T) {
         for _, r := range test.objToAdd {
            err := db.PutObj(&r)
            require.NoError(t, err)
         }
         objReturned, err := db.GetObjsWithFieldEqualsXPTO()
         require.NoError(t, err)
         require.Equal(t, test.queryResultSize, len(objReturned))
      })
      return fmt.Errorf("returning error to clean up the database rolling back the transaction")
   })
}

I was expecting to rollback the transaction on the end of the scenario, so the next for step will have an empty database, but when I run, the data is never been rolling back.

I believe I'm trying to do what the doc suggested: https://pg.uptrace.dev/faq/#how-to-test-mock-database, am I right?

More info: I notice that my interface is implementing a layer over RunInTransaction as:

func (gs *DB) RunInTransaction(fn func() error) error {
    f := func(*pg.Tx) error { return fn() }
    return gs.pgDB.RunInTransaction(f)
}

IDK what is the problem yet, but I really guess that is something related to that (because the TX is encapsulated just inside the RunInTransaction implementation.

Answer 1

go-pg uses connection pooling (in common with most go database packages). This means that when you call a database function (e.g. db.Exec) it will grab a connection from the pool (establishing a new one if needed), run the command and return the connection to the pool.

When running a transaction you need to run BEGIN, whatever updates etc you require, followed by COMMIT/ROLLBACK, on a single connection dedicated to the transaction (any commands sent on other connections are not part of the transaction). This is why Begin() (and effectively RunInTransaction) provide you with a pg.Tx; use this to run commands within the transaction.

example_test.go provides an example covering the usage of RunInTransaction:

incrInTx := func(db *pg.DB) error {
        // Transaction is automatically rollbacked on error.
        return db.RunInTransaction(func(tx *pg.Tx) error {
            var counter int
            _, err := tx.QueryOne(
                pg.Scan(&counter), `SELECT counter FROM tx_test FOR UPDATE`)
            if err != nil {
                return err
            }

            counter++

            _, err = tx.Exec(`UPDATE tx_test SET counter = ?`, counter)
            return err
        })
    }

You will note that this only uses the pg.DB when calling RunInTransaction; all database operations use the transaction tx (a pg.Tx). tx.QueryOne will be run within the transaction; if you ran db.QueryOne then that would be run outside of the transaction.

So RunInTransaction begins a transaction and passes the relevant Tx in as a parameter to the function you provide. You wrap this with:

func (gs *DB) RunInTransaction(fn func() error) error {
    f := func(*pg.Tx) error { return fn() }
    return gs.pgDB.RunInTransaction(f)
}

This effectively ignores the pg.Tx and you then run commands using other connections (e.g. err := db.PutObj(&r)) (i.e. outside of the transaction). To fix this you need to use the transaction (e.g. err := tx.PutObj(&r)).