postgres

Open a PostgreSQL Repo with production defaults: configuration from a connection URL, TLS unless the server is local, UTC and session timeouts on every connection, and a start that waits for the server and fails clearly instead of leaving every later query to fail. The result is an ordinary Gloo Repo, so an application that builds its own pool loses nothing and can keep doing so.

Code that queries pog directly, such as Squirrel’s generated functions, can share the pool and its transactions: see start_pool and transaction.

let assert Ok(db) =
  postgres.from_env()
  |> result.map(postgres.pool_size(_, 20))
  |> result.try(postgres.start)

start links the pool to the calling process. In an app, put it under the supervisor with supervised instead, so a pool that crashes is restarted rather than taking the caller with it.

listen hears NOTIFY on a channel, from this node or any other, and notify sends one.

Types

Config

opaque </>

pub opaque type Config

Error

</>

pub type Error {
  MissingUrl(variable: String)
  InvalidUrl
  UnsupportedSslMode(mode: String)
  StartFailed
  Unreachable
  UnsupportedVersion(found: Int, minimum: Int)
}

Constructors

```
MissingUrl(variable: String)
```
The environment variable is unset or empty.
```
InvalidUrl
```
The URL is not postgres://user:password@host:port/database. It is not repeated here because it may contain a password.
```
UnsupportedSslMode(mode: String)
```
An sslmode other than disable, require, verify-ca or verify-full. There is no fallback to plain text, so allow and prefer are refused.
```
StartFailed
```
```
Unreachable
```
No query succeeded before the startup timeout: the server is down or unreachable, refused the credentials, or the database does not exist.

UnsupportedVersion(found: Int, minimum: Int)

ListenError

</>

pub type ListenError {
  NotPostgres
  SettingsNotFound
  InvalidChannel
}

Constructors

```
NotPostgres
```
The Repo is not on PostgreSQL.
```
SettingsNotFound
```
The Repo’s pool was not opened by this module and its settings could not be found inside it. Give them with listen_via.
```
InvalidChannel
```
A channel is a plain name: lowercase letters, digits and _, not starting with a digit, at most 63 characters.

Listener

opaque </>

A process hearing NOTIFY on one channel. See listen.

pub opaque type Listener

Pool

opaque </>

A PostgreSQL pool reachable two ways: as the Gloo Repo that Howdy modules take, and as the pog.Connection for code that talks to pog directly, such as queries generated by Squirrel. Both use the same connections.

pub opaque type Pool

Ssl

</>

pub type Ssl {
  SslDisabled
  SslUnverified
  SslVerified
}

Constructors

```
SslDisabled
```
Unencrypted. Only for a server on the same host or a private network.
```
SslUnverified
```
Encrypted without checking the server’s certificate. Protects against eavesdropping but not impersonation.
```
SslVerified
```
Encrypted, with the certificate checked against the system’s CAs and the host name.

Values

connection

</>

pub fn connection(pool: Pool) -> pog.Connection

The pog connection, for queries that do not go through Gloo.

error

</>

pub fn error(error: pog.QueryError) -> service.Error

The error for a failed pog query, matching howdy/database: a constraint violation is a Conflict, anything else is Internal. Driver messages may contain personal data, so neither repeats them.

error_to_string

</>

pub fn error_to_string(error: Error) -> String

from_env

</>

pub fn from_env() -> Result(Config, Error)

Read the connection URL from DATABASE_URL, as most hosts provide it.

from_pog

</>

pub fn from_pog(started: actor.Started(pog.Connection)) -> Pool

Adopt a pool the application started with pog.start itself. Unlike start_pool, this neither waits for the server nor checks its version.

from_url

</>

pub fn from_url(url: String) -> Result(Config, Error)

Parse postgres://user:password@host:port/database?sslmode=.... The user defaults to postgres and the port to 5432; percent-encoded characters in the user, password and database are decoded. Without sslmode, TLS is off for loopback addresses and dotless host names such as a Compose service called db, and verified for everything else.

Every connection then starts with these settings:

timezone UTC
statement_timeout 30 seconds
lock_timeout 5 seconds, the counterpart of SQLite’s busy timeout
idle_in_transaction_session_timeout 60 seconds, so a transaction left open cannot hold its locks indefinitely

idle_in_transaction_timeout

</>

pub fn idle_in_transaction_timeout(
  config: Config,
  milliseconds: Int,
) -> Config

End the session of a transaction left idle for longer. Zero disables the limit.

listen

</>

pub fn listen(
  repo: repo.Repo,
  channel: String,
  notify: fn(String) -> Nil,
) -> Result(Listener, ListenError)

Hear NOTIFY on channel, sent by any connection to the database, such as another node of the app. notify is called with each payload, in the listener’s own process, one at a time; a crash in it is logged and listening carries on.

let assert Ok(_) =
  postgres.listen(db, "orders", fn(id) { process.send(orders, id) })

The listener has a connection of its own, not one of the pool’s, opened with the pool’s settings: remembered for a pool this module opened, and otherwise looked for inside the running pool. It returns once it is listening, or has tried for five seconds, so a NOTIFY sent after listen returns is heard while the server is up.

The listener is linked to the caller and stops when it does. When the connection is lost it connects and listens again by itself, but what was sent meanwhile is not heard: check now and then as well, more slowly, if missing one matters.

listen_error_to_string

</>

pub fn listen_error_to_string(error: ListenError) -> String

listen_via

</>

pub fn listen_via(
  config: pog.Config,
  channel: String,
  notify: fn(String) -> Nil,
) -> Result(Listener, ListenError)

listen, connecting with these settings, such as pog.url_config(name, url): for a pool this module did not open, whose settings listen cannot find. The pool name in them is not used.

listener_pid

</>

pub fn listener_pid(listener: Listener) -> process.Pid

The listener’s process, to monitor it or to hand it to a supervisor.

lock_timeout

</>

pub fn lock_timeout(config: Config, milliseconds: Int) -> Config

Fail a statement that waits longer for a lock. Zero disables the limit.

minimum_version

</>

pub const minimum_version: Int

The oldest PostgreSQL major version still supported upstream.

notify

</>

pub fn notify(
  repo: repo.Repo,
  channel: String,
  payload: String,
) -> Result(Nil, service.Error)

Send payload to everything listening on channel. Inside a transaction it is sent when the transaction commits, and not at all if it rolls back. PostgreSQL limits a payload to 8000 bytes.

parameter

</>

pub fn parameter(
  config: Config,
  name: String,
  value: String,
) -> Config

Set any PostgreSQL run-time parameter when each connection opens, replacing an earlier value for the same name. application_name cannot be set here: the driver always reports the Erlang node name, which a release sets to something like notes@host. Poolers such as PgBouncer may refuse parameters they do not recognise: set them on the role instead (ALTER ROLE ... SET) and pass zero to the timeout functions here.

pool_size

</>

pub fn pool_size(config: Config, size: Int) -> Config

Connections kept open. Default 10.

repo

</>

pub fn repo(pool: Pool) -> repo.Repo

The Repo for Howdy modules and howdy/database.

ssl

</>

pub fn ssl(config: Config, ssl: Ssl) -> Config

start

</>

pub fn start(config: Config) -> Result(repo.Repo, Error)

Start the pool and wait until the server answers and is at least minimum_version. The pool is closed again when it does not. The pool is linked to the caller; in an app, prefer supervised.

start_pool

</>

pub fn start_pool(config: Config) -> Result(Pool, Error)

start, keeping the pog.Connection as well as the Repo.

let assert Ok(pool) = postgres.from_env() |> result.try(postgres.start_pool)
let db = postgres.repo(pool)
let assert Ok(pog.Returned(rows:, ..)) =
  sql.find_notes(postgres.connection(pool), owner)

startup_timeout

</>

pub fn startup_timeout(
  config: Config,
  milliseconds: Int,
) -> Config

How long start waits for the server to answer. Default 10 seconds.

statement_timeout

</>

pub fn statement_timeout(
  config: Config,
  milliseconds: Int,
) -> Config

Cancel any statement that runs longer. Zero disables the limit. howdy/migration lifts it for its own transaction.

stop_listening

</>

pub fn stop_listening(listener: Listener) -> Nil

Stop listening.

supervised

</>

pub fn supervised(
  config: Config,
) -> #(Pool, supervision.ChildSpecification(Pool))

The pool as a child of a supervisor, with the handle to query it by. The handle is made here, before the pool starts, so it can be given to the app while the supervisor owns the pool:

let #(pool, child) = postgres.supervised(config)
static_supervisor.new(static_supervisor.OneForOne)
|> static_supervisor.add(child)
|> static_supervisor.add(howdy.supervised(app(postgres.repo(pool))))
|> howdy.start_application(name: "my_app_server")

Starting the child waits for the server like start does, up to startup_timeout, so a supervisor whose database is down fails to start with a clear reason rather than serving requests that all fail. The handle keeps working across restarts of the pool; a query made while it is restarting, or after the supervisor stops, crashes the calling process (usually exiting with noproc), as pog does for any named pool. Because the supervisor owns the pool, repo.close on this handle’s Repo does nothing: stop the supervisor instead.

transaction

</>

pub fn transaction(
  pool: Pool,
  run: fn(pog.Connection, repo.Repo) -> Result(a, service.Error),
) -> Result(a, service.Error)

Commit when run returns Ok; roll back and return its error otherwise. run gets the transaction twice: as a pog.Connection for pog and Squirrel queries, and as a Repo for Howdy modules, whose own transactions become savepoints within this one. Map pog errors with error.

Nesting only works that way round. Inside a database.transaction, or another transaction, pog would check out a second pooled connection, so the writes would not be atomic with the outer transaction’s and could deadlock against its row locks. That is refused with an Internal error rather than run.

use conn, db <- postgres.transaction(pool)
use note <- result.try(
  sql.insert_note(conn, owner, body) |> result.map_error(postgres.error),
)
audit.record(db, owner, "note created")