René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Stale ~lineage entries cause spurious semantic-check failures; redeclare should overwrite","articleBody":"## Problem\n\nDuring the Built-On demo prep (May 18–19, 2026) on a Lakebase / PostgreSQL backend\nwe hit:\n\n```\nDataJointError: Cannot join on attribute 'image_id': different lineages\n(blob_detection.image.image_id vs None). Use .proj() to rename one of the attributes.\n```\n\non the very first `populate()` of a `dj.Imported` / `dj.Computed` table whose PK was\nfully FK-inherited from a single parent. The semantic-check is correct in principle:\nboth sides of a populate antijoin should agree on the source-table lineage of the\ninherited PK attribute. After a clean schema drop + redeclare on the *same workspace*,\nthe bug went away — `Image.proj().heading[\"image_id\"].lineage` came back equal to\n`ImageSpec.proj().heading[\"image_id\"].lineage`, both `'blob_detection.image_spec.image_id'`.\n\nThis pattern is concerning because:\n- The user can't tell from the error message whether the issue is *their* schema\n  (e.g., a real lineage mismatch the semantic-check exists to catch) or a *stale*\n  internal record they have no obvious way to inspect.\n- The recovery (`schema.rebuild_lineage()` or full drop+redeclare) is undocumented\n  in the error.\n- Originally I (incorrectly) diagnosed this as a missing fix in the populate-antijoin\n  code path and opened #1453 to disable semantic_check; closed once we found the\n  lineage table itself had been the issue. The right fix lives in declaration /\n  rebuild logic, not in the antijoin.\n\n## Speculation: what could leave stale entries in `~lineage`?\n\nThese are speculations, not verified causes — flagged for investigation:\n\n1. **DJ version skew across the table's history.** A table is first declared under\n   a DJ version whose declaration logic writes a lineage row, then later the user\n   upgrades DJ — the new code expects different lineage-string formatting (e.g.,\n   the pre-`6506badb` PostgreSQL-double-quote situation we hit in January) and the\n   live row no longer matches what `proj()` computes at query time. The table\n   itself looks healthy via `information_schema`; only `~lineage` is stale.\n\n2. **Partial declare on PostgreSQL** where the lineage insert raises silently or\n   gets rolled back independently of the DDL. Lakebase + PG 17 may have quirks here\n   that MySQL doesn't.\n\n3. **Schema-definition changes that don't re-touch the table.** Today's `@schema`\n   decorator path may skip re-writing `~lineage` if the table already exists\n   (`is_declared == True`). If the in-code definition diverges from what's stored\n   — e.g., a FK retargeting that doesn't change column types and so passes the\n   schema-equivalence check — `~lineage` keeps the old parent linkage forever.\n\n4. **Manual `DROP TABLE` outside DataJoint** that leaves orphan `~lineage` rows\n   pointing at a no-longer-existent table.\n\n5. **Multiple schemas with overlapping table names** during dev (we drop+redeclare\n   many times during this kind of work). If the `~lineage` cleanup keys by table\n   identity in a way that differs from the redeclare's identity, ghost rows can\n   accumulate.\n\n## Proposed direction\n\nEven without pinning the root cause, the user-visible failure is bad enough to\nwarrant defensive declaration logic:\n\n### Short term (declaration robustness)\n- On every `@schema` decoration of a table (whether `is_declared` is True or False),\n  **clear and re-insert** the table's rows in `~lineage` from the current FK\n  definition. Make declaration idempotent for `~lineage`, the same way it is for\n  the actual table structure.\n- Add a fast post-declare consistency check (or invariant assertion under\n  `safemode=True`): for each PK attribute, the lineage recorded in `~lineage`\n  matches the lineage that `Table.proj()` would compute. If not, log a warning\n  pointing the user at `schema.rebuild_lineage()`.\n\n### Medium term (better error)\nWhen the semantic-check on the populate antijoin fails with a `None` lineage on\none side — which is by construction either a freshly-declared-but-not-yet-saved\ntable or a stale row — surface a tailored error:\n\n```\nCannot join on attribute 'X': lineage missing on \u003ctable_name\u003e. This usually\nindicates a stale `~lineage` entry from an older DataJoint version or an\nincomplete redeclare. Run:\n    schema.rebuild_lineage()\nto recompute from current FK definitions.\n```\n\n### Long term (schema versioning)\nTag every `~lineage` row with the DJ minor version that wrote it. On read,\nif the row's version is older than a known floor (e.g., the version that\nintroduced clean PG-quote stripping), trigger an in-place rebuild for that\nrow before returning it. This keeps the cost off the hot path for current-version\nschemas while making upgrades self-healing.\n\n## Repro vector (for whoever picks this up)\n\nWe don't have a deterministic reproducer because the failing state on the workspace\nis gone (cleaned via drop+redeclare). But a likely synthetic repro:\n\n1. Apply [`6506badb`](https://github.com/datajoint/datajoint-python/commit/6506badb)\n   in reverse (or check out master before that SHA) and declare a schema with an\n   FK against PostgreSQL.\n2. Inspect `~lineage` — entries should carry double-quotes inside the lineage\n   string (`\"db\".\"table\".attr` rather than `db.table.attr`).\n3. Upgrade DJ to master, **don't** drop the schema. Try `populate()` on a child\n   table — semantic-check failure should reappear.\n4. Confirm `schema.rebuild_lineage()` clears it; confirm a full schema redeclare\n   clears it; confirm that just re-running `@schema(Table)` *without* recomputing\n   the row does not clear it.\n\n## What was *not* the cause (closing #1453's misdirection)\n\nThe populate antijoin's semantic-check at\n`(self._jobs_to_do(restrictions) - self.proj())` is the right design. Disabling\nit (as #1453 proposed) would silence the entire class of correctness bugs that\n#1405 added it to catch. This issue is specifically about making the\n*lineage table* trustworthy, not about loosening the check that reads it.","author":{"url":"https://github.com/dimitri-yatsenko","@type":"Person","name":"dimitri-yatsenko"},"datePublished":"2026-05-19T23:43:18.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/1454/datajoint-python/issues/1454"}