horizondb-idiosyncrasies

# HorizonDB Idiosyncrasies Azure HorizonDB is a managed, preview Postgres-17 service (a.k.a. OrionDB internally). It bundles AI/graph/vector/durable-execution extensions but locks them behind an allow-list and several managed-PG restrictions that differ from vanilla Postgres. This skill captures the verified facts and the workarounds. > Reference implementation: `incubator/horizon-facts/` — a HorizonDB-only > enhanced facts interface. Its integration suite (`test/integration/`) runs > live against a HorizonDB cluster and exercises every capability below. ## Quick reference | Symptom | Cause | Fix | | --- | --- | --- | | `self-signed certificate in certificate chain` | pg v8 treats `sslmode=require` as `verify-full` | append `uselibpqcompat=true` to the connection string | | `extension "X" is not allow-listed for "azure_pg_admin" users` | empty/insufficient `azure.extensions` on the parameter group | add X to `azure.extensions` on a custom parameter group, attach to cluster | | `access to library "age" is not allowed` | `age` is in `shared_preload_libraries`; explicit `LOAD 'age'` is forbidden | tolerate that error — the library is already preloaded | | `syntax error at or near "WHERE"` from `cypher_parser.c` | AGE doesn't support `any(x IN list WHERE pred)` | use `size([x IN list WHERE pred]) > 0` | | `schema "..." already exists` creating an AGE graph | graph name collides with an existing schema (`create_graph` makes a schema) | give the graph a name distinct from every other schema | | `relation "<label>" already exists` (`42P07`) **or** `duplicate key … "pg_class_relname_nsp_index"` (`23505`) under concurrent graph writes | AGE creates a label's backing table lazily on first `CREATE`/`MERGE`; concurrent first-references race the DDL (two detection points) | re-run the same idempotent statement — the label now exists (see §3 concurrency) | | `cannot cast type agtype to jsonb` | `agtype` has no jsonb cast | `RETURN` scalars/`label()`/`type()`, parse client-side | | `ECONNRESET` / `Connection terminated unexpectedly` mid-workload | HorizonDB resets idle pooled TLS connections | guard + bounded retry on a **fresh** connection (§1.1) | | AGE writes are 600 ms–2 s each under concurrency | small connection pool → ops queue (connection-queue wait, not query cost) | raise `poolMax` for the parallelism (default now 16, `HORIZON_POOL_MAX`); per-connection AGE setup; fewer round-trips per upsert (§3 performance) | ## 1. Connection & TLS HorizonDB requires SSL. The cluster's certificate chain is **not** in Node's default trust store, and modern `pg` (v8+) / `pg-connection-string` treat `sslmode=require` as `verify-full`, so a plain `?sslmode=require` URL fails with: ``` CONN FAIL: self-signed certificate in certificate chain ``` **Fix:** append `uselibpqcompat=true`, which restores libpq semantics where `require` means *encrypt but don't verify the CA*: ``` postgresql://USER:PW@<cluster>.<id>.<region>.horizondb.azure.com:5432/postgres?sslmode=require&uselibpqcompat=true ``` For production trust verification, install the Azure CA bundle and use `sslmode=verify-full` with an explicit `ca`. For dev/test against preview clusters, `uselibpqcompat=true` is the pragmatic path. The horizon-facts test harness normalizes this automatically — see `normalizeDbUrl()` in `incubator/horizon-facts/test/integration/_db.mjs`. ### 1.1 Idle pooled connections get reset — guard + retry on a fresh connection HorizonDB (preview) intermittently drops **idle pooled TLS connections**. Under a sustained workload this surfaces two ways: - an **uncaught** `error` event on a checked-out client (`ECONNRESET`) — this crashes a long-running Node process unless you attach a pool/client `error` handler; - a **rejected** query (`ENOTCONN` / `EPIPE` / `Connection terminated unexpectedly`) from `pool.query`. Neither partially commits, so re-running on a **fresh** connection is safe. Pattern (see `incubator/horizon-facts/src/db-retry.ts`): - `pool.on("error", …)` and a process-level guard that swallow **only** this transient socket class; - a bounded retry (`withDbRetry`) that re-acquires a connection and re-runs. **Keep two retry classifiers distinct** — they target different recovery actions: | Classifier | Matches | Retry action | | --- | --- | --- | | `isTransientDbError` | `ECONNRESET`/`ENOTCONN`/`EPIPE`/`Connection terminated` + pg `08xxx`/`57P0x` | re-run on a **fresh** connection | | `isLabelCreationRaceError` | `42P07` **or** `23505` on a `pg_catalog` index (`pg_class_*`/`pg_type_*`/`pg_namespace_*`) | re-run the **same** statement (AGE label race, §3) | Do **not** lump the label race into the connection regex, and do **not** retry a bare `23505` — a unique violation on a *user* constraint is a real error; only the **system-catalog** index variant is the label race. ## 2. Extension allow-list (the #1 blocker) HorizonDB rejects `CREATE EXTENSION` unless the extension name is in the `azure.extensions` server parameter, even for the admin user: ``` ERROR: extension "vector" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL (SQLSTATE 0A000) ``` The **default parameter group** (`default_pg17`) ships with `azure.extensions` **empty** — only `plpgsql` is installed. The extensions exist on the image (check `pg_available_extensions`), they're just not allowed yet. ### Available AI/graph extensions (PG17 image, verified 2026-06) | Extension | Version | Purpose | | --- | --- | --- | | `vector` (pgvector) | 0.8.0 | embedding storage + HNSW ANN | | `age` (Apache AGE) | 1.6.0 | property graph + Cypher | | `pg_durable` | 0.2.1 | durable execution + `df.http()` for in-DB HTTP | | `pg_textsearch` | 1.3.0-dev | ranked lexical search | | `azure_ai` | 2.2.1 | native in-DB model inference (Azure-managed) | | `pg_diskann` | 0.7.1 | disk-based ANN index (azure.extensions ONLY — NOT preloadable) | ### Enabling extensions **Parameter groups are immutable.** Updating an existing group is rejected with `ParameterGroupUpdateNotSupported`, including changes to `max_connections`. Create a new group under a distinct name with the complete desired parameters, then attach it to the existing cluster with `applyImmediately: true` and wait for `InSync`. Retrying provisioning should reuse the revisioned group rather than PUT an update. CI uses a deterministic settings-revision suffix. In the Microsoft repository, perform these mutations through GitHub Actions. You **cannot** set `azure.extensions` per-session. You must create a custom **parameter group**, set the allow-list (and `shared_preload_libraries` for preload-required extensions), and attach it to the cluster. Attaching triggers a **cluster restart** (~2 min downtime) when `shared_preload_libraries` changes. **Preload requirements:** `age` and `pg_durable` must be in `shared_preload_libraries`. `vector` and `pg_textsearch` only need to be in `azure.extensions`. `pg_diskann` is `azure.extensions`-ONLY — it is **rejected** in `shared_preload_libraries` (HorizonDB's allowed preload set is fixed and does not include it), yet works without preloading. #### Via ARM (az rest) ```bash SUB=<subscription-id>; RG=<resource-group>; API=2026-01-20-preview CLUSTER=<cluster-name>; PG=<cluster>-ext # 1. Create the parameter group (sparse list merges into ~426 defaults). # NOTE: top-level "location" is REQUIRED or you get LocationRequired. cat > /tmp/pg-body.json <<EOF { "location": "<region>", "properties": { "description": "Enable AI/graph extensions", "pgVersion": "17", "parameters": [ { "name": "azure.extensions", "value": "age,azure_ai,pg_durable,pg_textsearch,vector" }, { "name": "shared_preload_libraries", "value": "age,pg_durable,pg_textsearch" } ] } } EOF az rest --method put \ --url "https://management.azure.com/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.HorizonDb/parameterGroups/$PG?api-version=$API" \ --headers "Content-Type=application/json" --body @/tmp/pg-body.json # 2. Poll until properties.provisioningState == "Succeeded". # 3. Attach to the cluster (triggers restart). PATCH only the parameterGroup ref. cat > /tmp/cluster-patch.json <<EOF { "properties": { "parameterGroup": { "id": "/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.HorizonDb/parameterGroups/$PG" } } } EOF az rest --method patch \ --url "https://management.azure.com/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.HorizonDb/clusters/$CLUSTER?api-version=$API" \ --headers "Content-Type=application/json" --body @/tmp/cluster-patch.json # 4. Poll the cluster: properties.state goes Updating -> Succeeded. # (provisioningState may be empty in steady state; key off `state`.) ``` Notes: - The platform **re-injects its own system preload libs** (`azure`, `orion_storage`, `pg_availability`, `pg_qs`, `pgms_stats`, `pgms_wait_sampling`) on top of yours — you only list the extras. - The default group `default_pg17` is **not** an ARM resource (GET returns ResourceNotFound). Discover existing user groups with: `az graph query -q "resources | where type =~ 'microsoft.horizondb/parametergroups'"`. - A good template to clone from is any working group that already enables these (e.g. a `fts-age-pgts` style group in the same subscription). ### Verify after restart ```js await pool.query(`show shared_preload_libraries`); // includes age,pg_durable,pg_textsearch await pool.query(`CREATE EXTENSION IF NOT EXISTS vector`); await pool.query(`CREATE EXTENSION IF NOT EXISTS age`); await pool.query(`CREATE EXTENSION IF NOT EXISTS pg_durable`); // df.http() should now exist: await pool.query(`select proname from pg_proc where proname='http' and pronamespace::regnamespace::text='df'`); ``` ## 3. Apache AGE quirks ### Don't `LOAD 'age'` when it's preloaded On vanilla Postgres you must `LOAD 'age'` per session. On HorizonDB `age` is in `shared_preload_libraries`, so it's already loaded and an explicit `LOAD` is **rejected**: ``` ERROR: access to library "age" is not allowed ``` Make the load tolerant — attempt it (for portability to vanilla PG) but swallow that one error: ```ts async function loadAge(client: any): Promise<void> { try { await client.query(`LOAD 'age'`); } catch (err: any) { if (!/access to library "age" is not allowed/i.test(String(err?.message ?? ""))) throw err; // already preloaded via shared_preload_libraries — nothing to do } } ``` Still run `SET search_path = ag_catalog, "$user", public` on every AGE session. ### Graph name must differ from every schema name `SELECT create_graph('g')` creates a **Postgres schema** named `g`. If you also have a facts/data schema named `g`, you get `schema "g" already exists`. Always use distinct names (e.g. data schema `hf_test_<r>`, graph `hf_graph_<r>`). ### Cypher: no `any(... WHERE ...)` AGE 1.6.0's Cypher parser rejects the list predicate `any(x IN list WHERE pred)` with `syntax error at or near "WHERE"` (`cypher_parser.c`). Use the list-comprehension + `size()` form instead: ```cypher -- BROKEN on AGE: WHERE any(a IN e.aliases WHERE toLower(a) CONTAINS 'tgl') -- WORKS: WHERE size([a IN e.aliases WHERE toLower(a) CONTAINS 'tgl']) > 0 ``` `UNWIND ... WITH x WHERE ...` also works. When in doubt, probe syntax against a throwaway graph before committing to a query shape. ### Other AGE limitations to design around - Avoid `startNode()` / `endNode()` / `UNWIND` over variable-length paths. For neighbourhood edges, collect node keys first, then match edges with `WHERE a.entity_key IN [...] AND b.entity_key IN [...]`. - Cypher cannot be parameterized through pg bind params — values are inlined into the `cypher('graph', $$ ... $$)` literal. Use a strict escaping helper (see `cypherStr` / `cypherStrList` / `cypherNum` in horizon-facts `sql-util.ts`) and never interpolate untrusted text without it. - `agtype` has **no cast to `jsonb`** (`cannot cast type agtype to jsonb`). Don't return a whole node/edge and cast it — `RETURN` the scalars you need, or `label(x)` / `type(r)`, and parse the agtype scalars client-side. ### AGE concurrency: lazy label creation races (SQLSTATE 42P07) AGE creates a label's backing **table** *lazily* — on the first `CREATE`/`MERGE` that references a label that doesn't exist yet. That internal check-then-`CREATE TABLE` is **not atomic and not serialized**. Any concurrent writer (a connection-per-call pool, or an LLM agent issuing parallel tool calls) that races the *first* reference to the same label hits the collision in **one of two forms**, depending on where the duplicate is detected: ``` relation "GraphNode" already exists -- 42P07 duplicate_table duplicate key value violates unique constraint "pg_class_relname_nsp_index" -- 23505 unique_violation ``` The `42P07` form is caught at the relation level; the `23505` form fires when two `CREATE TABLE` for the same label race into the **`pg_class` system catalog** before either registered. **Both are the same race** and both **abort the whole statement** (the MERGE/CREATE persisted nothing). Errors are **front-loaded** — they cluster on the first touch of each label, then disappear once the backing tables exist. > ⚠️ The `23505` form is easy to miss: the "already exists" text is in > `err.detail`, **not** `err.message` (the message is "duplicate key value …"). > A message-only regex will silently let it through. Key off the SQLSTATE + > the catalog constraint name (`pg_class_*` / `pg_type_*` / `pg_namespace_*`).