# Timeout Management and withClient

# Timeout Management and withClient

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [docs/ai/dal-usage-guide.md](docs/ai/dal-usage-guide.md)
- [src/dal/dal.ts](src/dal/dal.ts)
- [test/dal-timeout.test.ts](test/dal-timeout.test.ts)

</details>



Timeout management is a core anti-throttling strategy in `@primebrick/dal-pg` designed to prevent slow queries from starving the connection pool [src/dal/dal.ts:15-18](). The library enforces a `statement_timeout` at the session level by default and provides mechanisms to override this for specific long-running operations or transactions without leaking those settings to subsequent users of the connection [src/dal/dal.ts:23-30]().

## Anti-Throttling Strategy

The DAL implements a multi-layered timeout strategy to ensure high-async REST traffic remains responsive:

1.  **Global Session Default**: Every connection acquired by the `Dal` instance is initialized with a `statement_timeout` (default 30s) [src/dal/dal.ts:66-69]().
2.  **Fail-Fast Connection Acquisition**: The `connectionTimeoutMillis` (default 5s) ensures that if the pool is exhausted, the application errors quickly rather than queuing requests indefinitely [src/dal/dal.ts:71-73]().
3.  **Transaction-Scoped Overrides**: For bulk operations, the DAL uses `SET LOCAL statement_timeout` within a transaction, ensuring the setting automatically reverts on `COMMIT` or `ROLLBACK` [src/dal/dal.ts:23-26]().
4.  **Client-Scoped Overrides**: The `withClient` pattern allows manual overrides for ad-hoc long queries, with a guaranteed reset to the session default upon releasing the client back to the pool [src/dal/dal.ts:27-28]().

### Timeout Measurement
The `statement_timeout` measures the **full wall-clock time** from the moment the command arrives at the server until the server completes the command and transmits all result rows back to the client [docs/ai/dal-usage-guide.md:124-126]().

**Sources:** [src/dal/dal.ts:15-30](), [src/dal/dal.ts:66-73](), [docs/ai/dal-usage-guide.md:124-126]()

---

## The `withClient` Pattern

The `withClient(fn, options)` method is the primary tool for manual transaction management and per-call timeout overrides [src/dal/dal.ts:251-253]().

### Implementation and Data Flow
When `withClient` is called:
1.  A `PoolClient` is acquired from the `pg.Pool` [src/dal/dal.ts:258]().
2.  If `timeoutMs` is provided, the DAL executes `SET statement_timeout TO <ms>` on that specific client [src/dal/dal.ts:262-264]().
3.  The provided function `fn(client)` is executed [src/dal/dal.ts:267]().
4.  **Leakage Prevention**: In the `finally` block, if a custom timeout was set, it is reset to the global `config.statementTimeoutMs` before the client is released back to the pool [src/dal/dal.ts:270-272]().

### Code Entity Interaction
The following diagram illustrates how `withClient` manages the lifecycle of a `PoolClient` and its settings.

**Client Lifecycle and Timeout Reset**
```mermaid
sequenceDiagram
    participant App as "Consumer Code"
    participant Dal as "Dal Instance"
    participant Pool as "pg.Pool"
    participant Client as "pg.PoolClient"

    App->>Dal: withClient(fn, { timeoutMs: 5000 })
    Dal->>Pool: connect()
    Pool-->>Dal: Client
    Dal->>Client: query("SET statement_timeout TO 5000")
    
    Dal->>App: fn(Client)
    Note over App: Perform manual queries<br/>or Repository(Client) ops
    App-->>Dal: Result / Error
    
    Dal->>Client: query("SET statement_timeout TO 30000")
    Note right of Client: Reset to session default
    Dal->>Client: release()
    Dal-->>App: Final Result
```
**Sources:** [src/dal/dal.ts:251-280](), [test/dal-timeout.test.ts:157-179]()

---

## Per-Call Overrides in Bulk Operations

Bulk operations (`addMany`, `upsertMany`, `updateMany`) accept a `timeoutMs` in their options [src/types/types.ts:168-171](). Unlike `withClient`, these methods use `SET LOCAL` within a transaction block [src/dal/dal.ts:23-26]().

### Transactional Scoping
Because bulk operations are wrapped in a transaction, the DAL issues `SET LOCAL statement_timeout`. This PostgreSQL command ensures the timeout is only active for the duration of the current transaction. This provides a second layer of safety against timeout leakage, as the database engine itself reverts the setting upon transaction termination [src/dal/dal.ts:23-26]().

| Method | Override Mechanism | Scope |
| :--- | :--- | :--- |
| `addMany` | `SET LOCAL statement_timeout` | Transaction |
| `upsertMany` | `SET LOCAL statement_timeout` | Transaction |
| `updateMany` | `SET LOCAL statement_timeout` | Transaction |
| `withClient` | `SET statement_timeout` + Manual Reset | Connection Session |

**Sources:** [src/dal/dal.ts:23-30](), [src/types/types.ts:168-171](), [test/dal-timeout.test.ts:31-35]()

---

## Configuration Defaults

The `Dal` gateway applies these defaults unless overridden during initialization via `DalConfig` [src/dal/dal.ts:93-99]().

| Property | Code Constant | Default Value | Description |
| :--- | :--- | :--- | :--- |
| `statementTimeoutMs` | `DEFAULTS.statementTimeoutMs` | `30000` (30s) | Max duration for a single SQL command execution. |
| `connectionTimeoutMillis` | `DEFAULTS.connectionTimeoutMillis` | `5000` (5s) | Max time to wait for a free connection from the pool. |
| `max` | `DEFAULTS.max` | `10` | Maximum number of concurrent connections in the pool. |

**Sources:** [src/dal/dal.ts:54-84](), [src/dal/dal.ts:93-99]()

---

## Technical Flow: Connection Initialization

When a new connection is created (the `onConnect` event), the DAL applies the base session configuration [src/dal/dal.ts:147-163]().

**Entity Mapping: Configuration to SQL Commands**
```mermaid
graph TD
    subgraph "DalConfig (JS Space)"
        SC["schema"]
        ST["statementTimeoutMs"]
        AN["applicationName"]
    end

    subgraph "PostgreSQL Session (SQL Space)"
        SP["SET search_path TO ..."]
        STT["SET statement_timeout TO ..."]
        SAN["SET application_name TO ..."]
    end

    SC -->|quoteIdent| SP
    ST -->|numeric value| STT
    AN -->|escaped string| SAN

    subgraph "dal.ts Entities"
        OC["onConnectHandler"]
        DAL["Dal Class"]
    end

    OC -.-> SP
    OC -.-> STT
    OC -.-> SAN
    DAL --> OC
```
**Sources:** [src/dal/dal.ts:147-164](), [src/dal/dal.ts:116-125]()

---