#Postgres Pool

• reusing PostgreSQL sessions
• isolating long-running queries from short ones
• letting multiple async tasks talk to PostgreSQL without sharing one connection timeline

#Quick Start

///|
async fn _pool_quick_start(
  host : String,
  user : String,
  database : String,
  password : String,
) -> Unit {
  @async.with_task_group(group => {
    let config = Config::new(
      host,
      user~,
      dbname=database,
      password~,
      application_name="my-service",
      pool=PoolConfig::new(2),
    )
    let pool = Pool::new(config, group)

    let value : Int = pool.with_client(client => {
      client.query_one("select 1::int4 as value").get_name("value")
    })
    ignore(value)

    pool.close()
  })
}

///|
async fn _pool_cancellable_example(pool : Pool) -> Unit {
  @async.with_task_group(group => {
    ignore(
      pool.with_client(client => {
        client.run_cancellable((op, token) => {
          group.spawn_bg(no_wait=true, () => {
            @async.sleep(50)
            token.cancel()
          })
          let value : Int = op
            .query_one("select pg_sleep(5), 1::int4 as value")
            .get_name("value")
          value
        })
      }),
    ) catch {
      _ => ()
    }
  })
}

#Choose The Checkout Style

• Pool::with_client(...): the normal entry point; borrow one client for one callback and release it automatically
• Pool::get(): manual lease management when the client must outlive one callback
• Client::with_transaction(f, options?): run one callback inside a transaction that auto-commits on success and rolls back best-effort on error
• Client::with_stream(...), with_simple_query(...), with_copy_in(...), with_copy_out(...): callback-scoped access to low-level streaming APIs
• Client::run_cancellable(...): exclusive one-request-at-a-time scope with a cancel token

#Multi-Target Config

• host and hosts are appended together in order
• port is broadcast to every target when ports is absent
• hostaddr and hostaddrs let TCP routing differ from certificate identity
• password is optional in the config, but each connection needs it when the server selects cleartext password or SCRAM authentication
• application_name is required by Config::new and is copied to every target
• target_session_attrs=ReadWrite rejects a target that reports transaction_read_only = on
• load_balance_hosts=Random randomizes the order in which new connections try targets; it does not reshuffle already-open idle connections

///|
fn _multi_target_config() -> Config raise {
  Config::new(
    "primary.db",
    user="moon",
    dbname="app",
    application_name="my-service",
    hosts=["replica.db"],
    ports=[5432, 5432],
    target_session_attrs=TargetSessionAttrs::read_write(),
    load_balance_hosts=Random,
    pool=PoolConfig::new(
      4,
      timeouts=Timeouts::new(
        wait_ms=Some(500),
        create_ms=Some(1000),
        recycle_ms=Some(250),
      ),
      queue_mode=QueueMode::lifo(),
      recycling_method=RecyclingMethod::verified(),
    ),
  )
}

#Timeouts, Queue Mode, And Recycling

• wait_ms: time spent waiting for pool capacity
• create_ms: time spent opening a new physical connection
• recycle_ms: time spent validating or cleaning one idle connection

• RecyclingMethod::fast(): trust the idle connection as-is
• RecyclingMethod::verified(): run a lightweight health check
• RecyclingMethod::clean(): reset session state before reuse
• RecyclingMethod::custom(sql): run your own cleanup SQL during checkout

#Pool Options

• post_create: run after a brand-new connection opens
• pre_recycle: run before recycle-time validation / cleanup of an idle connection
• post_recycle: run after recycling succeeded and just before checkout

///|
fn _pool_options_hook_example(group : @async.TaskGroup[Unit]) -> Pool raise {
  let config = Config::new(
    "db.example",
    user="moon",
    dbname="app",
    application_name="my-service",
    pool=PoolConfig::new(2),
  )
  let options = PoolOptions::new(
    post_create=client => client.batch_execute("set search_path to app, public"),
    pre_recycle=client => client.check_connection(),
  )
  Pool::new(config, group, options~)
}

#Safe API Surface

• fully-drained query helpers such as query_all, query_one, query_opt, and query_typed_all
• command helpers such as execute, batch_execute, and check_connection
• transaction helpers such as transaction, with_transaction, and with_savepoint
• scope-bound prepared statement helpers such as with_prepared(...) and with_prepared_cached(...)
• scope-bound low-level helpers such as with_stream(...), with_simple_query(...), with_copy_in(...), with_copy_out(...), and PreparedStatement::with_portal(...)

• Pool::get() requires the caller to release the lease
• Client::transaction() and Transaction::transaction() require an explicit commit() or rollback()
• Client::detach_raw() permanently removes the connection from pool management

#Statement Cache

///|
async fn _statement_cache_example(pool : Pool) -> Unit {
  pool.with_client(client => {
    client.with_prepared_cached("select $1::int4 as value", prepared => {
      let value = 7
      let params : Array[&@client.ToSql] = [value as &@client.ToSql]
      let row = prepared.query_one(params~)
      let decoded : Int = row.get_name("value")
      ignore(decoded)
    })
  })
  |> ignore

  let cache_size = pool.with_client(client => client.statement_cache().size())
  ignore(cache_size)

  pool.manager().statement_caches().clear()
}

• client.statement_cache(): manage the cache of the currently checked-out physical connection
• transaction.statement_cache(): same, but from inside one pooled transaction
• pool.manager().statement_caches(): clear or remove cached statements across every connection that is live right now

#Detaching A Raw Client

• the pool frees one capacity slot immediately
• the returned @client.Client keeps using the existing background driver task
• the pool no longer recycles, tracks, or closes that connection for you
• you must close the raw client yourself

///|
async fn _detach_raw_example(pool : Pool) -> Unit {
  let lease = pool.get()
  let raw = lease.detach_raw()
  let value : Int = raw.query_one("select 1::int4 as value").get_name("value")
  ignore(value)
  raw.close()
}