golang数据库事务：如果单个exec语句失败，则继续

I am writing a Go app which should insert thousands of values from a file into a database. This works fine, as long as all values can be inserted into the database. If one of the queries fails, all queries afterwards fail because of pq: : current transaction is aborted, commands ignored until end of transaction block

I want to insert all elements and if the insert of an element fails, it should be skipped and the other elements should be inserted.

My Code:

func (db *Database) Insert(values []Value) (transerr error) {
    tx, err := db.Begin()
    if transerr != nil {
        return nil, err
    }
    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }
    stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
    if err != nil {
        return err
    }

    defer stmt.Close()

    for _, value : range values {
        _, err = stmt.Exec(value)
        if err != nil {
            log.Error(err)
        }
    }
    return nil
}

I tried to add a tx.Rollback() in case a stmt.Exec fails - however this results in sql: statement is closed.

My solution for the problem looks like this:

• Do not create a single transaction and add all statements into it, instead just run it without creating transactions.
• As the values are read in, spawn new go routines and let the transaction run parallelized (be careful with connection limits).
• Without parallelization, the performance dropped about 30% (from 20s for 25k values to 30s - we did not use parallelization before).
• With parallelization, the performance increased about 4 times (to 5 seconds) - just be careful you stay within the connection ranges

For Postgresql, you can use ON CONFLICT DO NOTHING

I have tried the code below with postgresql db on my side and it ignores the insert line that has error. I made few other changes to try on my side. You can ignore my other changes.

func insert(db *sql.DB, values []string) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Commit()
    stmt, err := tx.Prepare("INSERT INTO foo (  foo_col) VALUES ($1) ON CONFLICT DO NOTHING")

    if err != nil {
        fmt.Println("errro at stmt", err)
        return err
    }

    defer stmt.Close()

    for _, value := range values {
        _, err = stmt.Exec(value)
        if err != nil {
            fmt.Println(value, err)
        }
    }
    return nil
}

For mysql, you can use INSERT IGNORE

stmt, err := tx.Prepare("INSERT IGNORE INTO foo (  foo_col) VALUES ($1) ")