happenstance-postgres 0.8.0

PostgreSQL event store and projection store adapters for happenstance.
Documentation
# happenstance-postgres

The PostgreSQL event store and projection store adapters for
[happenstance](https://github.com/Wet-Ink-Corporation/happenstance) — a
storage-agnostic event sourcing library built on the
[Dynamic Consistency Boundary specification](https://dcb.events/specification/).

New to happenstance? [The happenstance guide](https://wet-ink-corporation.github.io/happenstance/)
explains event sourcing and dynamic consistency boundaries from the beginning,
with programs you can run.

[![Buy me a coffee](https://img.shields.io/badge/buy%20me%20a%20coffee-support-yellow?logo=buymeacoffee&logoColor=black)](https://buymeacoffee.com/ryanbritton)

> **Status: conformant, and in the `0.8.0` release set.** **108 of 108** gated
> tests in the event-store binary passed against a live PostgreSQL 17.10, counted at `0.4.0` — the 95
> rules of the event-store family, the 6 of the concurrency family at 64
> contenders, the generated model family, and this crate's own six. The
> projection suite clears its own target beside it.
>
> Stated per target rather than as one total, because the total was wrong: this
> line said 132 and the four targets list 133 between them. A composite nobody
> can reproduce from a single command is a number that goes stale quietly, and
> this one had. This was the **first adapter in the portfolio to clear the concurrency
> family against a store whose writers are not serialised**, which is the whole
> reason the crate exists; `happenstance-neon` reaches the same server through a
> different transport and clears it too. It has been on crates.io since
> `0.2.0`.
>
> **One of those gated tests runs a weaker check than the rest.** The fixture
> declines `READ_YOUR_OWN_WRITES`, so the generated model family holds this store
> to the testkit's trailing model instead of its exact one. That family
> predicts whether a conditional append will be refused,
> and this store judges a condition over every committed row while a read shows
> only the rows below its frontier — see *What this store costs a caller*. The
> trailing model predicts over what is held and defers a verdict it cannot
> decide yet. What it gives up is listed on `ops_agree_with_the_model` in
> `happenstance-testkit`: chiefly, a write that never becomes visible reads as
> lag rather than as a lost write.
>
> **None of the others skips for it.** The 83 named rules that read back their
> own appends ask the fixture to settle the frontier first (CF-42), and the
> fixture waits there until a read shows every row this schema holds.

## Which crate do I want?

- **Writing an application?** Use [`happenstance`]https://crates.io/crates/happenstance,
  and reach for this crate only to choose where the events live.
- **Want the contract without a driver?**
  [`happenstance-core`]https://crates.io/crates/happenstance-core is the
  smaller semver surface, and it ships an in-memory reference store.
- **Want a single file rather than a server?**
  [`happenstance-sqlite`]https://crates.io/crates/happenstance-sqlite.
- **Writing your own adapter?** Read this one for the position-visibility
  problem, then measure yours against
  [`happenstance-testkit`]https://crates.io/crates/happenstance-testkit. A
  crate that compiles is not an adapter until it has run that suite.

## Features

```toml
happenstance-postgres = "0.8"                              # the event store
happenstance-postgres = { version = "0.8", features = ["projection-store"] }
happenstance-postgres = { version = "0.8", features = ["sync"] }   # + the ingest port

sqlx = "0.8"                                               # only for queries of your own
```

You do not need that third line to name the driver. `PgPool` and `sqlx::Error`
are on this crate's public signatures, so `sqlx` is re-exported: reach it as
`happenstance_postgres::sqlx::…`. That buys *type identity* — the `sqlx::Error`
you match on is the one this crate's error enum actually carries.

`projection-store` is off by default and gates this crate's own projection
modules — two of them. `PostgresProjectionStore` buffers and is the one an
application uses; `LivePostgresProjectionStore` holds a live `sqlx`
transaction as its batch and is the instrument that stood at the far end of
PS-2's batch-shape axis, which is what let ADR-0063 freeze the port. The flag
forwarded `happenstance-core`'s `unstable-projection` gate until that decision;
it forwards nothing now, because the port needs no feature.

`sync` is off by default and is the store's half of replication: it
implements `happenstance-sync`'s `SendIngestStore` for `PostgresEventStore`,
so generic code bound on `IngestStore` accepts it, and re-exports the port as
`happenstance_postgres::happenstance_sync`. The identity an ingest checks
claims against is the one `append` stamps from, read from `store_meta` inside
every append's and every ingest's own transaction, never cached on the handle
(#104). It turns on `event-store`, and `happenstance-core`'s
`serde` feature through `happenstance-sync`. It also implements
`SendWriteGroupStore`, which reads the log back in the writes that made it
(#420), and stores the guard an ingested group arrived under, which makes it a
local side for `happenstance-sync`'s runner, `runner::Edge` (#244).

The sync peer over this store is `happenstance-sync`'s `StoreSyncPeer`, with
the store as both halves: `StoreSyncPeer::new(store.clone(), store)`. There is
no Postgres peer type, unlike `happenstance-neon`'s `NeonSyncPeer`, because
over a pool the peer's two queries a pull and three a push are cheap, and
`read_groups` already decides `waiting` inside its own statement (SY-37). CI's
`live-postgres` job runs `sync_peer_conformance!` on it, with a replay and a
byte-identical round trip, and the workspace's `harness/sync-round-trip` runs a
native SQLite store through it (#131).

## The problem this crate is here to solve

`nextval()` allocates **outside** the transaction. So on any naive Postgres
schema, position order is not visibility order: a reader can see position 7
while 6 is still uncommitted, and a projection checkpointing on `after` skips
the events that appear below it. Data loss, no error, no failing test.

The specification calls that ES-10 — *once a position is visible, nothing below
it becomes visible later* — and every other adapter in this workspace satisfies
it for free by assigning positions under a lock it holds until commit. This one
cannot, which is why it is an **instrument before it is a target**.

What it does instead is ADR-0024's arm C, chosen on a measurement rather than a
preference: an `xid8` column defaulted to `pg_current_xact_id()`, and every read
bounded by `xact_id < pg_snapshot_xmin(pg_current_snapshot())`. Position order
stops being treated as visibility order, and a frontier predicate goes on the
read side. The two serialising alternatives measured 16× and 30× at 64 writers;
this one measured 0.99–1.03× of baseline.

**The cost is real and is stated rather than buried.** Any long-running
transaction anywhere in the database holds `pg_snapshot_xmin` back, so one
forgotten `BEGIN` in an unrelated application delays what this store's readers
can see. It does not corrupt anything and it does not error; it makes readers
lag. `head()` reports the frontier, not the newest row.

## Two roles, one pool

```rust,ignore
let pool = PgPool::connect(url).await?;
happenstance_postgres::migration::apply(&pool).await?;
happenstance_postgres::migration::apply_projection(&pool).await?;

let events = PostgresEventStore::new(pool.clone());
let views  = PostgresProjectionStore::new(pool);
```

Sharing one pool is the expected deployment, which is why neither type builds
its own. The schema lives in `migrations/` as SQL and is compiled in, so a
consumer needs nothing from the repository to apply it — `migration::MIGRATION_1`
and `migration::MIGRATION_3` for the event log, both applied by `migration::apply`,
and `migration::MIGRATION_2` for the projection checkpoint, applied by
`migration::apply_projection`.
They are applied separately on purpose: an application using only the event store
gets no `projection_checkpoint` table.

**Open the pool on a runtime that lives at least as long as the pool.** A pooled
connection's socket belongs to the I/O driver of the tokio runtime that opened
it. Once that runtime is dropped, `sqlx` does not see the connection as broken,
and a query handed it ends in `PoolTimedOut` or does not finish. Neither store
can rebuild your pool, so this one is yours to keep. A pool opens connections
lazily, inside the call that needs one, so the runtimes you call the stores on
should outlive the pool too. The usual way to break it is a pool in a `static`,
initialised inside one `#[tokio::test]` and used by the next, each test on its
own runtime.

**Call the event store from a runtime with tokio's drivers enabled** —
`enable_all`, as `#[tokio::main]` and `#[tokio::test]` do. `PostgresEventStore`
runs each operation on the runtime it is called on (ADR-0081), and `sqlx`
acquires every connection under `tokio::time::timeout`, so a call from a runtime
built without `enable_time` fails as `PostgresEventStoreError::Worker` carrying a
panicked `JoinError`. `PostgresEventStore::new` documents both under *Runtimes*.

## What the projection batch is, and what it costs you

`PostgresProjectionBatch` is an **owned buffered write set**, not a live
`sqlx::Transaction`, and it is the default to reach for: it holds no connection
while `apply` runs. Its transaction is opened inside `commit`, which replays
your statements and writes the checkpoint in one unit.

The consequence for you: **a projection whose `apply` must read what it has
already written in the same batch cannot be written against the buffered
store.** `READS_THROUGH_BATCH` is `false` and the conformance suite reports it
as a skip rather than passing quietly.

`LivePostgresProjectionStore`'s batch is a real transaction opened at `begin`,
and since the typed layer's `apply` became `async` (ADR-0074) a projection
awaits `LivePostgresBatch::execute` inside it and `run_projection` drives it:
statements reach the server as `apply` runs. The transaction does read through
(PS-12's rule runs against it, through the conformance probe), but
`LivePostgresBatch` has no query method yet, so `apply` cannot read its pending
writes back on either store. The trade for the live store: a pooled connection
held for a whole chunk, and a statement the server refuses aborts the
transaction, unless `apply` rolled back to a savepoint it set (#103). The
buffered store stays the default.

## Upgrading to schema version 2: migrate, then deploy

This release adds migration 3 (`migrations/0003_write_group.sql`): two nullable
columns on `event`, `write_group` and `group_guard`, and a partial index on the
first. They record which rows one append, or one ingested group with its
compensation, wrote together, so a runner and a store peer forward the log in
whole writes (#420). Every `INSERT` this build sends names `write_group`, so
**run `migration::apply` against the schema before deploying this build to
any process that writes to it**. A build that runs against a schema without the
column fails its first append with an undefined-column error from the server.

`apply` is idempotent, so on a schema an older build migrated only migration 3
does anything. Its two `ALTER TABLE … ADD COLUMN`s rewrite no row, but take a
brief exclusive lock on `event`; its index build blocks writes while it runs. On
a large table, build the index first with
`CREATE INDEX CONCURRENTLY event_write_group_idx ON event (write_group) WHERE write_group IS NOT NULL`,
and `apply` then leaves it alone. Rows written before the migration read back as
groups of one, with no guard; nothing is backfilled. An older build keeps
working against a migrated schema, and writes rows that read as groups of one.

## Upgrading to schema version 3: migrate, then deploy

`0.8` adds migration 4 (`migrations/0004_removal_record.sql`,
`migration::MIGRATION_4`), and `SCHEMA_VERSION` is `3`. It creates the
**removal record** (#483, #484, SY-40): a table, `event_removed`, and a
`BEFORE DELETE` row trigger on `event`, `event_record_removal`, that records the
origin identity of every event deleted from the log, by this crate or by any
other client (`psql`, a retention job), whatever that session's `search_path`.
Ingest skips an event whose identity is recorded there and counts it in
`Ingested::skipped`, so a pruned replica that re-pulls, or is re-pushed, what it
pruned neither takes the events back nor refuses its own as
`UnheldOwnIdentity`. A recorded event is dropped before it is written, so it
draws no position, and an own claim the store never held is still refused
(SY-38).

Every ingest takes `LOCK TABLE event_removed IN SHARE MODE` in its first round
trip, so **run `migration::apply` against the schema before deploying this
build to any process that ingests**: against a schema without the table, an
ingest fails with an undefined-table error from the server. Appends do not read
the record and are unaffected. An older build keeps working against a migrated
schema, but its ingest ignores the record and re-admits what was removed, so
upgrade every process that replicates into the store. `apply` is idempotent; on
a schema at version 2 its only lasting lock is the trigger's creation, which
blocks writes to `event` for a moment.

### Privileges

A role that deletes from `event`, a retention job granted `SELECT, DELETE` on
it, needs **nothing** more: the trigger's function is `SECURITY DEFINER`, so its
insert into `event_removed` runs as the role that ran the migration. A role that
ingests needs `SELECT` and `UPDATE` on `event_removed`, on top of what it held
under `0.7`: `SELECT` for the lookup, and `UPDATE` because Postgres admits
`LOCK TABLE ... IN SHARE MODE` only to a role holding `UPDATE`, `DELETE`,
`TRUNCATE` or, from Postgres 17, `MAINTAIN`. On 17, `SELECT, MAINTAIN` is the
narrower grant, as it writes nothing:

```sql
GRANT SELECT, UPDATE ON event_removed TO <ingest role>;
```

### A session holding a `DELETE` holds every ingest

The lock makes a removal and an ingest wait for each other rather than race,
and Postgres' default `lock_timeout` is `0`, which waits for ever. So a `psql`
session that runs a `DELETE` on `event` inside a transaction and leaves it open
stops every ingest into the store until it commits or rolls back. Set a
`lock_timeout` on the ingesting role, `ALTER ROLE <ingest role> SET
lock_timeout = '5s'`, or on its connections, `options=-c lock_timeout=5s` in
the URL. An ingest that gives up then fails with SQLSTATE `55P03`,
which this crate classifies as transient, so the runner retries it rather than
stopping the edge. The crate sets no bound of its own. `TRUNCATE` fires no
row trigger, so it records nothing. Rows deleted before the migration are not
recorded either; to record one by hand, run
`INSERT INTO event_removed (origin_store, origin_position) VALUES ('\x<origin store id, 16 bytes>'::bytea, <origin position>) ON CONFLICT DO NOTHING`.
The record grows by one small row per removed event and is kept for ever:
deleting a row of it lets that identity be ingested again, so do that only once
no peer can offer the event. A restore brings the record back with the log and
the sequence, which is what keeps SY-38's refusal of a restored store true.

## After a restore, re-mint before the first append

A `pg_restore`, a point-in-time recovery, a promoted replica that lost acknowledged writes, or any
other copy of this store's database brings back its identity row together with an older position
sequence. The copy would then re-issue event identities it has already issued, and a replication
peer would silently drop the new events as already seen. In this order:

1. Stop every process that appends to the copy.
2. Re-mint the copy's identity: call `PostgresEventStore::remint_identity`, or run, against the
   copy, the statement it runs:
   ```sql
   UPDATE store_meta SET v = uuid_send(gen_random_uuid()) WHERE k = 'store_id';
   ```
3. Resume appends.

No restart is needed: every append reads the identity row inside its own transaction, so a
handle opened before step 2 stamps the new identity from its next append on. Events already in
the database keep the identity they were written with.

## What its conformance fixtures decline

A rule whose capability a fixture declines still runs, and reports the fixture's stated
reason as a `SKIP` line rather than vanishing from the binary (CF-18). A default
`cargo test` does not show that line; `--show-output` does. So the declines are listed
here, where a reader choosing this adapter will see them. Every capability the table does
not name is supported.

| Fixture | Capability | Why it is declined |
| --- | --- | --- |
| `PostgresFixture` | `READ_YOUR_OWN_WRITES` | Reads and `head` admit only rows below `pg_snapshot_xmin` (ADR-0024), so a committed append is not visible until every transaction open when it committed has ended. The named rules wait for the frontier (CF-42), and the model family runs its trailing model |
| `PostgresProjectionFixture` and `LivePostgresProjectionFixture` | `RESET_REFUSAL` | No call marks a projection protected, so `reset` never returns `Refused`. A gap in scope, tracked as #406 |

The reasons in full are on the constants in `tests/support/mod.rs`.

## Limits this store enforces

| | |
|---|---|
| `MAX_EVENT_DATA_LEN` | 1 MiB |
| `MAX_TAGS_PER_EVENT` | 128 |
| `MAX_EVENTS_PER_BATCH` | 256 |

## Running its tests

They need a Docker daemon, and they are `#[ignore]`d so that a machine without
one still has a green build:

```console
cargo test -p happenstance-postgres --all-features -- --ignored --list
cargo test -p happenstance-postgres --all-features -- --ignored --show-output --test-threads=1
```

`--list` first, so "no rule is absent from the run" is proven rather than
assumed. `--test-threads=1` is not a flake workaround: it is connection
arithmetic, and the reasoning is in the test module.

## Minimum supported Rust version

Rust **1.97.1**, the `rust-version` this crate declares
([ADR-0029](https://github.com/Wet-Ink-Corporation/happenstance/blob/main/.kb/decisions/0029-msrv-raised-to-1-97-1.md)).
After `1.0.0` the floor rises only in a minor release, never a patch; only to a
Rust stable at least six months old on the day that minor ships; and always with
a `CHANGELOG.md` entry naming the old floor, the new one and what forced it
([ADR-0067](https://github.com/Wet-Ink-Corporation/happenstance/blob/main/.kb/decisions/0067-msrv-after-1-0-rises-are-bounded.md)).

Before `1.0.0`, a rise ships in a `0.x` minor, which a `"0.x"` requirement never
resolves across
([ADR-0037](https://github.com/Wet-Ink-Corporation/happenstance/blob/main/.kb/decisions/0037-msrv-becomes-a-promise-at-0-2-0.md)).
From `1.0.0`, what keeps a rise from reaching you is your workspace's resolver,
not this crate's: under `resolver = "3"`, the default for an edition-2024 root,
or with `resolver.incompatible-rust-versions = "fallback"` set, Cargo prefers
the newest version whose `rust-version` your lowest `package.rust-version` (or
your `rustc`, if you declare none) satisfies, and `cargo update` says so by
printing *to latest Rust 1.xx compatible versions* where resolver 2 prints *to
latest compatible versions*. On resolver 2, only your lockfile holds you back.

## Licence

MIT or Apache-2.0, at your option.