Postgres table batch updates using golang pgxpool not reflected in database

Question

I need to update a rather large psql table with newly transformed ID's (passed in as 'records'). I created this function below to utilize pgxpool's connection pool and request batching, if I apply these transactions using a different client they update the db, also if I look at the results printed out they indicate that each time 1 row had been affected, yet when I retrieve those rows from the database they remain unchanged. Am I using the batching incorrectly somehow?

import (
    ...
    "github.com/jackc/pgconn"
    "github.com/jackc/pgx/v4"
    "github.com/jackc/pgx/v4/pgxpool"

    log "github.com/sirupsen/logrus"
)

func SetNewSubscriptionValuesBatch(dsn string, records map[string]string) error {
    
    var db *pgxpool.Pool

    db, err := pgxpool.Connect(context.Background(), dsn)
    defer db.Close()
    if err != nil {
        panic(err)
    }
    
    tx, err := db.Begin(context.Background())
    if err != nil {
        panic(err)
    }

    b := &pgx.Batch{}

    for id, subID := range records {

    sqlStatement := `
UPDATE event
SET subscription_id_2 = $2
WHERE id = $1;`
        b.Queue(sqlStatement, id, subID)
    }
    
    batchResults := tx.SendBatch(context.Background(), b)
    
    var berr error
    var result pgconn.CommandTag
    for berr == nil {
        result, berr = batchResults.Exec()
        log.WithField("result", result.String()).WithField("result.rows-affected", result.RowsAffected()).Info("batch-result")
    }
    return nil
}

Printout:

{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"UPDATE 1","result.rows-affected":1,"time":"2020-10-14T16:47:25+01:00"}
{"level":"info","msg":"batch-result","result":"","result.rows-affected":0,"time":"2020-10-14T16:47:25+01:00"}

I don't know `go`, but generally when this issue occurs there has been a failure to `COMMIT` the transaction. So the rows are visible in the transaction, but not outside/after the transaction. — Adrian Klaver, Oct 14 '20 at 16:21
@AdrianKlaver thanks I suspected it would be something like this but couldn't work out how this is to be done. But you were right - I'll post my changes following your suggestion. The code nows works as expected. — Willeman, Oct 15 '20 at 09:41

score 5 · Answer 1 · answered Oct 15 '20 at 09:49

Following the suggestion of @Adrian Klaver I made these changes to complete the transactions:

The transactions still needs to be committed explitely
The transaction connection(s) will be remain open following SendBatch. It may be required to release these before using the connetion for the commit.

Here is the code following the changes:

import (
    ...
    "github.com/jackc/pgconn"
    "github.com/jackc/pgx/v4"
    "github.com/jackc/pgx/v4/pgxpool"

    log "github.com/sirupsen/logrus"
)

func SetNewSubscriptionValuesBatch(dsn string, records map[string]string) error {

    ctx := context.Background()
    var db *pgxpool.Pool

    db, err := pgxpool.Connect(ctx, dsn)
    defer db.Close()
    if err != nil {
        panic(err)
    }
    
    tx, err := db.Begin(ctx)
    if err != nil {
        panic(err)
    }

    b := &pgx.Batch{}

    for id, subID := range records {

    sqlStatement := `
UPDATE event
SET subscription_id_2 = $2
WHERE id = $1;`
        b.Queue(sqlStatement, id, subID)
    }
    
    batchResults := tx.SendBatch(ctx, b)
    
    var qerr error
    var rows pgx.Rows
    for qerr == nil {
        rows, qerr = batchResults.Query()
        rows.Close()
    }
    
    return tx.Commit(ctx)
}

Postgres table batch updates using golang pgxpool not reflected in database

1 Answers1