pgutil

README

pgutil

Transactions

Simplify postgres transactions using WithTx for serializable transactions, or WithTxDefault for the default isolation level. Use the SerialTxRunner type to get automatic retries of serialization errors.

Migrations

Put your migrations into a directory, for example migrations, ordered by name (YYYY-MM-DD prefix, for example). Embed the directory and pass it to the Migrate function:

//go:embed migrations
var migrations embed.FS

func init() {
    Migrate(db, migrations) // Check the error, of course.
}

Testing

In order to test this packge, we need to create a test user and database:

sudo su postgres
psql

CREATE DATABASE test;
CREATE USER test WITH ENCRYPTED PASSWORD 'test';
GRANT ALL PRIVILEGES ON DATABASE test TO test;

use test

GRANT ALL ON SCHEMA public TO test;

Check that you can connect via the command line:

psql -h 127.0.0.1 -U test --password test

Documentation

Index

• func DropAllTables(db *sql.DB) error
• func ErrHasCode(err error, code string) bool
• func ErrIsDuplicateKey(err error) bool
• func ErrIsForeignKey(err error) bool
• func ErrIsSerializationFailure(err error) bool
• func Migrate(db *sql.DB, migrationFS embed.FS) error
• func WithTx(db *sql.DB, fn func(*sql.Tx) error) error
• func WithTxDefault(db *sql.DB, fn func(*sql.Tx) error) error
• type SerialTxRunner
  • func (r SerialTxRunner) WithTx(db *sql.DB, fn func(*sql.Tx) error) error

Functions

func DropAllTables

func DropAllTables(db *sql.DB) error

Deletes all tables in the database. Useful for testing.

func ErrHasCode

func ErrHasCode(err error, code string) bool

func ErrIsDuplicateKey

func ErrIsDuplicateKey(err error) bool

func ErrIsForeignKey

func ErrIsForeignKey(err error) bool

func ErrIsSerializationFailure

func ErrIsSerializationFailure(err error) bool

func Migrate

func Migrate(db *sql.DB, migrationFS embed.FS) error

func WithTx

func WithTx(db *sql.DB, fn func(*sql.Tx) error) error

Postgres doesn't use serializable transactions by default. This wrapper will run the enclosed function within a serializable. Note: this may return an retriable serialization error (see ErrIsSerializationFaiilure).

func WithTxDefault

func WithTxDefault(db *sql.DB, fn func(*sql.Tx) error) error

This is a convenience function to provide a transaction wrapper with the default isolation level.

Types

type SerialTxRunner

type SerialTxRunner struct {
	MinTimeout time.Duration
	MaxTimeout time.Duration
}

SerialTxRunner attempts serializable transactions in a loop. If a transaction fails due to a serialization error, then the runner will retry with exponential backoff, until the sleep time reaches MaxTimeout.

For example, if MinTimeout is 100 ms, and MaxTimeout is 800 ms, it may sleep for ~100, 200, 400, and 800 ms between retries.

10% jitter is added to the sleep time.

func (SerialTxRunner) WithTx

func (r SerialTxRunner) WithTx(db *sql.DB, fn func(*sql.Tx) error) error