Skip to content

Catalog table/column deletion leaves orphaned Term and ColumnAttribute rows that escape scoped reset #445

Open

Description

@TrBn17

Summary

When a previously compiled table or column is removed from the catalog during incremental re-ingestion (update_diff_from_existing_schema $\rightarrow$ delete_table / delete_schema / delete_columns_batch in auto_ontology/catalog/store/db.py#L113-L153) — either because the object was dropped in the source database or excluded by a newly added table_deny_regex — the catalog rows cascade away, but the semantic rows (term, column_attribute) and their vectors in vdb.semantic_layer are left behind in an unreferenced state that even delete_semantic_layer(database_name) and delete_all_data(database_name) can no longer remove.

Related to #258, but this specifically affects catalog deletion paths and breaks scoped reset/deletion invariants, upsert_table_term name allocation, and fetch_attr_column_contexts ownership resolution.

What happens

1. Dropped or newly denied tables leave term and column_attribute rows that escape delete_semantic_layer(database_name) and delete_all_data(database_name)

  1. Suppose table shop.orders (id = 'tbl-1') in database D is ingested and compiled into a Term (id = 'term-1', name = 'Order') and ColumnAttribute rows (table_id = 'tbl-1').
  2. shop.orders is dropped in D (or excluded via table_deny_regex) and D is re-ingested. delete_table('tbl-1') (catalog/store/db.py#L128-L137) runs DELETE FROM catalog_table WHERE id = 'tbl-1':
    • table__term cascades (schema.py#L479), leaving 'term-1' in public.term with zero rows in table__term.
    • column__has_attribute cascades (schema.py#L560), leaving the column_attribute rows in public.column_attribute because column_attribute.table_id is an unconstrained Text column (schema.py#L501).
    • Their embeddings in vdb.semantic_layer remain intact (re-ingest only rebuilds vdb.data_objects_layer).
  3. If the operator subsequently runs a scoped semantic reset (delete_semantic_layer("D")) or deletes the connection entirely (delete_all_data("D")), _delete_scoped_semantic("D") (auto_ontology/dal/reset.py#L66-L163) resolves scope through _table_ids("D") (current rows in catalog_table):
    • 'tbl-1' is already gone from catalog_table, so only_ours(s.table__term, ...) does not match 'term-1' (which has no table__term links at all), and s.column_attribute.c.table_id.in_(tables) does not match 'tbl-1'.
    • As a result, 'term-1' and its column_attribute rows survive delete_all_data("D") permanently unless an unscoped whole-instance reset (database_name=None) is executed.

2. Recreating or un-denying the table forces a disambiguated fallback Term name

In upsert_table_term (auto_ontology/dal/terms.py#L467-L492):

existing = store().query_read(
    select(s.term.c.id, s.table__term.c.table_id)
    .select_from(
        s.term.outerjoin(
            s.table__term, s.table__term.c.term_id == s.term.c.id
        )
    )
    .where(
        s.term.c.name == candidate_name,
        s.term.c.source == SEMANTIC_SOURCE,
    )
)
represented_tables = {
    row["table_id"] for row in existing if row.get("table_id")
}
if existing and represented_tables == {table_id}:
    ...
if existing:
    continue

Because the orphaned 'Order' row in s.term has existing = [{"id": term_id, "table_id": None}] (represented_tables == set()), if existing: continue treats 'Order' as owned by another table and forces the recreated shop.orders (which has a new table_id) onto 'Order (D.shop.orders)' even though no live table represents 'Order'.

3. semantic_layer_calculated() stays True when only orphaned Terms remain

_semantic_terms (auto_ontology/dal/terms.py#L139) requires [s.term.c.source == SEMANTIC_SOURCE, _represented()], whereas semantic_layer_calculated() (auto_ontology/dal/terms.py#L286-L292) omits _represented():

def semantic_layer_calculated() -> bool:
    """Has the semantic layer been built at all?"""
    return bool(
        store().query_read(
            select(s.term.c.id).where(s.term.c.source == SEMANTIC_SOURCE).limit(1)
        )
    )

If all represented tables are dropped (or the database is deleted after a table drop), GET /api/terms returns [] (total = 0), but semantic_layer_calculated() still returns True (so POST /api/chat/completions does not return the expected 409 readiness error and GET /api/semantic-compilation/status reports calculated: true).

4. Dropping a column causes fetch_attr_column_contexts to promote a referencing SEMANTIC_FK column as the owning column

fetch_attr_column_contexts (auto_ontology/dal/attributes.py#L296-L397) unions column__has_attribute (rank = 0, owning column) with column__semantic_fk (rank = 1, referencing FK column) and picks the first row per attr_id. When delete_columns_batch deletes the owning column, column__has_attribute (rank = 0) cascades away while column_attribute and any incoming column__semantic_fk (rank = 1) survive — so fetch_attr_column_contexts returns the referencing FK column and its table as the attribute's owning column context.

Suggested fix

  1. In delete_schema, delete_table, and delete_columns_batch (auto_ontology/catalog/store/db.py), delete column_attribute rows whose owning columns (column__has_attribute) or table_id are being deleted, and delete term rows (source = 'semantic') that lose their last table__term link (along with their vdb.semantic_layer embeddings).
  2. In _delete_orphaned_statements / dal/reset.py, also sweep any unrepresented term rows (~_represented()) and column_attribute rows whose table_id no longer exists in catalog_table (or has no column__has_attribute row).
  3. In semantic_layer_calculated() (auto_ontology/dal/terms.py#L286-L292), include _represented() so it agrees with _semantic_terms.

Activity

  1. added
    lifecycle/workflowBuilding, curating, certifying, importing, and governing the ontology
    bugSomething isn't working
    on Nov 16, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinglifecycle/workflowBuilding, curating, certifying, importing, and governing the ontology

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions