`defer` order when variable persists

Question

I've seen a few questions on Go's defer, but nothing like the implementation I'm trying to do yet.

The test case

The application I'm writing does a fair bit of db transactions, so I have a function

func getCursor() *sql.Tx {
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    err = db.Ping()
    if err != nil {
        panic(err)
    }
    tx, err := db.Begin()
    handleErr(err, tx)
    return tx
}

Which gives me back a transaction, and defer's db.Close() so I don't flood the database's pool.

Other functions that use this are:

// addPerson lets you add a person using a transaction that's passed as the first argument.
func addPerson(tx *sql.Tx, firstName string, lastName string, phoneNumber string) sql.Result {
    statement := "INSERT INTO public.persons (first_name, last_name, phone_number, data) VALUES ($1, $2, $3, '{}')"
    res, err := tx.Exec(statement, firstName, lastName, phoneNumber)
    handleErr(err, tx)
    return res
}

// wraps addPerson in a transaction to be used as standalong
func AddPerson(firstName string, lastName string, phoneNumber string) int64 {
    tx := getCursor()
    defer tx.Rollback()
    err := tx.Commit()
    res := addPerson(tx, firstName, lastName, phoneNumber)
    handleErr(err, tx)
    affected, err := res.RowsAffected()
    handleErr(err, tx)
    return affected
}

from https://blog.golang.org/defer-panic-and-recover

Deferred function calls are executed in Last In First Out order after the surrounding function returns.

So if the tx.Commit() fails, the transaction will be rolled back.

My confusion is around the defer db.Close(). If this is getting executed after getCursor is done, how am I still able to make an INSERT into the database? (The code in the question works, I'm just really confused by why it does). Any help clarifying is super appreciated.

Apparently, it waits til the transaction completes: https://golang.org/pkg/database/sql/#DB.Close ? — zerkms, Feb 02 '20 at 22:33
In general, you should not close a db connection using a defer in a function that returns objects depending on that connection, like cursors. — Burak Serdar, Feb 02 '20 at 22:42
The documentation suggests that it's better to just leave the thing open as long as possible—for instance, open it at the start of the program, and close it just before ending the program entirely. — torek, Feb 02 '20 at 23:45

yogy fresta rahmawan · Answer 1 · 2020-02-03T06:49:07.243

First, the db is initialised once per main app. So, if you want to call db.Close, put it on main function. and you need to refactor your code. Here is some idea of how you must implement it. Hopefully it is useful.

func main() {
    db, err := createDBConn()
    if err != nil {
        panic(err)
    }
    defer db.Close()

    tx, err := beginTx(db)
    if err != nil {
        panic(err)
    }

    AddPerson("test", "test", "test")

}

func addPerson(tx *sql.Tx, firstName string, lastName string, phoneNumber string) sql.Result {
    statement := "INSERT INTO public.persons (first_name, last_name, phone_number, data) VALUES ($1, $2, $3, '{}')"
    res, err := tx.Exec(statement, firstName, lastName, phoneNumber)
    handleErr(err, tx)
    return res
}

// wraps addPerson in a transaction to be used as standalong
func AddPerson(tx *sql.Tx, firstName string, lastName string, phoneNumber string) int64 {
    defer tx.Rollback()
    err := tx.Commit()
    res := addPerson(tx, firstName, lastName, phoneNumber)
    handleErr(err, tx)
    affected, err := res.RowsAffected()
    handleErr(err, tx)
    return affected
}

func beginTx(db *sql.DB) (*sql.Tx, error) {
    tx, err := db.Begin()
    if err != nil {
        return nil, fmt.Errorf("create tx: %w", err)
    }
    return tx, nil
}

func createDBConn() (*sql.DB, error) {
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        return nil, fmt.Errorf("failed connect to db: %w", err)
    }

    err = db.Ping()
    if err != nil {
        return nil, fmt.Errorf("failed ping db: %w", err)
    }

    return db, nil
}

For why you still can do query. from godoc https://golang.org/pkg/database/sql/#Conn.Close . Close returns the connection to the connection pool. All operations after a Close will return with ErrConnDone. Close is safe to call concurrently with other operations and will block until all other operations finish. It may be useful to first cancel any used context and then call close directly after. it will block until all other operations finish .

And https://golang.org/src/database/sql/sql.go line 1930

Good advice, but doesn't answer the question. – Jonathan Hall Feb 03 '20 at 04:18 — Jonathan Hall, Feb 03 '20 at 04:18

`defer` order when variable persists

The test case

1 Answers1