tessl/golang-github-com-jackc-pgx-v5

pgx is a pure Go driver and toolkit for PostgreSQL providing a native high-performance interface with PostgreSQL-specific features plus a database/sql compatibility adapter.

—

Pending

Overview

Eval results

Files

Transactions

Name: tessl/golang-github-com-jackc-pgx-v5
Author: tessl

Import

import "github.com/jackc/pgx/v5"

Starting Transactions

func (c *Conn) Begin(ctx context.Context) (Tx, error)
func (c *Conn) BeginTx(ctx context.Context, txOptions TxOptions) (Tx, error)

The context only affects the BEGIN command itself; there is no auto-rollback on context cancellation.

TxOptions

type TxOptions struct {
    IsoLevel       TxIsoLevel
    AccessMode     TxAccessMode
    DeferrableMode TxDeferrableMode

    // BeginQuery overrides all other settings with a raw SQL string
    BeginQuery  string
    // CommitQuery overrides the default COMMIT SQL string
    CommitQuery string
}

Transaction Isolation Levels

type TxIsoLevel string

const (
    Serializable    TxIsoLevel = "serializable"
    RepeatableRead  TxIsoLevel = "repeatable read"
    ReadCommitted   TxIsoLevel = "read committed"
    ReadUncommitted TxIsoLevel = "read uncommitted"
)

Transaction Access Modes

type TxAccessMode string

const (
    ReadWrite TxAccessMode = "read write"
    ReadOnly  TxAccessMode = "read only"
)

Transaction Deferrable Modes

type TxDeferrableMode string

const (
    Deferrable    TxDeferrableMode = "deferrable"
    NotDeferrable TxDeferrableMode = "not deferrable"
)

Tx Interface

type Tx interface {
    Begin(ctx context.Context) (Tx, error)  // start pseudo-nested tx (savepoint)
    Commit(ctx context.Context) error
    Rollback(ctx context.Context) error

    Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
    Query(ctx context.Context, sql string, args ...any) (Rows, error)
    QueryRow(ctx context.Context, sql string, args ...any) Row

    CopyFrom(ctx context.Context, tableName Identifier, columnNames []string, rowSrc CopyFromSource) (int64, error)
    SendBatch(ctx context.Context, b *Batch) BatchResults
    LargeObjects() LargeObjects
    Prepare(ctx context.Context, name, sql string) (*pgconn.StatementDescription, error)

    Conn() *Conn
}

Commit is safe to call multiple times; subsequent calls are no-ops if already committed.
Rollback is safe to call even after a successful Commit (becomes a no-op).
Calling Begin on a Tx creates a savepoint (pseudo-nested transaction).

Error Variables

var ErrTxClosed error        // Tx is already closed
var ErrTxCommitRollback error // COMMIT on an aborted transaction was rolled back

Usage Pattern

tx, err := conn.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx) // safe no-op if Commit succeeds

_, err = tx.Exec(ctx, "INSERT INTO foo(id) VALUES ($1)", 1)
if err != nil {
    return err
}

return tx.Commit(ctx)

BeginTx Example

tx, err := conn.BeginTx(ctx, pgx.TxOptions{
    IsoLevel:   pgx.Serializable,
    AccessMode: pgx.ReadWrite,
})

Helper Functions

func BeginFunc(
    ctx context.Context,
    db interface {
        Begin(ctx context.Context) (Tx, error)
    },
    fn func(Tx) error,
) error

func BeginTxFunc(
    ctx context.Context,
    db interface {
        BeginTx(ctx context.Context, txOptions TxOptions) (Tx, error)
    },
    txOptions TxOptions,
    fn func(Tx) error,
) error

These functions begin a transaction, call fn, then commit if fn returns nil or rollback if fn returns an error.

err = pgx.BeginFunc(ctx, conn, func(tx pgx.Tx) error {
    _, err := tx.Exec(ctx, "INSERT INTO foo(id) VALUES ($1)", 1)
    return err
})

// With options:
err = pgx.BeginTxFunc(ctx, conn, pgx.TxOptions{IsoLevel: pgx.Serializable},
    func(tx pgx.Tx) error {
        _, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1)
        return err
    })

Pseudo-Nested Transactions (Savepoints)

Calling tx.Begin() on an existing Tx creates a savepoint:

tx, err := conn.Begin(ctx)
defer tx.Rollback(ctx)

nestedTx, err := tx.Begin(ctx) // creates SAVEPOINT sp1
_, err = nestedTx.Exec(ctx, "INSERT INTO ...")
if err != nil {
    nestedTx.Rollback(ctx) // rolls back to sp1
} else {
    nestedTx.Commit(ctx) // releases sp1
}

tx.Commit(ctx) // commits the outer transaction

To force a new real transaction (not a savepoint), use BeginTx on the Conn directly.

Works with pgxpool.Pool

All transaction functions also work with *pgxpool.Pool:

// pgxpool implements the same Begin/BeginTx interface
pool, _ := pgxpool.New(ctx, connStr)
err = pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
    _, err := tx.Exec(ctx, "INSERT INTO ...")
    return err
})

Install with Tessl CLI

npx tessl i tessl/golang-github-com-jackc-pgx-v5