Compare · 023

Transactions: PostgreSQL and CockroachDB

Every file of the PostgreSQL and CockroachDB versions side by side, with the lines that differ marked. 4 parameter or column types differ in Go.

Compare PostgreSQL withSQLite CockroachDB TimescaleDB MySQL MariaDB DuckDB

Configuration

sgl.yamldiffers

PostgreSQL
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
- name: bank
- name: bank
  dialect: postgres
  dialect: cockroachdb
  migrations: db/migrations
  migrations: db/migrations
  queries: db/queries
  queries: db/queries
  go:
  go:
    package: store
    package: store
    out: internal/store
    out: internal/store
    driver: database/sql
    interface:
      enabled: true
  migrate:
    dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_accounts.up.sqldiffers

PostgreSQL
CockroachDB
1CREATE TABLE accounts (
1CREATE TABLE accounts (
2    id      BIGINT  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2    id      INT8    NOT NULL DEFAULT unique_rowid() PRIMARY KEY,
3    owner   TEXT    NOT NULL,
3    owner   STRING  NOT NULL,
4    balance INTEGER NOT NULL CHECK (balance >= 0)
4    balance INTEGER NOT NULL CHECK (balance >= 0)
5);
5);

db/migrations/0001_accounts.down.sqlidentical

PostgreSQL
CockroachDB
1DROP TABLE accounts;
1DROP TABLE accounts;

Queries

OpenAccountidentical

PostgreSQL · db/queries/accounts.sql
CockroachDB · db/queries/accounts.sql
1-- sgl.query(OpenAccount, one)
1-- sgl.query(OpenAccount, one)
2INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;
2INSERT INTO accounts (owner, balance) VALUES ($1, $2) RETURNING id;

Depositdiffers

PostgreSQL · db/queries/accounts.sql
CockroachDB · db/queries/accounts.sql
1-- sgl.query(Deposit, exec)
1-- sgl.query(Deposit, exec)
2UPDATE accounts SET balance = balance + :amount WHERE id = :id;
2-- $1 as sgl.param(amount)
3UPDATE accounts SET balance = balance + $1 WHERE id = $2;

Withdrawdiffers

PostgreSQL · db/queries/accounts.sql
CockroachDB · db/queries/accounts.sql
1-- sgl.query(Withdraw, exec)
1-- sgl.query(Withdraw, exec)
2UPDATE accounts SET balance = balance - :amount WHERE id = :id;
2-- $1 as sgl.param(amount)
3UPDATE accounts SET balance = balance - $1 WHERE id = $2;

ListAccountsidentical

PostgreSQL · db/queries/accounts.sql
CockroachDB · db/queries/accounts.sql
1-- sgl.query(ListAccounts, many)
1-- sgl.query(ListAccounts, many)
2SELECT id, owner, balance FROM accounts ORDER BY id;
2SELECT id, owner, balance FROM accounts ORDER BY id;

GetBalanceonly CockroachDB

PostgreSQL · not in this version
CockroachDB · db/queries/accounts.sql
1-- sgl.query(GetBalance, one)
2SELECT balance FROM accounts WHERE id = $1;

SetBalanceonly CockroachDB

PostgreSQL · not in this version
CockroachDB · db/queries/accounts.sql
1-- sgl.query(SetBalance, exec)
2UPDATE accounts SET balance = $1 WHERE id = $2;

What sgl generates

OpenAccountdiffers

PostgreSQL
CockroachDB
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
1func (queries *Queries) OpenAccount(ctx context.Context, params OpenAccountParams) (OpenAccountRow, error)
2
2
3type OpenAccountParams struct {
3type OpenAccountParams struct {
4	Owner   string
4	Owner   string
5	Balance int32
5	Balance int64
6}
6}
7
7
8type OpenAccountRow struct {
8type OpenAccountRow struct {
9	ID int64 `json:"id"`
9	ID int64 `json:"id"`
10}
10}

Depositdiffers

PostgreSQL
CockroachDB
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
1func (queries *Queries) Deposit(ctx context.Context, params DepositParams) error
2
2
3type DepositParams struct {
3type DepositParams struct {
4	Amount int32
4	Amount int64
5	ID     int64
5	ID     int64
6}
6}

Withdrawdiffers

PostgreSQL
CockroachDB
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
1func (queries *Queries) Withdraw(ctx context.Context, params WithdrawParams) error
2
2
3type WithdrawParams struct {
3type WithdrawParams struct {
4	Amount int32
4	Amount int64
5	ID     int64
5	ID     int64
6}
6}

ListAccountsdiffers

PostgreSQL
CockroachDB
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
1func (queries *Queries) ListAccounts(ctx context.Context) ([]ListAccountsRow, error)
2
2
3type ListAccountsRow struct {
3type ListAccountsRow struct {
4	ID      int64  `json:"id"`
4	ID      int64  `json:"id"`
5	Owner   string `json:"owner"`
5	Owner   string `json:"owner"`
6	Balance int32  `json:"balance"`
6	Balance int64  `json:"balance"`
7}
7}

GetBalanceonly CockroachDB

PostgreSQL · not in this version
CockroachDB
1func (queries *Queries) GetBalance(ctx context.Context, id int64) (GetBalanceRow, error)
2
3type GetBalanceRow struct {
4	Balance int64 `json:"balance"`
5}

SetBalanceonly CockroachDB

PostgreSQL · not in this version
CockroachDB
1func (queries *Queries) SetBalance(ctx context.Context, params SetBalanceParams) error
2
3type SetBalanceParams struct {
4	Balance int64
5	ID      int64
6}

The program

example.godiffers

PostgreSQL
CockroachDB
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"errors"
6	"errors"
7	"fmt"
7	"fmt"
8	"io"
8	"io"
9
9
10	"seagull.sh/seagull/examples/postgres/023_transactions/internal/store"
10	"seagull.sh/seagull/examples/cockroachdb/023_transactions/internal/store"
11)
11)
12
12
13// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
13const (
14// does.
14	// serialisationFailure is the SQLSTATE CockroachDB reports when it aborts a transaction
15	// that conflicted with another, so that the client can run it again.
16	serialisationFailure = "40001"
17
18	// maxAttempts bounds how many times retrySerialisable runs a transaction.
19	maxAttempts = 3
20)
21
22// sqlStateError is the method a driver's error offers to report its SQLSTATE. pgx's
23// *pgconn.PgError has it, so code can read the state without importing pgx.
15type sqlStateError interface {
24type sqlStateError interface {
16	error
25	error
17
26
18	// SQLState returns the five-character SQLSTATE code.
27	// SQLState returns the five-character SQLSTATE code.
19	SQLState() string
28	SQLState() string
20}
29}
21
30
22// run opens two accounts in a transaction it manages itself, then makes two transfers
31// run opens two accounts in a transaction it manages itself, then makes two transfers
23// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
32// with RunInTx: one commits, and one breaks the balance check part-way and rolls back.
24//
33// Last, it pays interest with a read-then-write transaction that another client's deposit
25// sgl.yaml sets go.interface, so the generated package also declares store.Querier, an
34// interrupts, which CockroachDB aborts and retrySerialisable runs again.
26// interface of every query method, which *store.Queries implements whether it runs on the
27// database or inside a transaction. printBalances takes a store.Querier, so the same
28// function reads the committed balances and, inside each transfer, the balances the
29// transaction sees before it commits or rolls back; a test could pass it a fake instead.
30//
35//
31// Takes database (*sql.DB) which is a migrated database.
36// Takes database (*sql.DB) which is a migrated database.
32// Takes output (io.Writer) which receives each step and the balances.
37// Takes output (io.Writer) which receives each step and the balances.
33//
38//
34// Returns error when a query fails other than as the example expects.
39// Returns error when a query fails other than as the example expects.
35func run(ctx context.Context, database *sql.DB, output io.Writer) error {
40func run(ctx context.Context, database *sql.DB, output io.Writer) error {
36	queries := store.New(database)
41	queries := store.New(database)
37
42
38	pip, gull, err := openAccounts(ctx, database, queries)
43	pip, gull, err := openAccounts(ctx, database, queries)
39	if err != nil {
44	if err != nil {
40		return fmt.Errorf("opening accounts: %w", err)
45		return fmt.Errorf("opening accounts: %w", err)
41	}
46	}
42	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
47	fmt.Fprintln(output, "opened accounts for Pip and Gull: committed")
43	if err := printBalances(ctx, queries, output, "balances"); err != nil {
48	if err := printBalances(ctx, queries, output); err != nil {
44		return err
49		return err
45	}
50	}
46
51
47	if err := transfer(ctx, database, queries, output, pip, gull, 1500); err != nil {
52	if err := transfer(ctx, database, queries, pip, gull, 1500); err != nil {
48		return fmt.Errorf("first transfer: %w", err)
53		return fmt.Errorf("first transfer: %w", err)
49	}
54	}
50	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
55	fmt.Fprintln(output, "transferred 1500 from Pip to Gull: committed")
51	if err := printBalances(ctx, queries, output, "balances"); err != nil {
56	if err := printBalances(ctx, queries, output); err != nil {
52		return err
57		return err
53	}
58	}
54
59
55	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
60	// Gull holds 3500, so this transfer credits Pip and then fails the CHECK constraint when
56	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
61	// it debits Gull. RunInTx rolls back, and the credit to Pip that had already run is
57	// undone with it.
62	// undone with it.
58	err = transfer(ctx, database, queries, output, gull, pip, 9000)
63	err = transfer(ctx, database, queries, gull, pip, 9000)
59	if err == nil {
64	if err == nil {
60		return errors.New("an overdrawing transfer committed")
65		return errors.New("an overdrawing transfer committed")
61	}
66	}
67	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (%v)\n", err)
68	if err := printBalances(ctx, queries, output); err != nil {
69		return err
70	}
62
71
63	// pgx reports a server error as a *pgconn.PgError, whose SQLState method gives the
72	aborted, err := payInterest(ctx, database, queries, gull)
64	// standard five-character code: 23514 for a failed CHECK constraint. Match on the code,
73	if err != nil {
65	// not the message, as the wording differs between servers and versions.
74		return fmt.Errorf("paying interest: %w", err)
66	failure, hasState := errors.AsType[sqlStateError](err)
67	if !hasState {
68		return fmt.Errorf("an overdrawing transfer failed without a SQLSTATE: %w", err)
69	}
75	}
70	fmt.Fprintf(output, "transferred 9000 from Gull to Pip: rolled back (SQLSTATE %s)\n", failure.SQLState())
76	fmt.Fprintln(output, "paid Gull 10% interest while a deposit of 100 landed:")
71	return printBalances(ctx, queries, output, "balances")
77	// A 40001 message names timestamps and transaction IDs that change on every run, so only
78	// its SQLSTATE is printed.
79	for index, abort := range aborted {
80		fmt.Fprintf(output, "  attempt %d: aborted with SQLSTATE %s, retried\n", index+1, sqlState(abort))
81	}
82	fmt.Fprintf(output, "  attempt %d: committed\n", len(aborted)+1)
83	return printBalances(ctx, queries, output)
72}
84}
73
85
74// openAccounts opens both accounts in one transaction that it begins and commits itself.
86// openAccounts opens both accounts in one transaction that it begins and commits itself.
75// WithTx returns a copy of queries bound to that transaction, which suits a transaction
87// WithTx returns a copy of queries bound to that transaction, which suits a transaction
76// begun with options or shared with code that does not use Seagull.
88// begun with options or shared with code that does not use Seagull.
77//
89//
78// Takes database (*sql.DB) which begins the transaction.
90// Takes database (*sql.DB) which begins the transaction.
79// Takes queries (*store.Queries) which are bound to it.
91// Takes queries (*store.Queries) which are bound to it.
80//
92//
81// Returns pipID (int64) which is Pip's account ID.
93// Returns pipID (int64) which is Pip's account ID.
82// Returns gullID (int64) which is Gull's account ID.
94// Returns gullID (int64) which is Gull's account ID.
83// Returns err (error) when an account cannot be opened or the commit fails.
95// Returns err (error) when an account cannot be opened or the commit fails.
84func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
96func openAccounts(ctx context.Context, database *sql.DB, queries *store.Queries) (pipID, gullID int64, err error) {
85	transaction, err := database.BeginTx(ctx, nil)
97	transaction, err := database.BeginTx(ctx, nil)
86	if err != nil {
98	if err != nil {
87		return 0, 0, err
99		return 0, 0, err
88	}
100	}
89	// Rollback after a successful Commit does nothing, so deferring it covers every early
101	// Rollback after a successful Commit does nothing, so deferring it covers every early
90	// return.
102	// return.
91	defer func() { _ = transaction.Rollback() }()
103	defer func() { _ = transaction.Rollback() }()
92
104
93	inTransaction := queries.WithTx(transaction)
105	inTransaction := queries.WithTx(transaction)
94	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
106	pip, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Pip", Balance: 5000})
95	if err != nil {
107	if err != nil {
96		return 0, 0, err
108		return 0, 0, err
97	}
109	}
98	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
110	gull, err := inTransaction.OpenAccount(ctx, store.OpenAccountParams{Owner: "Gull", Balance: 2000})
99	if err != nil {
111	if err != nil {
100		return 0, 0, err
112		return 0, 0, err
101	}
113	}
102	return pip.ID, gull.ID, transaction.Commit()
114	return pip.ID, gull.ID, transaction.Commit()
103}
115}
104
116
105// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
117// transfer moves an amount between two accounts with RunInTx, which begins a transaction,
106// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
118// passes fn a Queries bound to it, and commits when fn returns nil or rolls back when it
107// returns an error. After the credit it prints the balances the transaction sees, through
119// returns an error.
108// the same printBalances the caller uses.
109//
120//
110// Takes database (*sql.DB) which begins the transaction.
121// Takes database (*sql.DB) which begins the transaction.
111// Takes queries (*store.Queries) which run the transfer.
122// Takes queries (*store.Queries) which run the transfer.
112// Takes output (io.Writer) which receives the balances inside the transaction.
113// Takes from (int64) which is the account to debit.
123// Takes from (int64) which is the account to debit.
114// Takes to (int64) which is the account to credit.
124// Takes to (int64) which is the account to credit.
115// Takes amount (int32) which is the sum to move, in pence.
125// Takes amount (int64) which is the sum to move, in pence.
116//
126//
117// Returns error when either update fails; the transaction is then rolled back.
127// Returns error when either update fails; the transaction is then rolled back.
118func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, output io.Writer, from, to int64, amount int32) error {
128func transfer(ctx context.Context, database *sql.DB, queries *store.Queries, from, to, amount int64) error {
119	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
129	return queries.RunInTx(ctx, database, func(inTransaction *store.Queries) error {
120		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
130		if err := inTransaction.Deposit(ctx, store.DepositParams{Amount: amount, ID: to}); err != nil {
121			return err
131			return err
122		}
132		}
123		if err := printBalances(ctx, inTransaction, output, "inside the transaction"); err != nil {
133		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
134	})
135}
136
137// payInterest adds a tenth to an account's balance by reading the balance into Go and
138// writing back the result, under retrySerialisable.
139//
140// To show a conflict on demand, the first attempt deposits 100 into the same account on
141// the pool between its read and its write, standing in for another client. That deposit
142// commits first, so the balance this transaction read is stale. CockroachDB uses
143// SERIALIZABLE isolation by default, so instead of letting the write overwrite the
144// deposit it fails the write with SQLSTATE 40001, "restart transaction", and RunInTx
145// rolls back. The second attempt reads the balance with the deposit in it, and commits.
146//
147// Takes database (*sql.DB) which begins each attempt's transaction.
148// Takes queries (*store.Queries) which run the reads and writes.
149// Takes account (int64) which is the account to pay.
150//
151// Returns []error which holds the error of each attempt CockroachDB aborted.
152// Returns error when an attempt fails other than with 40001, or every attempt does.
153func payInterest(ctx context.Context, database *sql.DB, queries *store.Queries, account int64) ([]error, error) {
154	interrupted := false
155	return retrySerialisable(ctx, database, queries, func(inTransaction *store.Queries) error {
156		current, err := inTransaction.GetBalance(ctx, account)
157		if err != nil {
124			return err
158			return err
125		}
159		}
126		return inTransaction.Withdraw(ctx, store.WithdrawParams{Amount: amount, ID: from})
160		if !interrupted {
161			interrupted = true
162			if err := queries.Deposit(ctx, store.DepositParams{Amount: 100, ID: account}); err != nil {
163				return fmt.Errorf("depositing from another client: %w", err)
164			}
165		}
166		return inTransaction.SetBalance(ctx, store.SetBalanceParams{Balance: current.Balance + current.Balance/10, ID: account})
127	})
167	})
128}
168}
129
169
130// printBalances writes every account's balance on one line. It takes the generated
170// retrySerialisable runs work with RunInTx, and runs it again in a new transaction each
131// store.Querier rather than *store.Queries, so it reads through whatever it is given: the
171// time CockroachDB aborts the transaction with SQLSTATE 40001, up to maxAttempts.
132// queries on the database, the queries bound to a transaction, or a fake in a test.
133//
172//
134// Takes queries (store.Querier) which read the accounts.
173// The generated RunInTx commits or rolls back once and returns the error, so on
174// CockroachDB a caller retries itself. work must be safe to run more than once: each
175// attempt starts again from its first read, and anything work does outside the
176// transaction happens once per attempt. Under heavy contention, wait a short, jittered
177// while between attempts as well.
178//
179// Takes database (*sql.DB) which begins each transaction.
180// Takes queries (*store.Queries) which RunInTx binds to it.
181// Takes work (func(*store.Queries) error) which is the transaction's work.
182//
183// Returns []error which holds the error of each attempt CockroachDB aborted, so the
184// caller can log them.
185// Returns error which is nil once an attempt commits, work's own error, or the 40001 of
186// the last attempt.
187func retrySerialisable(ctx context.Context, database *sql.DB, queries *store.Queries, work func(*store.Queries) error) ([]error, error) {
188	var aborted []error
189	for range maxAttempts {
190		err := queries.RunInTx(ctx, database, work)
191		if sqlState(err) != serialisationFailure {
192			return aborted, err
193		}
194		aborted = append(aborted, err)
195	}
196	return aborted, fmt.Errorf("still conflicting after %d attempts: %w", maxAttempts, aborted[len(aborted)-1])
197}
198
199// sqlState returns the SQLSTATE that err, or an error it wraps, carries.
200//
201// Takes err (error) which is the error to inspect, or nil.
202//
203// Returns string which is the five-character code, or empty when err has none.
204func sqlState(err error) string {
205	stateError, hasState := errors.AsType[sqlStateError](err)
206	if !hasState {
207		return ""
208	}
209	return stateError.SQLState()
210}
211
212// printBalances writes every account's balance on one line.
213//
214// Takes queries (*store.Queries) which read the accounts.
135// Takes output (io.Writer) which receives the line.
215// Takes output (io.Writer) which receives the line.
136// Takes label (string) which says whose view of the balances the line shows.
137//
216//
138// Returns error when the accounts cannot be listed.
217// Returns error when the accounts cannot be listed.
139func printBalances(ctx context.Context, queries store.Querier, output io.Writer, label string) error {
218func printBalances(ctx context.Context, queries *store.Queries, output io.Writer) error {
140	accounts, err := queries.ListAccounts(ctx)
219	accounts, err := queries.ListAccounts(ctx)
141	if err != nil {
220	if err != nil {
142		return fmt.Errorf("listing accounts: %w", err)
221		return fmt.Errorf("listing accounts: %w", err)
143	}
222	}
144	fmt.Fprintf(output, "  %s:", label)
223	fmt.Fprint(output, "  balances:")
145	for _, account := range accounts {
224	for _, account := range accounts {
146		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
225		fmt.Fprintf(output, " %s %d", account.Owner, account.Balance)
147	}
226	}
148	fmt.Fprintln(output)
227	fmt.Fprintln(output)
149	return nil
228	return nil
150}
229}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

Query	Field	PostgreSQL	CockroachDB
OpenAccount	parameter `balance`	`int32`	`int64`
Deposit	parameter `amount`	`int32`	`int64`
Withdraw	parameter `amount`	`int32`	`int64`
ListAccounts	column `balance`	`int32`	`int64`