Feb 6, 2025

I immediately went back to first principles to see if there is some other way to approach it. There are 2 queries that make up the confirmation process (3 if bip30 is active, which it never is). One of the queries is inclusive and the other is exclusive. Inclusive means we check to ensure that the prevouts are confirmed. One confirmation is all we expect/need, and we find it quickly. The other is to ensure that there is no confirmed spender of each external (not in-the-same-block) prevout. This is exclusive - we must exclude all possible spenders. Since we allow conflicting and redundant blocks and txs, and we store them all in the same space, we must ensure that none of them that spend the prevout are presently confirmed (this is during confirmation, so there is no race). This exhaustive search is to identify any confirmed spend is costly, as the natural point (hash/index), and is the only thing that correlates the spends. Spends are in another table, indexed by the point that they spend - but not the natural point, what I call a "foreign point" which is the primary key of the tx hash (4) in the point table, and the index (3) or 7 bytes. So finding all of the confirmed spenders of a point logically requires reading the spend, transiting to the point table, getting the hash (32) and then searching the point table for all identical hashes, and obtaining the set of point primary keys. Then the index is used to formulate the set of foreign points, and these are used to search the spend hash table. In doing so we exclude the spend that we are presently evaluating. Any other spends are then transitioned to their parent tx primary, which is then searched in the strong_tx table (for confirmedness)...