Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
15.6 KB
Raw Blame
/**
 * GST-2260: a prepared `SELECT *` or `RETURNING *` goes stale when another process adds, renames or drops a column on
 * its table, and Postgres answers its next execute with SQLSTATE 0A000 "cached plan must not change result type"
 * (routine RevalidateCachedQuery). The vendored driver re-prepares and retries such a statement, which heals it outside
 * a transaction; inside one the 0A000 has already aborted the transaction, so the caller saw it, once per statement shape
 * per pooled connection (a `gbrain serve` refusing writes after `apply-migrations` ran in another process).
 * 1. Protects: the first such error heals every pooled connection of the process, and the write paths that re-run an
 *    aborted transaction (retryWriteAdmission, the coordinator's transient classifier) re-run it, so a put_page after
 *    the migration succeeds with no caller-visible error.
 * 2. Fails when: the error is not retried by the admission loop, or another pooled connection still holds its stale
 *    statement after the first one healed.
 * 3. Existing coverage: none; test/access-ungranted-member-writes.test.ts flaked on it (a RENAME COLUMN and back).
 *    The two pure-database transactions outside those loops that a long-running process meets first (an OAuth token
 *    grant, a minion job's completion) run once more instead of failing a token request or a job that already ran.
 * 4. Real seams: real Postgres, DDL from a second client, the engine's transaction and executeRaw, MCP dispatch, the
 *    OAuth provider and the minion queue.
 */
import { afterAll, beforeAll, describe, expect, test } from 'bun:test';
import { randomUUID } from 'node:crypto';
import { readFileSync } from 'node:fs';
import net from 'node:net';
import type { AddressInfo } from 'node:net';
import postgres from '#postgres';
import { PostgresEngine } from '../src/core/postgres-engine.ts';
import { retryWriteAdmission } from '../src/core/persistence/admission-retry.ts';
import { transientDatabaseFailure } from '../src/core/persistence/coordinator.ts';
import { GBrainOAuthProvider } from '../src/core/oauth-provider.ts';
import { sqlQueryForEngine } from '../src/core/sql-query.ts';
import { mintLegacyToken } from '../src/core/token-mint.ts';
import { dispatchToolCall } from '../src/mcp/dispatch.ts';
import { disposePersistenceConsumer } from '../src/core/persistence/service.ts';
import { MinionQueue } from '../src/core/minions/queue.ts';
import { hashToken } from '../src/core/utils.ts';
import type { AuthInfo } from '../src/core/ops/contract.ts';
import { assertSafeE2eDatabaseUrl } from './helpers/db-guard.ts';
import { isolatedPersistencePostgres } from './helpers/persistence-postgres.ts';

const url = process.env.DATABASE_URL;
const caught = (p: Promise<unknown>) => p.then(() => null, (e: { code?: string; routine?: string }) => e);
const outcomes = (settled: PromiseSettledResult<unknown>[]) => settled.map(s => s.status === 'fulfilled' ? 'ok' : (s.reason as { code?: string }).code);

/** Each change alters the result type of `SELECT *` on the table; `step` counts the changes already made to it. */
const CHANGES = [
  ['ADD COLUMN', (t: string) => `ALTER TABLE ${t} ADD COLUMN added_${randomUUID().slice(0, 8)} text`],
  ['RENAME COLUMN', (t: string, step: number) => step % 2 ? `ALTER TABLE ${t} RENAME COLUMN note_alt TO note` : `ALTER TABLE ${t} RENAME COLUMN note TO note_alt`],
  ['DROP COLUMN', (t: string, step: number) => `ALTER TABLE ${t} DROP COLUMN ${step ? 'note' : 'spare'}`],
] as const;

describe('write admission and a stale plan that keeps coming back (GST-2260)', () => {
  test('another 0A000 is not a stale plan and is not retried', async () => {
    const unsupported = Object.assign(new Error('FOR SHARE is not allowed with aggregate functions'), { code: '0A000', routine: 'CheckSelectLocking' });
    let attempts = 0;
    expect(await caught(retryWriteAdmission(randomUUID(), async () => { attempts++; throw unsupported; }, 5000))).toBe(unsupported);
    expect(attempts).toBe(1);
  });
});

describe('every vendored driver copy carries the plan-generation heal (GST-2260)', () => {
  for (const path of ['vendor/postgres/src/connection.js', 'vendor/postgres/cjs/src/connection.js', 'vendor/postgres/cf/src/connection.js']) {
    test(`${path} starts a new plan generation on a stale cached plan`, () => {
      const source = readFileSync(new URL(`../${path}`, import.meta.url), 'utf8');
      expect(source).toContain('let planGeneration = 0');
      expect(source).toContain("errorResponse.routine === 'RevalidateCachedQuery' && planGeneration++");
    });
  }
});

describe.skipIf(!url)('a prepared SELECT * after another connection changes its table (GST-2260)', () => {
  let admin: ReturnType<typeof postgres>;
  const engines: PostgresEngine[] = [];
  beforeAll(async () => {
    assertSafeE2eDatabaseUrl(url!);
    admin = postgres(url!, { max: 1, prepare: false, onnotice: () => {} });
  });
  afterAll(async () => {
    for (const engine of engines) await engine.disconnect().catch(() => {});
    await admin.end({ timeout: 1 });
  });

  async function setup(poolSize: number, change: (t: string, step: number) => string) {
    const table = 'stale_plan_' + randomUUID().replace(/-/g, '').slice(0, 12);
    await admin.unsafe(`CREATE TABLE ${table} (id int PRIMARY KEY, scopes text[], note text, spare text); INSERT INTO ${table} VALUES (1, '{read}', 'n', 's')`);
    const engine = new PostgresEngine();
    engines.push(engine);
    await engine.connect({ database_url: url!, poolSize });
    const select = `SELECT * FROM ${table} WHERE id=$1 FOR SHARE`;
    let step = 0;
    return {
      engine, select,
      inTx: () => engine.transaction(tx => tx.executeRaw<{ id: number }>(select, [1])),
      alter: () => admin.unsafe(change(table, step++)),
    };
  }

  /** Prepares the statement on every pooled connection: `n` transactions hold their connections at once. */
  async function primeEvery(engine: PostgresEngine, select: string, n: number) {
    let arrived = 0;
    let open!: () => void;
    const barrier = new Promise<void>(resolve => { open = resolve; });
    await Promise.all(Array.from({ length: n }, () => engine.transaction(async tx => {
      await tx.executeRaw(select, [1]);
      if (++arrived === n) open();
      await barrier;
    })));
  }

  test('a prepare: false pool (what a transaction-mode pooler URL gets) sends unnamed statements and is unchanged', async () => {
    const { select, alter, inTx: preparedInTx } = await setup(1, CHANGES[0][1]);
    const unnamed = postgres(url!, { max: 1, prepare: false, onnotice: () => {} });
    try {
      const inTx = () => unnamed.begin(tx => tx.unsafe(select, [1]));
      expect((await preparedInTx())[0]!.id).toBe(1);
      for (let i = 0; i < 2; i++) {
        expect(Array.from(await inTx() as Iterable<{ id: number }>)[0]!.id).toBe(1);
        await alter();
      }
      expect(await caught(preparedInTx())).toMatchObject({ code: '0A000' });
      expect(Array.from(await inTx() as Iterable<{ id: number }>)[0]!.id).toBe(1);
    } finally { await unnamed.end({ timeout: 1 }); }
  });

  /** A TCP relay to the database that can hold the server's answers back, to deliver one connection's reply late. */
  async function relay() {
    const target = new URL(url!);
    let held: Array<string | Buffer> | null = null;
    let client: net.Socket | null = null;
    const sockets: net.Socket[] = [];
    const server = net.createServer(c => {
      client = c;
      const s = net.connect(Number(target.port || 5432), target.hostname);
      sockets.push(c, s);
      c.on('data', d => s.write(d));
      s.on('data', d => { if (held) held.push(d); else c.write(d); });
      c.on('close', () => s.destroy()); s.on('close', () => c.destroy());
      c.on('error', () => {}); s.on('error', () => {});
    });
    const port = await new Promise<number>(resolve => server.listen(0, '127.0.0.1', () => resolve((server.address() as AddressInfo).port)));
    const relayed = new URL(url!); relayed.hostname = '127.0.0.1'; relayed.port = String(port);
    return {
      url: relayed.toString(),
      hold() { held = []; },
      release() { const out = held ?? []; held = null; for (const d of out) client!.write(d); },
      close() { for (const s of sockets) s.destroy(); server.close(); },
    };
  }

  test('a statement described before the change but answered after the pool healed is not kept as prepared', async () => {
    const { select, alter } = await setup(1, CHANGES[0][1]);
    const types = new Map();
    const late = await relay();
    const a = postgres(url!, { max: 1, shared_types: types, onnotice: () => {} });
    const b = postgres(late.url, { max: 1, shared_types: types, onnotice: () => {} });
    try {
      expect(Array.from(await a.unsafe(select, [1], { prepare: true }) as Iterable<{ id: number }>)[0]!.id).toBe(1);
      await b.unsafe('SELECT 1');
      late.hold();
      const described = b.unsafe(select, [1], { prepare: true }).execute();
      await new Promise(resolve => setTimeout(resolve, 300));
      await alter();
      expect(Array.from(await a.unsafe(select, [1], { prepare: true }) as Iterable<{ id: number }>)[0]!.id).toBe(1);
      const unrelated = b.unsafe('SELECT 2 AS n').execute();
      late.release();
      await described;
      await unrelated;
      const inTx = await caught(b.begin(tx => tx.unsafe(select, [1], { prepare: true })));
      expect(inTx).toBeNull();
    } finally {
      await a.end({ timeout: 1 });
      await b.end({ timeout: 1 });
      late.close();
    }
  });

  for (const [name, change] of CHANGES) {
    test(`${name}: the in-transaction stale-plan error is retryable and the admission loop re-runs it with no caller-visible error`, async () => {
      const { inTx, alter } = await setup(1, change);
      expect((await inTx())[0]!.id).toBe(1);
      await alter();
      expect((await retryWriteAdmission(randomUUID(), () => inTx(), 5000))[0]!.id).toBe(1);
      await alter();
      const error = await caught(inTx());
      expect(error).toMatchObject({ code: '0A000', routine: 'RevalidateCachedQuery' });
      expect(transientDatabaseFailure(error)).toBe(true);
      expect((await inTx())[0]!.id).toBe(1);
    });

    test(`${name}: after one pooled connection healed, the other pooled connections do not fail`, async () => {
      const { engine, select, inTx, alter } = await setup(3, change);
      await primeEvery(engine, select, 3);
      await alter();
      expect((await engine.executeRaw<{ id: number }>(select, [1]))[0]!.id).toBe(1);
      expect(outcomes(await Promise.allSettled([inTx(), inTx(), inTx()]))).toEqual(['ok', 'ok', 'ok']);
      await primeEvery(engine, select, 3);
      await alter();
      expect(await caught(inTx())).toMatchObject({ code: '0A000', routine: 'RevalidateCachedQuery' });
      expect(outcomes(await Promise.allSettled([inTx(), inTx(), inTx()]))).toEqual(['ok', 'ok', 'ok']);
    });
  }
});

describe.skipIf(!url)('put_page after another connection adds a column to access_tokens (GST-2260)', () => {
  let engine: PostgresEngine;
  let close: () => Promise<void>;
  let databaseUrl: string;
  let token: string;
  beforeAll(async () => {
    ({ engine, close, databaseUrl } = await isolatedPersistencePostgres(url!));
    token = (await mintLegacyToken(engine, { name: 'writer-example', scopes: ['read', 'write'], takesHolders: ['world'] })).token;
  }, 240_000);
  afterAll(async () => { if (engine) await disposePersistenceConsumer(engine); await close?.(); }, 60_000);

  async function verify() {
    const provider = new GBrainOAuthProvider({ sql: sqlQueryForEngine(engine), transaction: fn => engine.transaction(tx => fn(sqlQueryForEngine(tx))) });
    return await provider.verifyAccessToken(token) as unknown as AuthInfo;
  }
  async function put(slug: string, auth: AuthInfo) {
    const out = await dispatchToolCall(engine, 'put_page', { slug, content: ['---', 'type: note', 'title: Stale plan', '---', '# Stale plan', '', 'Body ' + slug + '.'].join('\n'), request_id: randomUUID() },
      { remote: true, transport: 'http', auth, sourceId: auth.sourceId ?? 'default', takesHoldersAllowList: ['world'] });
    return { isError: out.isError === true, text: out.content.map(c => c.text).join('\n') };
  }

  /**
   * Several writes in a row, so the token read is prepared on most of the pool's connections before the column changes.
   * The token is verified once, before the change, so the first statement that meets the stale plan is the write's own
   * token read inside its admission transaction (a verify outside a transaction would heal the pool first).
   */
  test('a column added between writes leaves every later write with no error', async () => {
    const auth = await verify();
    for (let i = 0; i < 4; i++) expect(await put('notes/stale-plan-before-' + i, auth)).toMatchObject({ isError: false });
    const other = postgres(databaseUrl, { max: 1, prepare: false, onnotice: () => {} });
    try { await other.unsafe(`ALTER TABLE access_tokens ADD COLUMN stale_plan_probe_${randomUUID().slice(0, 8)} text`); }
    finally { await other.end({ timeout: 1 }); }
    const after: Array<{ isError: boolean; text: string }> = [];
    for (let i = 0; i < 4; i++) after.push(await put('notes/stale-plan-after-' + i, auth));
    expect(after.filter(out => out.isError).map(out => out.text)).toEqual([]);
    const [row] = await engine.executeRaw<{ n: number }>("SELECT count(*)::int AS n FROM pages WHERE slug LIKE 'notes/stale-plan-%' AND deleted_at IS NULL");
    expect(row!.n).toBe(8);
  }, 240_000);
});

describe.skipIf(!url)('pure-database transactions outside the write paths after another connection adds a column (GST-2260)', () => {
  let engine: PostgresEngine;
  let close: () => Promise<void>;
  let databaseUrl: string;
  beforeAll(async () => { ({ engine, close, databaseUrl } = await isolatedPersistencePostgres(url!, 'instance', 1)); }, 240_000);
  afterAll(async () => { await close?.(); }, 60_000);
  async function addColumn(table: string) {
    const other = postgres(databaseUrl, { max: 1, prepare: false, onnotice: () => {} });
    try { await other.unsafe(`ALTER TABLE ${table} ADD COLUMN stale_plan_probe_${randomUUID().slice(0, 8)} text`); }
    finally { await other.end({ timeout: 1 }); }
  }

  test('an OAuth client-credentials grant whose locked client read meets the stale plan still issues a token', async () => {
    const secret = 'stale-plan-secret-' + randomUUID();
    await engine.executeRaw(`INSERT INTO oauth_clients (client_id, client_secret_hash, client_name, redirect_uris, grant_types, scope, token_endpoint_auth_method,
      client_id_issued_at) VALUES ('stale-plan-client', $1, 'stale-plan-client', '{}', '{client_credentials}', 'read write', 'client_secret_post', 0)`, [hashToken(secret)]);
    const provider = new GBrainOAuthProvider({ sql: sqlQueryForEngine(engine), transaction: fn => engine.transaction(tx => fn(sqlQueryForEngine(tx))) });
    expect((await provider.exchangeClientCredentials('stale-plan-client', secret, 'read')).access_token).toStartWith('gbrain_at_');
    await addColumn('oauth_clients');
    expect((await provider.exchangeClientCredentials('stale-plan-client', secret, 'read')).access_token).toStartWith('gbrain_at_');
  }, 60_000);

  test('a minion job whose completion meets the stale plan is completed, not failed after it ran', async () => {
    const queue = new MinionQueue(engine);
    const run = async (token: string, before?: () => Promise<void>) => {
      await queue.add('sync', { probe: token });
      const claimed = await queue.claim(token, 30_000, 'default', ['sync']);
      expect(claimed).not.toBeNull();
      await before?.();
      return queue.completeJob(claimed!.id, token, { done: token });
    };
    expect((await run('stale-plan-first'))?.status).toBe('completed');
    expect((await run('stale-plan-second', () => addColumn('minion_jobs')))?.status).toBe('completed');
  }, 60_000);
});