Should I rollback a transaction with only COPY inside?

Question

I use postgres COPY in my Go backend. Copy is the only operation inside transaction. Should I roll it back if it failed?

func (pc *Postgres) Copy(records [][]interface{}) error {
    tx, err := pc.db.Begin()
    if err != nil {
        return errors.Wrap(err, "can't open transaction")
    }
    stmt, err := tx.Prepare(pq.CopyIn(pc.table, pc.columns...))
    if err != nil {
        return errors.Wrap(err, "can't prepare stmt")
    }
    for _, record := range records {
        if _, err := stmt.Exec(record...); err != nil {
            return errors.Wrap(err, "error exec record")
        }

    }

    if _, err = stmt.Exec(); err != nil {
        return errors.Wrap(err, "error exec stmt")
    }
    if err = stmt.Close(); err != nil {
        return errors.Wrap(err, "error close stmt")
    }
    if err = tx.Commit(); err != nil {
        return errors.Wrap(err, "error commit transaction")
    }
    return nil
}

As far as I understand if \copy fails transaction will be aborted(link) and rolled back.

However in officials lib/pq examples I see they always use rollback(but they have more than one operation).

Could somebody please guide me through these nuances?

in your code, the transacton is left idling if you encounter an error. Having a defered rollback ensure it is released when the function is left. The library is smart enough to not rollback if you had commited before. https://github.com/lib/pq/blob/master/conn.go#L579 — , Mar 23 '21 at 11:47
thanks, it answers my question. If you post it as a separate answer it will mark as a correct. — Sergii Getman, Mar 23 '21 at 11:53
thanks, but i figured out that this is a duplicate. https://stackoverflow.com/questions/46421602/why-defer-a-rollback — , Mar 23 '21 at 12:19
not exactly, my initial though was do I need rollback at all — Sergii Getman, Mar 23 '21 at 12:53

score 0 · Answer 1 · answered Mar 23 '21 at 21:01

It looks like part of the confusion is because \COPY is not the same thing as COPY.

\COPY (as referenced in this question) is a command in psql that "Performs a frontend (client) copy". In simple terms you run psql (a terminal-based front-end to PostgreSQL) on a computer and can \COPY data between the database and a file stored locally (or accessible from your machine). This command is part of psql and not something you can use via lib/pq.

COPY is a Postgres SQL command that runs on the server; the file you are copying "must be accessible by PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server". This is what you are calling in your application (from the pq docs "CopyIn uses COPY FROM internally" implementation here and here)

So, as the answer referenced by @mh-cbon states:

the deferred rollback is there to ensure that the transaction is rolled back in case of an early return.

Consider:

tx, err := pc.db.Begin()
if err != nil {
    return errors.Wrap(err, "can't open transaction")
}
stmt, err := tx.Prepare(pq.CopyIn(pc.table, pc.columns...))
if err != nil {
    return errors.Wrap(err, "can't prepare stmt")
}

If the Prepare fails you have created a transaction and then return without closing it; this leaves the transaction open which is not a good thing. Adding a defer tx.Rollback() ensures that does not happen.

thanks, agree @mh-cbon answer is what i was looking for. so i shouldn't leave transaction open i any case — Sergii Getman, Mar 24 '21 at 10:45
That is correct; when you begin a transaction a connection will be dedicated to that transaction so it's important that you either commit or rollback so the connection can be freed. — Brits, Mar 26 '21 at 02:17

Should I rollback a transaction with only COPY inside?

1 Answers1