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 it to the cluster's pool | | `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 You **cannot** set `azure.extensions` per-session. You must create a custom **parameter group** (settings group), set the allow-list (and `shared_preload_libraries` for preload-required extensions), and attach it to the cluster's **pool**. Attaching restarts the server once (about 10 s down) 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. #### API 2026-10-01-preview: pools own compute and the settings group The deploy module (`deploy/providers/azure/services/horizondb/bicep/main.bicep` plus `ensureHorizonDbParameterGroup` in `deploy/scripts/lib/horizondb.mjs`) uses API `2026-10-01-preview`. Its rules, each found by a failed deploy: | Rule | What happens otherwise | | --- | --- | | Compute (`computeModel`, `nodeCount`) and `parameterGroup` live on `clusters/{c}/pools/{pool}`, not on the cluster | the cluster PUT ignores them | | A new pool accepts only the built-in group `/subscriptions/{s}/providers/Microsoft.HorizonDb/locations/{region}/parameterGroups/default_pg17`; attach the custom group with a pool PATCH afterwards | pool create is rejected | | `clusters/{c}/authentications/passwordMethod` needs an existing pool | `Pool 'DefaultPool' was not found` | | `administratorLogin` can be sent once; later PUTs send only the password (a reset) | `administratorLogin cannot be specified because an administrator is already configured` | | PUT the settings group before the cluster, not in parallel: a PUT to a group that a pool uses keeps the cluster `Updating` for about a minute | cluster PUT fails with `ClusterNotReady` | | Read the host name from `clusters/{c}/endpoints/{c}` (role ReadWrite). New clusters use `{c}.rw.{id}.{region}.horizondb.azure.com` | the old API's `fullyQualifiedDomainName` does not resolve for new clusters | | One pool request may change only one property group (`nodeCount` + `highAvailability`, or `parameterGroup`). Pools of clusters migrated from the old API can show no `highAvailability` and no group, so a full pool PUT changes two groups. The deploy module PUTs the pool only to create it | `The request changes multiple Pool property groups ... Submit a separate request for each property group` | | Settings groups can be updated (PUT/PATCH); connected pools pick up the change | (API 2026-01-20-preview rejected updates with `ParameterGroupUpdateNotSupported`) | **Attach timing.** A pool PATCH takes about two minutes. During that time the pool GET still shows the **previous** group as `Succeeded` / `InSync`; the server restarts about a minute in, and the GET shows the new group only when the operation ends. Send **one** PATCH, then wait until the GET shows the new group with `syncStatus: InSync` (or follow the `Azure-AsyncOperation` URL). **The first PATCH soon after a pool is created can be dropped:** it ends `Succeeded`, no restart happens, and the pool stays on the built-in group. Seen twice, each time within a minute of the pool's create and login writes. A second PATCH then attached normally. `ensureHorizonDbParameterGroup` resends the PATCH when the GET still shows another group four minutes later (at most three PATCHes). `syncStatus` values: `Applying`, `InSync`, `PendingReboot` (static settings wait for `POST .../pools/{pool}/restart`), `FailedToApply`. With `applyMethod: Immediate` the service applied `shared_preload_libraries` without a manual restart in testing; the deploy still handles `PendingReboot`. #### Via ARM (az rest) ```bash SUB=<subscription-id>; RG=<resource-group>; API=2026-10-01-preview CLUSTER=<cluster-name>; PG=<cluster>-ext; REGION=<region> # 1. Create or update the settings group. Only the listed values are stored # as overrides (valueSource UserOverride); the rest are service defaults. cat > /tmp/pg-body.json <<EOF { "location": "$REGION", "properties": { "description": "Enable AI/graph extensions", "postgreSqlVersion": "17", "parameters": [ { "name": "azure.extensions", "value": "age,azure_ai,pg_diskann,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. Attach it to the pool (one restart). Then poll the pool GET until it # shows this group with syncStatus InSync (about two minutes). az rest --method patch \ --url "https://management.azure.com/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.HorizonDb/clusters/$CLUSTER/pools/DefaultPool?api-version=$API" \ --headers "Content-Type=application/json" \ --body "{\"properties\":{\"parameterGroup\":{\"parameterGroupResourceId\":\"/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.HorizonDb/parameterGroups/$PG\",\"applyMethod\":\"Immediate\"}}}" ``` Notes: - The platform **re-injects its own system preload libs** (`azure`, `orion_storage`, `pg_availability`, `pg_qs`, `pgms_stats`, `pgms_wait_sampling`, and on newer builds `pgaadauth`, `horizondb_ext`) on top of yours — you only list the extras. - List the built-in groups with `GET /subscriptions/{s}/providers/Microsoft.HorizonDb/locations/{region}/parameterGroups?api-version=2026-10-01-preview`. - Extension versions depend on the server build. Clusters created at different times can run different builds (for example `age` 1.7.0 on one and 1.8.0 on a newer one); check `pg_available_extensions` on the cluster you test. ### 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.