Compare · 018

Missing rows without errors: TimescaleDB and CockroachDB

TimescaleDB and CockroachDB run the same files for this example: the SQL and the Go are identical.

Compare TimescaleDB withSQLitePostgreSQLCockroachDBDuckDB

Configuration

sgl.yamlidentical
TimescaleDB
CockroachDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: ringing
4 - name: ringing
5 dialect: postgres
5 dialect: postgres
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store
11 driver: database/sql
11 driver: database/sql
12 migrate:
12 migrate:
13 dsn: ${env:DATABASE_URL}
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_gulls.up.sqlidentical
TimescaleDB
CockroachDB
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 ring TEXT NOT NULL UNIQUE,
3 ring TEXT NOT NULL UNIQUE,
4 species TEXT NOT NULL
4 species TEXT NOT NULL
5);
5);
db/migrations/0001_gulls.down.sqlidentical
TimescaleDB
CockroachDB
1DROP TABLE gulls;
1DROP TABLE gulls;

Queries

RingGullidentical
TimescaleDB · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(RingGull, one, optional: true)
1-- sgl.query(RingGull, one, optional: true)
2INSERT INTO gulls (ring, species) VALUES ($1, $2)
2INSERT INTO gulls (ring, species) VALUES ($1, $2)
3ON CONFLICT (ring) DO NOTHING
3ON CONFLICT (ring) DO NOTHING
4RETURNING id, ring;
4RETURNING id, ring;
FindGullidentical
TimescaleDB · db/queries/gulls.sql
CockroachDB · db/queries/gulls.sql
1-- sgl.query(FindGull, one, optional: true)
1-- sgl.query(FindGull, one, optional: true)
2SELECT id, ring, species FROM gulls WHERE ring = $1;
2SELECT id, ring, species FROM gulls WHERE ring = $1;

What sgl generates

RingGullidentical
TimescaleDB
CockroachDB
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) (RingGullRow, bool, error)
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) (RingGullRow, bool, error)
2
2
3type RingGullParams struct {
3type RingGullParams struct {
4 Ring string
4 Ring string
5 Species string
5 Species string
6}
6}
7
7
8type RingGullRow struct {
8type RingGullRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Ring string `json:"ring"`
10 Ring string `json:"ring"`
11}
11}
FindGullidentical
TimescaleDB
CockroachDB
1func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error)
1func (queries *Queries) FindGull(ctx context.Context, ring string) (FindGullRow, bool, error)
2
2
3type FindGullRow struct {
3type FindGullRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Ring string `json:"ring"`
5 Ring string `json:"ring"`
6 Species string `json:"species"`
6 Species string `json:"species"`
7}
7}

The program

example.goidentical
TimescaleDB
CockroachDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8
8
9 "seagull.sh/seagull/examples/postgres/018_optional_one/internal/store"
9 "seagull.sh/seagull/examples/postgres/018_optional_one/internal/store"
10)
10)
11
11
12// run rings gulls and looks them up. Both queries are one queries with optional: true, so
12// run rings gulls and looks them up. Both queries are one queries with optional: true, so
13// they return (row, ok, error): a missing row gives ok false and a nil error instead of
13// they return (row, ok, error): a missing row gives ok false and a nil error instead of
14// sql.ErrNoRows, and error is left for real failures.
14// sql.ErrNoRows, and error is left for real failures.
15//
15//
16// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
16// RingGull inserts with ON CONFLICT (ring) DO NOTHING RETURNING. A ring that is already
17// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
17// taken skips the insert, so RETURNING has no row and ok is false. Without optional: true
18// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
18// sgl vet warns about this (Q046), as the call would fail with sql.ErrNoRows. optional
19// applies only to a one query; on any other command sgl vet refuses it (Q047).
19// applies only to a one query; on any other command sgl vet refuses it (Q047).
20//
20//
21// PostgreSQL takes the next id from the identity column's sequence before it checks for
21// PostgreSQL takes the next id from the identity column's sequence before it checks for
22// the conflict, and a skipped insert does not hand it back. The black-headed gull that is
22// the conflict, and a skipped insert does not hand it back. The black-headed gull that is
23// turned away and then ringed GB1003 becomes gull 4, not gull 3.
23// turned away and then ringed GB1003 becomes gull 4, not gull 3.
24//
24//
25// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives each ringing and lookup.
26// Takes output (io.Writer) which receives each ringing and lookup.
27//
27//
28// Returns error when a query fails.
28// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30 queries := store.New(database)
30 queries := store.New(database)
31
31
32 attempts := []store.RingGullParams{
32 attempts := []store.RingGullParams{
33 {Ring: "GB1001", Species: "herring gull"},
33 {Ring: "GB1001", Species: "herring gull"},
34 {Ring: "GB1002", Species: "kittiwake"},
34 {Ring: "GB1002", Species: "kittiwake"},
35 {Ring: "GB1001", Species: "black-headed gull"},
35 {Ring: "GB1001", Species: "black-headed gull"},
36 {Ring: "GB1003", Species: "black-headed gull"},
36 {Ring: "GB1003", Species: "black-headed gull"},
37 }
37 }
38 for _, attempt := range attempts {
38 for _, attempt := range attempts {
39 ringed, ok, err := queries.RingGull(ctx, attempt)
39 ringed, ok, err := queries.RingGull(ctx, attempt)
40 if err != nil {
40 if err != nil {
41 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
41 return fmt.Errorf("ringing %s: %w", attempt.Ring, err)
42 }
42 }
43 if !ok {
43 if !ok {
44 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
44 fmt.Fprintf(output, "ring %s is already taken, so the %s was not ringed\n", attempt.Ring, attempt.Species)
45 continue
45 continue
46 }
46 }
47 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
47 fmt.Fprintf(output, "ringed a %s as %s, gull %d\n", attempt.Species, ringed.Ring, ringed.ID)
48 }
48 }
49
49
50 for _, ring := range []string{"GB1002", "GB9999"} {
50 for _, ring := range []string{"GB1002", "GB9999"} {
51 gull, ok, err := queries.FindGull(ctx, ring)
51 gull, ok, err := queries.FindGull(ctx, ring)
52 if err != nil {
52 if err != nil {
53 return fmt.Errorf("finding %s: %w", ring, err)
53 return fmt.Errorf("finding %s: %w", ring, err)
54 }
54 }
55 if !ok {
55 if !ok {
56 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
56 fmt.Fprintf(output, "no gull wears ring %s\n", ring)
57 continue
57 continue
58 }
58 }
59 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
59 fmt.Fprintf(output, "ring %s is on gull %d, a %s\n", ring, gull.ID, gull.Species)
60 }
60 }
61 return nil
61 return nil
62}
62}