sqldb: avoid materializing non-terminal payments
What the developer wrote
Authored by ziggie
sqldb: avoid materializing non-terminal payments
What changed, and why it matters
This commit rewrites a database query that lists unfinished Lightning payments. The old query first built a temporary list of all matching payment IDs, which could become huge and consume excessive memory or CPU. The new query checks the same conditions directly while scanning payments in small paginated chunks, avoiding that large intermediate result. It is a performance and robustness improvement rather than a fix for a clear security vulnerability.
Treat as a routine performance/robustness improvement. No immediate security response required, but operators running large nodes should benefit from reduced memory and CPU spikes during payment reconciliation.
Security signals we found
Resource-exhaustion risk from unbounded CTE materialization reduced
Query logic preserved while eliminating large intermediate result set
Pagination now applied before expensive non-terminal filtering
Evidence from the diff
The patch replaces a CTE-based FetchNonTerminalPayments query with an equivalent inline predicate. The original CTE (non_terminal_ids) could materialize a large set of payment IDs before pagination was applied, especially for nodes with many historical payments. The rewritten query applies the same non-terminal logic (fail_reason IS NULL with no settled attempt, or any attempt lacking a resolution) as a WHERE clause against the paginated payments scan. This avoids materializing the full ID set and lets the database use indexes more effectively. No semantic change to which rows are returned is evident.
Changed components
sqldb/sqlc/payments.sql.gosqldb/sqlc/queries/payments.sqlFetchNonTerminalPaymentsInspect captured patch +48 / −48
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index 2bf4308..42a8fb8 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -392,27 +392,6 @@ func (q *Queries) FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds [
}
const fetchNonTerminalPayments = `-- name: FetchNonTerminalPayments :many
-WITH non_terminal_ids AS (
- SELECT p.id
- FROM payments p
- WHERE p.fail_reason IS NULL
- AND NOT EXISTS (
- SELECT 1 FROM payment_htlc_attempt_resolutions hr
- JOIN payment_htlc_attempts ha
- ON ha.attempt_index = hr.attempt_index
- WHERE ha.payment_id = p.id
- AND hr.resolution_type = 1
- )
-
- UNION
-
- SELECT DISTINCT ha.payment_id AS id
- FROM payment_htlc_attempts ha
- WHERE NOT EXISTS (
- SELECT 1 FROM payment_htlc_attempt_resolutions hr
- WHERE hr.attempt_index = ha.attempt_index
- )
-)
SELECT
p.id,
p.amount_msat,
@@ -421,12 +400,33 @@ SELECT
p.fail_reason,
pi.intent_type,
pi.intent_payload
-FROM non_terminal_ids n
-JOIN payments p
- ON p.id = n.id
+FROM payments p
LEFT JOIN payment_intents pi
ON pi.payment_id = p.id
WHERE p.id > $1
+AND (
+ (
+ p.fail_reason IS NULL
+ AND NOT EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempts ha
+ JOIN payment_htlc_attempt_resolutions hr
+ ON hr.attempt_index = ha.attempt_index
+ WHERE ha.payment_id = p.id
+ AND hr.resolution_type = 1
+ )
+ )
+ OR EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempts ha
+ WHERE ha.payment_id = p.id
+ AND NOT EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
+ )
+ )
+)
ORDER BY p.id ASC
LIMIT $2
`
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index 68a9126..16682c8 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -129,27 +129,6 @@ ORDER BY p.id ASC;
-- Fetch all non-terminal payments using pagination. A payment is
-- non-terminal if it has an unresolved attempt, or if it has not been
-- permanently failed and has no settled attempt yet.
-WITH non_terminal_ids AS (
- SELECT p.id
- FROM payments p
- WHERE p.fail_reason IS NULL
- AND NOT EXISTS (
- SELECT 1 FROM payment_htlc_attempt_resolutions hr
- JOIN payment_htlc_attempts ha
- ON ha.attempt_index = hr.attempt_index
- WHERE ha.payment_id = p.id
- AND hr.resolution_type = 1
- )
-
- UNION
-
- SELECT DISTINCT ha.payment_id AS id
- FROM payment_htlc_attempts ha
- WHERE NOT EXISTS (
- SELECT 1 FROM payment_htlc_attempt_resolutions hr
- WHERE hr.attempt_index = ha.attempt_index
- )
-)
SELECT
p.id,
p.amount_msat,
@@ -158,12 +137,33 @@ SELECT
p.fail_reason,
pi.intent_type,
pi.intent_payload
-FROM non_terminal_ids n
-JOIN payments p
- ON p.id = n.id
+FROM payments p
LEFT JOIN payment_intents pi
ON pi.payment_id = p.id
WHERE p.id > $1
+AND (
+ (
+ p.fail_reason IS NULL
+ AND NOT EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempts ha
+ JOIN payment_htlc_attempt_resolutions hr
+ ON hr.attempt_index = ha.attempt_index
+ WHERE ha.payment_id = p.id
+ AND hr.resolution_type = 1
+ )
+ )
+ OR EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempts ha
+ WHERE ha.payment_id = p.id
+ AND NOT EXISTS (
+ SELECT 1
+ FROM payment_htlc_attempt_resolutions hr
+ WHERE hr.attempt_index = ha.attempt_index
+ )
+ )
+)
ORDER BY p.id ASC
LIMIT $2;
Why this scored 23/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.