hoppersqlite

Overview ¶

Package hoppersqlite is hopper's SQLite driver, for an application that runs on one machine and wants hopper without a database server: a command-line tool that runs a workflow, a single-node service, local development. It is pure Go (modernc.org/sqlite, SQLite transpiled to Go), so a program that uses it builds with CGO_ENABLED=0.

d, err := hoppersqlite.Open("/var/lib/app/hopper.db", nil)
if _, err := hoppermigrate.Up(ctx, d, nil); err != nil { ... }
client, err := hopper.NewClient(d, cfg)

It passes the drivertest conformance suite, as the Postgres drivers do, and several processes may share one database. It uses internal packages of hopper's module, so it works with the hopper release of its own version: upgrade the two together. What SQLite's design makes different from Postgres:

• One transaction writes at a time (driver.Capabilities.SingleWriter). A transaction that writes holds the database's write lock until it ends, and every other write waits for it: within this process in turn, for as long as its context allows, and across processes through SQLite's busy handler, which polls rather than queues, so a process that writes without pause holds another's writes up for longer than its own. A claim is exclusive without SKIP LOCKED, and the transaction that finalizes a job settles its batch and promotes its dependents itself. A finalize with SkipLocked, or a progress write with NoWait, waits 100ms for a write lock held elsewhere, and then skips every job, or returns driver.ErrJobLocked.
• So a transaction held open holds up every client that shares the database, their lease renewals included: one held for longer than about the lease (hopper.Config.LeaseTTL) costs them their leases, and their running jobs are rescued. Keep transactions short.
• Transactions come from DB, whose connections take the write lock when a transaction runs its first statement, not when it begins, so that what it reads is never stale by the time it writes, and so that beginning one never blocks. A transaction begun read-only takes no lock and may not write. Within a transaction, write through it (InsertTx and the like), not through the client: a write on the pool would wait for the transaction's own lock. In a job's worker, whose context names the job's attempt, such a write fails at once with ErrLockHeldByOwner instead, if the worker began the transaction with that context or one derived from it; begin it so. Writes of the attempt with no transaction of its own open wait their turn, from however many goroutines.
• After some errors (an interrupted write, a full disk, an I/O error) SQLite rolls the whole transaction back itself. The transaction's later statements, and its Commit, then fail with ErrTransactionAborted, as those of a transaction an error aborted do on Postgres. An error that SQLite undoes only the statement of, such as a constraint's, leaves the transaction open, where Postgres would abort it. A COMMIT, END or ROLLBACK statement run in a transaction ends it: its later statements fail with sql.ErrTxDone. In a read-only transaction SQLite tells nothing of a COMMIT, and the statements after it, reads all, run on their own.
• The database must be on a local filesystem: Open refuses one on a network filesystem, or a virtual machine's shared folder, with ErrNetworkFilesystem, checking where symbolic links in the path lead. Its clock is therefore the host's, which every process that opens it shares, and the driver reads it as database time, never earlier than a time it read before in the same process (a host clock stepped back can still date one process's write before another's earlier one). A host that sleeps and wakes finds its clock moved on, as a database server's would, and may rescue the jobs of its own clients, whose leases expired while it slept.
• Commits are durable once they return (Config.Synchronous), on macOS with F_FULLFSYNC. The write-ahead log is cut back to 32MiB once a checkpoint empties it, and JobsMaintain truncates it once a reader that held up checkpoints is done.
• Notifications are rows of a table, delivered on commit to listeners in every process that opens the database: at once in this process, and within ListenInterval in others.
• History is one table, and retention deletes its rows in batches, without returning the space to the filesystem; JobsMaintain refreshes statistics but never vacuums. A stream's events commit in the order they are appended, so a position is an event's sequence number (Xid is always 0).
• Args, metadata and other JSON values are stored as given, not normalized as Postgres's jsonb normalizes them, and refused for what jsonb refuses. A metadata filter matches as jsonb's @> does.
• Job IDs are minted in Go, from crypto/rand, rather than by the database. There is no COPY: bulk inserts go through the one insert path. Nor are there SQL functions to insert jobs from SQL.