Repository navigation
driver-sql (PG): introspectForeignKeys' information_schema query is not schema-scoped — a same-named table in another schema contributes its foreign keys #11201
Description
Activity
First-touch (triage): promoted
pm:queue,domain:engine, Bug — the last unscoped introspection arm; fix shape is the family's existing pin (AND tc.table_schema = ANY (current_schemas(false))), keeping one resolution rule across the four arms. The live-PG test isolation (#9350, per-file schemas in one database) makes name collision the normal state of a test run, so a regression pin against two same-named tables in different schemas is in scope. Serial:sql-driver.tsis hot (#11161 in flight, #11223/#11224 queued this round) — the engine seat owns the five-gate fold-or-serial answer across the family at claim time.
Generated by Claude Code
Claim — engine seat, session
session_01RfyXxZ2WPjcjhuXpiQQc3y, branchclaude/issue-11201-introspect-fk-schema-scope.pm:queue→pm:dispatchedin one label write, read back.Correction: this card was held earlier today on a mis-measurement — mine
At ~10:07 I recorded, on the seat post, that #11201 would not be dispatched because
introspectForeignKeys"is exactly the region #11270 is rewriting". That was wrong, and the way it was wrong is worth recording. I read the wordforeignKeysin #11270's PR body — where it appears in a sentence describing which extra keys the interface carries (isUnique,maxLength,foreignKeys,primaryKeys) — and treated a prose mention as a region reading. I never opened the diff.Measured now, on the actual patch (
origin/main...96b4153c):PR hunks in sql-driver.tswhat they are #11270 (parked draft) 3673–3697, 3702–3715, 3722–3729, 12925–12934 the three Omitinterface declarations + one column-type site#11224 (in flight) ~5937 updatedAtStamp()and call sites 6139 / 7100 / 7155write-door stamp #11201 (this card) 13137 introspectForeignKeysbody, call site 10324the information_schemaWHERE clauseintrospectForeignKeysis ~200 lines below #11270's nearest hunk and in a different method. The three regions are disjoint. The serialization carve-out applies as written — a clearly-stated region does not force whole-file serialization — and #11270's body states its region precisely.Two things made the wrong hold more expensive than a simple miss: #11270 is parked as a draft until the cloud-side reading on seam card #11232 posts clean, so the hold I placed had no bounded exit; and the failure shape is the one already recorded on #11223 this term — inheriting a claim and dressing it in the language of measurement. A region claim is a measurement claim. The diff is cheap; I should have read it the first time.
H17 trigger-file check — no rider owed
Two
pm:on-holdcards declare this file as their trigger, and both scope the trigger to a function, not the file: #8740 names the upsert region /insertOnlyUpsertColumns; #6009 namessqliteCanonicalDatetimeSql. This change touches neither. The discipline is already on record at #6009 (2026-08-07): "read the patch rather than the file list, because restart condition 2 is about one function, not the file" — and, explicitly, "asql-driver.tscommit in the log is not by itself a trigger for this issue." A courtesy touch-notice will be posted on both cards once the PR number exists.Scope
Not Clause ②:
packages/specis untouched,IntrospectedForeignKey's shape does not change, and the accepted set is not widened — a same-named table in another schema ceases to contribute foreign keys it never should have contributed. That isdeclared = enforcedrestored, i.e. a Bug fix, dispatched at the standard tier.
Generated by Claude Code
- added a commit that references this issue
on Aug 23, 2026 { "issue": 11201, "status": "done", "branch": "claude/issue-11201-introspect-fk-schema-scope", "pr": "https://github.com/objectstack-ai/objectstack/pull/11325", "premise_still_valid": true, "summary": "Verified the issue's premise against origin/main before writing: introspectForeignKeys' Postgres arm really did filter information_schema.table_constraints on constraint_type + tc.table_name with no table_schema predicate, and it was the last unscoped introspection arm. Added the family's existing pin, `AND tc.table_schema = ANY (current_schemas(false))`, as the final WHERE predicate, plus a comment recording why. No interface shape changed and no accepted input widened. SIBLING-ARM COMPARISON (done on the file, not copied from the card): exactly two arms carry this predicate — introspectSchema's table listing (~10293) and introspectUniqueConstraints (~13416) — and they agree with each other exactly in spelling, ANY(...) form, and placement as the last WHERE predicate after `tc.table_name = ?`; this PR matches both, so there was no winner to pick. The two pg_index arms (introspectIndexes ~9965 `to_regclass(?)`, introspectPrimaryKeys ~13294 `?::regclass`) are not a disagreement: pg_index takes a relation rather than a schema name, so they reach the same session scoping by resolving to an OID. introspectColumnOrder (~13049) uses `current_schema()` deliberately and documents why — it mirrors knex's own columnInfo() scoping. Existing JOIN correlations on kcu.table_schema / ccu.table_schema were left untouched. MYSQL ARM: checked, NOT affected and NOT widened into this PR — it already pins `TABLE_SCHEMA = DATABASE()` on a direct KEY_COLUMN_USAGE read, so the defect is not expressible there; SQLite has no schemas. REGION DISCIPLINE: both hunks land at ~13151 and ~13167 inside the query body; IntrospectedForeignKey's shape is unchanged and #11270's declaration regions (3673-3729, 12925-12934) and #11224's write-door stamp region are untouched.", "tests": "All runs on tree 5c2897943 (= PR head, working tree clean, `git status --porcelain` empty). Heavy commands went through scripts/pm/os-verify-lock.sh; verdicts read from its VERDICT line, exit codes captured by redirect-then-capture, never after a pipe. LIVE SERVER: a real PostgreSQL 16.13 was reachable in this container at the CI-documented URL (postgres://postgres:postgres@127.0.0.1:5432/postgres) — 'PostgreSQL 16.13 (Ubuntu 16.13-0ubuntu0.24.04.1)'. I connected only; I started, stopped and restarted nothing. NEW PIN: packages/drivers/driver-sql/src/sql-driver-11201-introspect-fk-schema-scope.test.ts, PG-only via declareDialectCell(PG_CELL, ...) so an unprovisioned run is a named skip and a red under OS_EXPECT_LIVE_DIALECT_MATRIX=1. Suite label in the output reads 'live postgres', confirming the live cell ran and not a SQLite fallback. Case 1 is a non-vacuity pin (re-issues the pre-fix predicate and requires it to see BOTH schemas' constraints, plus to_regclass resolving the bare name to this file's own schema) so the absence assertions cannot go green on a fixture that never collided. REVERSE VERIFICATION, direction predicted before each leg. Leg 1 (fix applied) predicted 3 passed, observed 'Test Files 1 passed (1) / Tests 3 passed (3)'. Leg 2 (fix reverted) predicted cases 2+3 RED with the neighbour's FK present and case 1 still green because it issues the pre-fix predicate itself; observed exactly that — 1 passed, 2 failed, each failure diff carrying the extra row {columnName: 'there_ref', constraintName: 'os11201_fk_there', referencedTable: 'os11201_ref_there'}. Leg 3 (restored) predicted 3 passed, observed 3 passed. MUTATION PROVEN ON DISK, not from an editor exit code: before leg 2, `grep -c` on the new predicate went 2 -> 1 (the unique-constraints arm keeps its one), the new comment marker 'spans EVERY schema the session' counted 0, and `git diff --stat HEAD` showed 20 deletions. RESTORE PROVEN: predicate count back to 2, marker back to 1, `git diff --quiet HEAD` byte-identical to the committed blob, `git status --porcelain` empty. NO REBUILD between legs and the behaviour still flipped — positive evidence the pin resolves SOURCE (it imports '../src/index.js' from inside src/), so no dist/ablation preflight applies; nothing here is an ablation of a gate. GATES — 22 run, ALL exit 0, by name. Path-derived from `node scripts/pm/dispatch-gates.mjs` with no paths passed (it derived the change set itself from merge-base e278a2970, three-dot): check:changeset-gate-self-tests 0, check:driver-conformance 0, check:objectui-changeset 0, check:published-files 0, check:slot-lookup 0, check:test-source-alias 0, check:type-source-resolution 0, check-adr-0087-registration.mjs 0, check-changeset-no-major.mjs 0, check-ci-filter-parity.mjs 0, check-empty-changeset.mjs 0, check-plugin-teardown-shape.mjs 0, docs-audit/check-affected-docs.mjs 0. Convention-triggered (adds a test file): check:query-options-erasure 0, check:type-check-coverage 0, check:type-check-debt 0, check:engine-double-contract 0, check:cross-package-test-inputs 0, check:where-matcher 0. check:type-check-debt was run AFTER `turbo run build --filter=./packages/* --filter=./packages/*/*` exactly as lint.yml sequences it, so it measured rather than refused, printing 'check-type-check-coverage --re-measure: OK — 33 ledger entr(ies) re-measured in 342.9s, 1897 raw tsc error(s) total, none above its recorded number'. Also: check:nul-bytes 0, plus a manual control-byte scan `grep -naP '[\\x00-\\x08\\x0b\\x0c\\x0e-\\x1f\\x7f]'` over both touched files (no matches). PACKAGE: `pnpm --filter @objectstack/driver-sql typecheck` exit 0 with the script name echoed ('> tsc --noEmit'), so this was not a zero-match silent green. FULL PACKAGE SUITE against the live server, TZ=America/New_York + OS_TEST_POSTGRES_URL: 'Test Files 120 passed | 1 skipped (121) / Tests 2153 passed | 45 skipped (2198)', 0 failed, VERDICT command-exit 0. NOT MEASURED, and not claimed: live MySQL was not exercised locally — the diff does not touch the MySQL branch at all, and CI's required 'Temporal Conformance (live PG + MySQL)' job covers that cell. I did not wait for CI; gate status there is whatever the PR reports.", "open_questions": [], "out_of_scope_findings": [ "filed as #11324: driver-sql (PG) introspectForeignKeys' JOIN correlations are wrong in two further ways, both MEASURED on the live PG 16.13 against the query as it stands AFTER this card's fix (so this PR neither causes nor repairs them) — (a) `ccu.table_schema = tc.table_schema` demands parent and child share a schema, so a foreign key whose target table lives in another schema returns ZERO rows and vanishes from the answer; (b) the kcu-to-ccu join carries no ordinal correlation, so a 2-column composite foreign key returns 4 rows, the cartesian product (x->a, x->b, y->a, y->b instead of x->a, y->b). Filed unassigned and unlabelled for PM triage rather than fixed in place: different defect class from this card's unscoped read, each needs its own fixture, and the likely repair (a pg_constraint + WITH ORDINALITY rewrite, the shape #11101/#11162 used for primary keys) could reach IntrospectedForeignKey's declaration, which is #11270's region. Searched open issues first (introspectForeignKeys, constraint_column_usage, composite foreign key) — #11202 is introspectUniqueConstraints and #11122 is the introspectSchema spec-contract divergence; neither covers this.", "not a defect, a PM bookkeeping item carried over from the claim comment: the H17 courtesy touch-notice promised to #8740 and #6009 once a PR number existed is still outstanding — PR #11325 now exists. Left to the PM: those are on-hold cards outside this card's scope, and neither trigger fires here (they scope to insertOnlyUpsertColumns and sqliteCanonicalDatetimeSql respectively; this change touches neither)." ] }
Generated by Claude Code
os-dev-report
Re-post of the dev report. The previous comment (5385764294) carried the marker in HTML-comment form and the GitHub body sanitizer ate it — the stored body now begins with the bare code fence, so that comment is invisible to a marker scan. Same JSON, marker as literal text per the fallback. Supersedes 5385764294; nothing in the payload changed.
{ "issue": 11201, "status": "done", "branch": "claude/issue-11201-introspect-fk-schema-scope", "pr": "https://github.com/objectstack-ai/objectstack/pull/11325", "premise_still_valid": true, "summary": "Verified the issue's premise against origin/main before writing: introspectForeignKeys' Postgres arm really did filter information_schema.table_constraints on constraint_type + tc.table_name with no table_schema predicate, and it was the last unscoped introspection arm. Added the family's existing pin, `AND tc.table_schema = ANY (current_schemas(false))`, as the final WHERE predicate, plus a comment recording why. No interface shape changed and no accepted input widened. SIBLING-ARM COMPARISON (done on the file, not copied from the card): exactly two arms carry this predicate — introspectSchema's table listing (~10293) and introspectUniqueConstraints (~13416) — and they agree with each other exactly in spelling, ANY(...) form, and placement as the last WHERE predicate after `tc.table_name = ?`; this PR matches both, so there was no winner to pick. The two pg_index arms (introspectIndexes ~9965 `to_regclass(?)`, introspectPrimaryKeys ~13294 `?::regclass`) are not a disagreement: pg_index takes a relation rather than a schema name, so they reach the same session scoping by resolving to an OID. introspectColumnOrder (~13049) uses `current_schema()` deliberately and documents why — it mirrors knex's own columnInfo() scoping. Existing JOIN correlations on kcu.table_schema / ccu.table_schema were left untouched. MYSQL ARM: checked, NOT affected and NOT widened into this PR — it already pins `TABLE_SCHEMA = DATABASE()` on a direct KEY_COLUMN_USAGE read, so the defect is not expressible there; SQLite has no schemas. REGION DISCIPLINE: both hunks land at ~13151 and ~13167 inside the query body; IntrospectedForeignKey's shape is unchanged and #11270's declaration regions (3673-3729, 12925-12934) and #11224's write-door stamp region are untouched.", "tests": "All runs on tree 5c2897943 (= PR head, working tree clean, `git status --porcelain` empty). Heavy commands went through scripts/pm/os-verify-lock.sh; verdicts read from its VERDICT line, exit codes captured by redirect-then-capture, never after a pipe. LIVE SERVER: a real PostgreSQL 16.13 was reachable in this container at the CI-documented URL (postgres://postgres:postgres@127.0.0.1:5432/postgres) — 'PostgreSQL 16.13 (Ubuntu 16.13-0ubuntu0.24.04.1)'. I connected only; I started, stopped and restarted nothing. NEW PIN: packages/drivers/driver-sql/src/sql-driver-11201-introspect-fk-schema-scope.test.ts, PG-only via declareDialectCell(PG_CELL, ...) so an unprovisioned run is a named skip and a red under OS_EXPECT_LIVE_DIALECT_MATRIX=1. Suite label in the output reads 'live postgres', confirming the live cell ran and not a SQLite fallback. Case 1 is a non-vacuity pin (re-issues the pre-fix predicate and requires it to see BOTH schemas' constraints, plus to_regclass resolving the bare name to this file's own schema) so the absence assertions cannot go green on a fixture that never collided. REVERSE VERIFICATION, direction predicted before each leg. Leg 1 (fix applied) predicted 3 passed, observed 'Test Files 1 passed (1) / Tests 3 passed (3)'. Leg 2 (fix reverted) predicted cases 2+3 RED with the neighbour's FK present and case 1 still green because it issues the pre-fix predicate itself; observed exactly that — 1 passed, 2 failed, each failure diff carrying the extra row {columnName: 'there_ref', constraintName: 'os11201_fk_there', referencedTable: 'os11201_ref_there'}. Leg 3 (restored) predicted 3 passed, observed 3 passed. MUTATION PROVEN ON DISK, not from an editor exit code: before leg 2, `grep -c` on the new predicate went 2 -> 1 (the unique-constraints arm keeps its one), the new comment marker 'spans EVERY schema the session' counted 0, and `git diff --stat HEAD` showed 20 deletions. RESTORE PROVEN: predicate count back to 2, marker back to 1, `git diff --quiet HEAD` byte-identical to the committed blob, `git status --porcelain` empty. NO REBUILD between legs and the behaviour still flipped — positive evidence the pin resolves SOURCE (it imports '../src/index.js' from inside src/), so no dist/ablation preflight applies; nothing here is an ablation of a gate. GATES — 22 run, ALL exit 0, by name. Path-derived from `node scripts/pm/dispatch-gates.mjs` with no paths passed (it derived the change set itself from merge-base e278a2970, three-dot): check:changeset-gate-self-tests 0, check:driver-conformance 0, check:objectui-changeset 0, check:published-files 0, check:slot-lookup 0, check:test-source-alias 0, check:type-source-resolution 0, check-adr-0087-registration.mjs 0, check-changeset-no-major.mjs 0, check-ci-filter-parity.mjs 0, check-empty-changeset.mjs 0, check-plugin-teardown-shape.mjs 0, docs-audit/check-affected-docs.mjs 0. Convention-triggered (adds a test file): check:query-options-erasure 0, check:type-check-coverage 0, check:type-check-debt 0, check:engine-double-contract 0, check:cross-package-test-inputs 0, check:where-matcher 0. check:type-check-debt was run AFTER `turbo run build --filter=./packages/* --filter=./packages/*/*` exactly as lint.yml sequences it, so it measured rather than refused, printing 'check-type-check-coverage --re-measure: OK — 33 ledger entr(ies) re-measured in 342.9s, 1897 raw tsc error(s) total, none above its recorded number'. Also: check:nul-bytes 0, plus a manual control-byte scan `grep -naP '[\\x00-\\x08\\x0b\\x0c\\x0e-\\x1f\\x7f]'` over both touched files (no matches). PACKAGE: `pnpm --filter @objectstack/driver-sql typecheck` exit 0 with the script name echoed ('> tsc --noEmit'), so this was not a zero-match silent green. FULL PACKAGE SUITE against the live server, TZ=America/New_York + OS_TEST_POSTGRES_URL: 'Test Files 120 passed | 1 skipped (121) / Tests 2153 passed | 45 skipped (2198)', 0 failed, VERDICT command-exit 0. NOT MEASURED, and not claimed: live MySQL was not exercised locally — the diff does not touch the MySQL branch at all, and CI's required 'Temporal Conformance (live PG + MySQL)' job covers that cell. I did not wait for CI; gate status there is whatever the PR reports.", "open_questions": [], "out_of_scope_findings": [ "filed as #11324: driver-sql (PG) introspectForeignKeys' JOIN correlations are wrong in two further ways, both MEASURED on the live PG 16.13 against the query as it stands AFTER this card's fix (so this PR neither causes nor repairs them) — (a) `ccu.table_schema = tc.table_schema` demands parent and child share a schema, so a foreign key whose target table lives in another schema returns ZERO rows and vanishes from the answer; (b) the kcu-to-ccu join carries no ordinal correlation, so a 2-column composite foreign key returns 4 rows, the cartesian product (x->a, x->b, y->a, y->b instead of x->a, y->b). Filed unassigned and unlabelled for PM triage rather than fixed in place: different defect class from this card's unscoped read, each needs its own fixture, and the likely repair (a pg_constraint + WITH ORDINALITY rewrite, the shape #11101/#11162 used for primary keys) could reach IntrospectedForeignKey's declaration, which is #11270's region. Searched open issues first (introspectForeignKeys, constraint_column_usage, composite foreign key) — #11202 is introspectUniqueConstraints and #11122 is the introspectSchema spec-contract divergence; neither covers this.", "not a defect, a PM bookkeeping item carried over from the claim comment: the H17 courtesy touch-notice promised to #8740 and #6009 once a PR number existed is still outstanding — PR #11325 now exists. Left to the PM: those are on-hold cards outside this card's scope, and neither trigger fires here (they scope to insertOnlyUpsertColumns and sqliteCanonicalDatetimeSql respectively; this change touches neither)." ] }
Generated by Claude Code
Generated by Claude Code
- added a commit that references this issue
on Aug 23, 2026
Found while working #11161 (which repaired the error contract of this method, not its query). Not fixed there: the bounded in-place-fix exemption did not apply — this is a scoping defect, a different class from the swallowed-error defect that card owns. #11161 remains open on its own subject; this issue is not addressed by that PR.
What
packages/drivers/driver-sql/src/sql-driver.ts,SqlDriver.introspectForeignKeys, Postgres arm. The query filters:with no
table_schemapredicate ontcat all.information_schema.table_constraintsspans every schema the user can read, so for a table name that exists in more than one schema, the result merges foreign keys from all of them — including schemas the session'ssearch_pathnever reaches.Why this is a real hazard here, not a theoretical one
introspectIndexesresolves byto_regclass(?)(session resolution),introspectSchemaliststable_schema = ANY (current_schemas(false)), and driver-sql: introspectPrimaryKeys / ForeignKeys / UniqueConstraints still swallow every error and report ABSENCE — the #7332 ruling was applied to introspectIndexes only #11161 just applied thecurrent_schemas(false)pin tointrospectUniqueConstraintswhen its (previously always-failing) query was repaired.introspectForeignKeysis now the only introspection arm still unscoped.Suggested shape
Add
AND tc.table_schema = ANY (current_schemas(false))— the same pinintrospectSchema's table listing andintrospectUniqueConstraintsuse, keeping one resolution rule across the four arms. (MySQL's arm already pinsTABLE_SCHEMA = DATABASE(); SQLite has no schemas.)Not measured here
Whether any in-tree consumer currently hits the collision (the live suite's per-file tables mostly have per-file names). The defect is the unscoped read, which returns a wrong answer the moment names collide.