set_table_metadata returns TABLE_NOT_FOUND for tables created via execute on the ephemeral primary

[StefanSteiner]

Summary

set_table_metadata returns TABLE_NOT_FOUND for any table created via the execute tool on the default ephemeral primary — including CREATE TABLE ... AS SELECT and plain CREATE TABLE. The table exists and is fully queryable (describe/query/sample all see it), but no _table_catalog stub row is created, so the metadata write is rejected.

This contradicts both the set_table_metadata tool description ("Requires an existing catalog entry — load the table first (load_file / load_data / execute CREATE TABLE) so the stub row is created automatically") and the error message the tool itself returns ("the catalog is refreshed automatically on those paths").

Verified on v0.7.0 (hyper_rust_api_version: 0.7.0.r5fa668df).

Reproduction

# 1. Create a table via execute on the default (ephemeral) primary
execute(sql: ["CREATE TABLE demo AS SELECT 1 AS id, 'alpha' AS name"])
  → { "statements": 1, "affected_rows": 0, ... }        # success

# 2. Confirm the table exists and is queryable
describe(table: "demo")
  → { "tables": [{ "name": "demo", "columns": [...], "row_count": 1 }] }   # present

# 3. Try to stamp metadata
set_table_metadata(table: "demo", purpose: "…")
  → ERROR TABLE_NOT_FOUND:
    "No catalog entry for table 'demo'. Load the table first
     (load_file / load_data / execute CREATE TABLE) or create it and
     re-run; the catalog is refreshed automatically on those paths."

Plain CREATE TABLE demo (id INT) (no AS SELECT) reproduces identically — this is not CTAS-specific.

Root cause

The execute handler updates the catalog through a reconcile pass rather than an explicit stub, and that reconcile only enumerates the persistent database's tables — never the ephemeral primary's.

• execute → on structural DDL calls after_execute_catalog_update(engine, target_db) (hyperdb-mcp/src/server.rs:2648-2650).
• after_execute_catalog_update calls reconcile_in(engine, None) (hyperdb-mcp/src/server.rs:1458). target_db = None because the create targeted the default primary.
• reconcile_in discovers tables to stub via user_tables_in (hyperdb-mcp/src/table_catalog.rs:816-835), which queries "<persistent>".pg_catalog.pg_tables WHERE schemaname = 'public'.
• A table created on the ephemeral primary lives in the ephemeral .hyper file, not the persistent attachment, so it never appears in that pg_tables scan — reconcile never stubs it.

By contrast, the ingest tools (load_file / load_data / load_files) call after_ingest_catalog_update → upsert_stub_in with the explicit table name (hyperdb-mcp/src/server.rs:1425), which stubs correctly regardless of which DB the table lives in. That's why the documented "load the table first" path works but the "execute CREATE TABLE" path doesn't.

Confirming evidence

Creating the same table in the persistent DB stubs correctly and lets metadata through:

execute(sql: ["CREATE TABLE demo AS SELECT 1 AS id"], database: "persistent")
set_table_metadata(table: "demo", purpose: "…", database: "persistent")
  → { "table_name": "demo", "purpose": "…", "load_tool": "unknown", "row_count": 1, ... }   # success

load_tool: "unknown" confirms the row was created by the reconcile pass (not an ingest stub) — reconcile does fire and does work, it just only ever looks at the persistent catalog's DB.

Impact

The primary casualty is the common analytics pattern of building a derived table with CREATE TABLE analysis AS SELECT … on the default workspace and then stamping it with provenance (source_url, purpose, refresh recipe) for the next session — exactly what set_table_metadata is for. Today that requires either:

• targeting database: "persistent" explicitly (works, but changes durability semantics and isn't discoverable from the error), or
• round-tripping the table out to Parquet via export and back in via load_file purely to trigger the ingest-path stub.

Suggested fixes (in rough order of preference)

1. Reconcile the DB that was actually written. When execute targets the ephemeral primary (target_db = None), reconcile_in should enumerate the ephemeral primary's pg_tables, not (only) the persistent attachment's. The stub rows can still be stored in the persistent catalog (matching where ephemeral-primary ingest stubs already live per the table_catalog module docs) — it's the table-discovery scan that needs to target the ephemeral DB.
2. Parse created table names from the executed DDL and upsert_stub_in them explicitly, mirroring the ingest path, instead of relying on a pg_tables diff. More robust than reconcile for the create case and avoids the extra round-trips.
3. At minimum, fix the misleading docs + error message if the behavior is intended: the set_table_metadata description and the TABLE_NOT_FOUND suggestion should not claim "execute CREATE TABLE … refreshed automatically" for the default-primary case, and should point users at database: "persistent" or the load path.

Environment

• hyper_rust_api_version: 0.7.0.r5fa668df-dirty-20260712T003150Z
• Engine mode: daemon
• Default (ephemeral) primary + persistent attachment both present (has_persistent: true)

[StefanSteiner]

Fixed in #196 (merged to main as 9975209).

Root cause was broader than the filed symptom: the persistent _table_catalog is the single shared store for tables in both the ephemeral primary (where unqualified execute DDL lands) and the persistent attachment, but the post-execute reconcile enumerated only the persistent attachment. So an execute-created primary table was never stubbed (the TABLE_NOT_FOUND you saw), and a later unrelated structural execute would classify a primary table's existing catalog row as "disappeared" and silently delete its purpose/source_url/notes.

The fix makes reconcile enumerate the union of persistent ∪ ephemeral-primary tables for the shared catalog, with the rename-heuristic target pool restricted to the catalog's own DB (so no cross-DB false-rename). Seven regression tests added.

Typed chore: deliberately so release-please defers the version bump — other fixes are being held to land before v0.7.1, so this won't appear as a fix: line in that CHANGELOG. One known limitation (multiple MCP processes sharing one persistent catalog while each owns a distinct ephemeral primary) is documented in the code and PR; a principled fix needs an origin_db column and is out of scope here.