A corpus-wide --rederive clears rdkit_mol_id / rdkit_reaction_id on every row whose SMILES changed:
ON CONFLICT (reaction_id) DO UPDATE SET
reaction_smiles = EXCLUDED.reaction_smiles,
rdkit_reaction_id = NULL
The rationale in database.py is sound as far as it goes — "the structure it pointed at is the old SMILES" — but the consequence is that the row leaves structure search entirely until the linking pass reaches it, and that pass runs serially after every dataset's SMILES stage rather than per dataset.
On the August 2026 rederive that put 1.98M of 2.43M reactions (81%) outside reaction-SMARTS search for hours. Component search was unaffected, since compound_smiles and product_compound_smiles relink almost immediately; only ReactionSmartsQuery joins rdkit.reactions.
Proposal
Leave the existing link in place and let the linking pass replace it.
A reaction findable by its old structure beats one that is not findable at all, and the state self-corrects. For the rederive that motivated this, old and new were the same molecule with a dative bond spelled differently (-> versus |C:|), so a stale link would have been very nearly harmless — where clearing it removed most of the corpus from that search path.
The linking pass then needs to pick up rows that are linked but stale, not just NULL ones. Options worth weighing:
- a
smiles_linked column recording which SMILES the current link was derived from, so "stale" is a comparison rather than a guess;
- or a nullable
rdkit_*_id_pending alongside, swapped in when the new structure lands.
The first is simpler and makes the partial indexes express the real predicate.
What not to do
Linking atomically in the same statement as the SMILES update. rdkit.mols is shared and deduplicated by structure, which is why the linking pass is serial "to avoid deadlocks" (loading.py:173); doing the insert inside each parallel shard's transaction reintroduces exactly that contention.
Notes
Not urgent — a rederive is rare and can be scheduled for a low-traffic window, which is the current mitigation and is now written down in the load skill. But the present behavior makes a rederive against a live database far more disruptive than the "only changed rows are affected" reading suggests, and that gap is worth closing before the next one.
🤖 Generated with Claude Code
A corpus-wide
--rederiveclearsrdkit_mol_id/rdkit_reaction_idon every row whose SMILES changed:The rationale in
database.pyis sound as far as it goes — "the structure it pointed at is the old SMILES" — but the consequence is that the row leaves structure search entirely until the linking pass reaches it, and that pass runs serially after every dataset's SMILES stage rather than per dataset.On the August 2026 rederive that put 1.98M of 2.43M reactions (81%) outside reaction-SMARTS search for hours. Component search was unaffected, since
compound_smilesandproduct_compound_smilesrelink almost immediately; onlyReactionSmartsQueryjoinsrdkit.reactions.Proposal
Leave the existing link in place and let the linking pass replace it.
A reaction findable by its old structure beats one that is not findable at all, and the state self-corrects. For the rederive that motivated this, old and new were the same molecule with a dative bond spelled differently (
->versus|C:|), so a stale link would have been very nearly harmless — where clearing it removed most of the corpus from that search path.The linking pass then needs to pick up rows that are linked but stale, not just
NULLones. Options worth weighing:smiles_linkedcolumn recording which SMILES the current link was derived from, so "stale" is a comparison rather than a guess;rdkit_*_id_pendingalongside, swapped in when the new structure lands.The first is simpler and makes the partial indexes express the real predicate.
What not to do
Linking atomically in the same statement as the SMILES update.
rdkit.molsis shared and deduplicated by structure, which is why the linking pass is serial "to avoid deadlocks" (loading.py:173); doing the insert inside each parallel shard's transaction reintroduces exactly that contention.Notes
Not urgent — a rederive is rare and can be scheduled for a low-traffic window, which is the current mitigation and is now written down in the load skill. But the present behavior makes a rederive against a live database far more disruptive than the "only changed rows are affected" reading suggests, and that gap is worth closing before the next one.
🤖 Generated with Claude Code