Important
This is an issue created as a response to the discussion of #14 (see #14 (comment)). It therefore depends on #14 to complete!
Context
omop_alchemy/maintenance/cli_vocab.py creates a dedicated staging schema (ensure_schema(engine, "staging"), line 274) and sets search_path = staging, {db_schema} on every connection (lines 286-291), purely so orm_loader's unqualified CREATE UNLOGGED TABLE "_staging_concept" lands in staging instead of db_schema. orm_loader itself has no schema concept, it only disambiguates staging vs. target tables by the _staging_ name prefix, so the schema split is a workaround layered on top, not something orm_loader knows about.
Problem
This only works because the prefix and the target table name never collide. Anyone reading this code has to reconstruct the full search_path resolution chain to know which schema an unqualified statement actually targets, this came up directly in PR review for the crash-recovery TRUNCATE TABLE "concept".
Proposal
Once orm_loader exposes schema-aware staging (see AustralianCancerDataNetwork/orm-loader#15), pass the staging schema to it explicitly, drop the search_path connect-event listener, and qualify any remaining unqualified statements (like the crash-recovery TRUNCATE) with db_schema directly. Removes the dependency on search_path ordering and prefix non-collision entirely.
Decision
Considered dropping the separate staging schema and relying on the prefix alone, but chose to make orm_loader schema-aware instead, the schema split is worth keeping for hygiene (UNLOGGED scratch tables stay out of backups/migrations/introspection of the CDM schema).
Important
This is an issue created as a response to the discussion of #14 (see #14 (comment)). It therefore depends on #14 to complete!
Context
omop_alchemy/maintenance/cli_vocab.pycreates a dedicatedstagingschema (ensure_schema(engine, "staging"), line 274) and setssearch_path = staging, {db_schema}on every connection (lines 286-291), purely so orm_loader's unqualifiedCREATE UNLOGGED TABLE "_staging_concept"lands instaginginstead ofdb_schema. orm_loader itself has no schema concept, it only disambiguates staging vs. target tables by the_staging_name prefix, so the schema split is a workaround layered on top, not something orm_loader knows about.Problem
This only works because the prefix and the target table name never collide. Anyone reading this code has to reconstruct the full search_path resolution chain to know which schema an unqualified statement actually targets, this came up directly in PR review for the crash-recovery
TRUNCATE TABLE "concept".Proposal
Once
orm_loaderexposes schema-aware staging (see AustralianCancerDataNetwork/orm-loader#15), pass thestagingschema to it explicitly, drop thesearch_pathconnect-event listener, and qualify any remaining unqualified statements (like the crash-recovery TRUNCATE) withdb_schemadirectly. Removes the dependency on search_path ordering and prefix non-collision entirely.Decision
Considered dropping the separate
stagingschema and relying on the prefix alone, but chose to make orm_loader schema-aware instead, the schema split is worth keeping for hygiene (UNLOGGED scratch tables stay out of backups/migrations/introspection of the CDM schema).