perf: resolve machine connection state through the current daemon runtime (#679) * perf: resolve machine connection state through the current daemon runtime machine_connection_states decided "online" with a select-list EXISTS over online_daemon_runtimes. Postgres cannot flatten that EXISTS, and because the lease_expires_at > statement_timestamp() estimate collapses to one row once the lease histogram is older than now, it hashes the subplan: every lookup of one machine scanned every online runtime in the fleet, so GetMachine, InsertProcess, InsertProcessAction and ListExecutableAgentMachineBindings got slower with every machine connected anywhere. The view now left-joins the machine's current active runtime by current_daemon_runtime_id and its unrevoked token, and evaluates the lease in the CASE. The online definition already required the runtime to be the machine's current one, so the result is unchanged. Keeping the lease check out of the join means the misestimated predicate can never steer the scan onto daemon_runtimes_active_lease_expiry_idx. GetMachineWakeState and GetProcessActionCreateBlocker embedded the same EXISTS and now read the view. The integration test compares the view with the previous definition across a fixture matrix and checks each hot lookup against a 600-runtime fleet with stale lease statistics. Co-Authored-By: Claude Opus 5.5 (1M context) * test: wrap long SQL lines and check rows.Err in the connection-state test Co-Authored-By: Claude Opus 5.5 (1M context) * test: prove connection-state lookups stay flat as the online fleet grows The plan check bounded output rows at a single fleet size, so a scan that filtered many runtime rows, or a plan with no runtime probe at all, could pass. The test now runs every lookup at 100 and 1,000 online runtimes. It requires a current-runtime index probe and counts loops and filtered rows, and it holds both sizes to one fixed buffer budget with near-zero growth. The matrix adds ended runtimes with unexpired leases so that the active-state check is load-bearing. GetMachineWakeState and GetProcessActionCreateBlocker are now executed across online, asleep and offline machines to pin the booleans they return. Co-Authored-By: Claude Opus 5.5 (1M context) * test: keep connection-state checks to expected states and current-runtime probes Drop the comparison against the previous view definition, which would only carry dead SQL after the migration, and the second fleet size with its buffer budgets, which repeated what the per-lookup probe check already proves. Exercise the active-state filter with an expired-lease runtime whose lease is still live, drop the cases the returned-value transitions already cover, and read expected states from the case table alone so a missing or duplicated machine fails. Co-Authored-By: Claude Opus 5.5 (1M context) * test: count only filter-removed rows as visited in plan nodes Index-recheck removals only occur for lossy indexes or bitmap scans that overflow work_mem, which the connection-state plan check never produces; a lookup that scans the fleet shows up as rows removed by filter. Co-Authored-By: Claude Opus 5.5 (1M context) * test: bound the rows each runtime step returns instead of rows visited The fleet-wide lookups this check guards against return every online runtime from the scan; none of the lookups discard runtime rows through a filter, so the filter-removed count and the RowsVisited helper added nothing. Co-Authored-By: Claude Opus 5.5 (1M context) * perf: drop conditions and test scaffolding that no check depends on The machine CHECK already ties deleted_at to lifecycle_state, and the online boolean needs no cast for sqlc to type it. Each remaining case and transition is the only one that catches some single-condition change to the view or the two queries; the dropped cases, the offline transition, the view-state check inside the transitions and the vacuity assertions caught nothing the rest did not. Only the runtime statistics and history make the previous view pick its fleet-wide plan, so the fixture analyzes just daemon_runtimes and drops the lease jitter. Co-Authored-By: Claude Opus 5.5 (1M context) * test: bound token reads too and analyze every table the view reads On a fresh database the previous view sometimes drives its fleet-wide scan from machine_daemon_tokens, so only bounding daemon_runtimes let half the lookups pass against it. Analyzing machines and tokens alongside runtimes keeps their estimates independent of whatever else the database holds, and with token reads bounded the runtime history no longer changes any outcome. Co-Authored-By: Claude Opus 5.5 (1M context) * test: drop ON COMMIT DROP from the connection cases table The test transaction always rolls back, which already removes the table. Co-Authored-By: Claude Opus 5.5 (1M context) --------- Co-authored-by: Claude Opus 5.5 (1M context) Co-authored-by: Kartik Sarangmath

Sunbelt Computer Software

PL/B Language Development and Support