This PR should work for what we have today, but now that the backfiller is another data source for the various database tables, we may want to revisit some of our strategies for cl_items and the various asset tables, since they can now get events out of order.
Background
This PR is needed to support replaying of Bubblegum instructions and avoid putting old data in various asset tables.
Modifying INSERT statements for Bubblegum Mint instructions to have ON_CONFLICT DO NOTHING clauses.
This allows us to replay a mint instruction if there was a failure that caused some of the inserts to various tables to be missing.
Modifying UPDATE statements for other Bubblegum instructions to have WHERE clauses that check seq.
There is/was a potential issue with this, because not every Bubblegum instruction writes the same fields to the asset tables.
Problem: If we always update the asset tables when we run old instructions during backfilling, then we might update newer data with old data. On the other hand, If we always check the sequence number for the asset table before running the old instruction, we may miss updates from older instructions because the newer instructions might not have updated the exact same fields.
Solution: One suggestion was to always write all the relevant fields. For example, have the Bubblegum delegate instruction processing also write owner to the database even though owner would not be changing during a delegate instruction. The relevant fields are present in the leaf schema today.
Note the burn command and the decompress command cannot follow this idea for different reasons, but I don't think anything could run after these commands anyways. So burn and decompress instructions are allowed to always be replayed.
Other ideas
Other suggestions involve always appending to the cl_items table and potentially doing gap management natively in the database.
Addressed last comments from @austbot, merged in latest changes from main and retested basic case with backfilling a couple missed sequence numbers and it still works. Merging into main.
Notes
cl_items
and the various asset tables, since they can now get events out of order.Background
INSERT
statements for Bubblegum Mint instructions to haveON_CONFLICT DO NOTHING
clauses.mint
instruction if there was a failure that caused some of the inserts to various tables to be missing.UPDATE
statements for other Bubblegum instructions to haveWHERE
clauses that checkseq
.delegate
instruction processing also writeowner
to the database even thoughowner
would not be changing during adelegate
instruction. The relevant fields are present in the leaf schema today.burn
command and thedecompress
command cannot follow this idea for different reasons, but I don't think anything could run after these commands anyways. Soburn
anddecompress
instructions are allowed to always be replayed.Other ideas
cl_items
table and potentially doing gap management natively in the database.