The GetInvoice query used an OR IS NULL pattern for each filter
parameter:
WHERE (i.hash = $1 OR $1 IS NULL)
AND (i.payment_addr = $2 OR $2 IS NULL)
SQLite's query planner decides on an execution plan at prepare time,
before seeing any parameter values. Because either condition can be
trivially true when its parameter is NULL, the planner conservatively
falls back to a full table scan rather than using the unique indexes on
hash and payment_addr. This caused every invoice lookup and update on
the hot path (HTLC settlement) to scan the entire invoices table.
Replace the single catch-all query with three dedicated queries, each
using a direct equality on a uniquely constrained column:
- GetInvoiceByHash: WHERE hash = $1
- GetInvoiceByAddr: WHERE payment_addr = $1 (new, covers AMP path)
- GetInvoiceBySetID: existing, unchanged
Update getInvoiceByRef to route to the appropriate query based on which
fields are present in the InvoiceRef. When both hash and payment address
are provided, we look up by hash and then verify the returned invoice's
payment address matches.
GetInvoice is now unused and removed from the SQLInvoiceQueries
interface.
Older LND versions could create multiple payments for the same hash.
We need to preserve those historical records during KV→SQL migration,
but they don’t fit the normal payment schema because we enforce a
unique payment hash constraint. Introduce a lean payment_duplicates
table to store only the essential fields (identifier, amount,
timestamps, settle/fail data).
This keeps the primary payment records stable and makes the migration
deterministic even when duplicate records lack attempt info. The table
is intentionally minimal and can be dropped after migration if no
duplicate payments exist.
For now there is no logic in place which allows the noderunner to
fetch duplicate payments after the migration.
All call sites have been migrated to the targeted replacement queries
in the previous commit. Remove FilterInvoices and its associated
params struct from the SQL source and regenerate.
The existing FilterInvoices query uses optional parameters via the
pattern `(col >= param OR param IS NULL)` for every filter. SQLite
cannot use indexes with this pattern because the OR prevents the query
planner from determining at plan time which rows satisfy the condition,
resulting in a full table scan regardless of the available indexes
(invoices_state_idx, invoices_settle_index_idx, and the primary-key
clustered index on id).
Additionally, the conditional ORDER BY:
ORDER BY CASE WHEN reverse = FALSE ... THEN id ELSE NULL END ASC,
CASE WHEN reverse = TRUE ... THEN id ELSE NULL END DESC
prevents the planner from using the index ordering and forces an
explicit sort.
Add five focused replacements, each with a plain sargable predicate and
a direct ORDER BY so the planner can always choose an index scan:
- FetchPendingInvoices: WHERE state IN (0, 3)
- FilterInvoicesBySettleIndex: WHERE settle_index >= $1
- FilterInvoicesByAddIndex: WHERE id >= $1
- FilterInvoicesForward: WHERE id >= $1 ... ORDER BY id ASC
- FilterInvoicesReverse: WHERE id <= $1 ... ORDER BY id DESC
FilterInvoicesForward and FilterInvoicesReverse accept non-nullable
timestamp parameters (created_after, created_before) so the planner
always sees plain range predicates on created_at. Callers supply
Go-side defaults when no date filter is needed, following the same
convention already used by the payments query.
FilterInvoices is kept in this commit so all existing call sites
continue to compile. It will be removed once all callers have been
migrated.
Add a gossip version parameter to DisabledChannelIDs in the Store
interface and both implementations. Add a new version-filtered SQL
query and update the builder caller.
Add SQL query to determine if a node has public v2 channels. Unlike
v1 which requires all four individual signatures, v2 channels are
considered public when the single aggregated signature is present.
Add SQL query to update the signature column for v2 channel auth proofs.
Unlike v1 which requires four separate signatures, v2 channels use a
single aggregated signature.
This query is less strict in terms of the latest update timestamp field.
We want to be less strict with our own node data since we always want
our own updates recorded.
This commit simplifies insertChanEdgePolicyMig. Much of the logic can be
removed given that this method is only used in the context of the graph
SQL migration.
This should improve the performance of the migration quite a lot since
it removes the extra GetChannelAndNodesBySCID call.
Finally, we make the channel-policy part of the SQL migration idempotent
by adding a migration-only policy insert query which will not error out
if the policy already exists and does not have a timestamp that is newer
than the existing records timestamp. To keep the commit simple, a
insertChanEdgePolicyMig function is added which is basically identical
to the updateChanEdgePolicy function except for the fact that it uses
the newly added query. In the next commit, it will be simplified even
more.
In this commit, we make the channel part of the graph SQL migration
idempotent (retry-safe!). We do this by adding a migration-only channel
insert query that will not error out if a the query is called and a
chanenl with the given scid&version already exists. We also ensure that
errors are not thrown if existing channel features & extra types are
re-added.
There is no need to use the "collect-then-update" pattern for node
insertion during the SQL migration since if we do have any previously
persisted data for the node and happen to re-run the insertion for that
node, the data will be exactly the same. So we can make use of "On
conflict, no nothing" here too.
In this commit, the graph SQL migration is updated so that the node
migration step is retry-safe. This is done by using migration specific
logic & queries that do not use the same node-update-constraint as the
normal node upsert logic. For normal "run-time" logic, we always expect
a node update to have a newer timestamp than any previously stored one.
But for the migration, we will only ever be dealing with a single
announcement for a given node & to make things retry-safe, we dont want
the query to error if we re-insert the exact same node.
Finally, we update the migrateZombieIndex function to use batch
validation just like was done in the previous commits. Here, we
additionally make sure to validate the entire zombie index entry and not
just the SCID.
As was done in the previous commits for nodes & channels, we update the
migrateClosedSCIDIndex function here so that it validates migrated
entries in batches rather than one-by-one.
As was done in the previous commits for nodes & channels, we update the
migratePruneLog function here so that it validates migrated entries in
batches rather than one-by-one.
Restructue the `migrateChannelsAndPolicies` function so that it does the
validation of migrated channels and policies in batches. So instead of
fetching channel and its policies individually after migrating it, we
wait for a minimum batch size to be reached and then validate a batch of
them together. This lets us make way fewer DB round trips.
Restructue the `migrateNodes` function so that it does the validation of
migrated nodes in batches. So instead of fetching each node individually
after migrating it, we wait for a minimum batch size to be reached and
then validate a batch of nodes together. This lets us make way fewer DB
round trips.
In this commit, we add the queries that will be needed to batch-fetch
the data of a set of nodes. The logic for using these new queries is
also added but not used yet.
In this commit, we remove the LEFT JOIN query that was used for fetching
a nodes addresses. The reason it was used before was to ensure that we'd
get an empty address list if the node did exist but had no addresses.
This was for the purposes of the `AddrsForNode` method since it needs to
return false/true to indicate if the given node exists.
Use the new `SLICES` directive to add a DeleteChannels query which takes
a set of DB channel IDs. Then replace all our calls to DeleteChannel
with a paginated call to DeleteChannels.
This commit adds a new GetChannelsByOutpoints query which takes a slice
of outpoint strings. This lets us then update PruneGraph to use
paginated calls to GetChannelsByOutpoints instead of making one DB call
per outpoint.
Here, a new query (GetChannelsByOutpoints) is added which makes use of
the /*SLICE:outpoints*/ directive & added workaround. This is then used
in a test to demonstrate how the ExecutePagedQuery helper can be used to
wrap a query like this such that calls are done in pages.
The query that has been added will also be used by live code paths in an
upcoming commit.
ziggie