@@ -1710,19 +1710,90 @@ func (q *FakeQuerier) DeleteOldWorkspaceAgentLogs(_ context.Context, threshold t
17101710q .mutex .Lock ()
17111711defer q .mutex .Unlock ()
17121712
1713- var validLogs []database.WorkspaceAgentLog
1714- for _ ,log := range q .workspaceAgentLogs {
1715- var toBeDeleted bool
1716- for _ ,agent := range q .workspaceAgents {
1717- if agent .ID == log .AgentID && agent .LastConnectedAt .Valid && agent .LastConnectedAt .Time .Before (threshold ) {
1718- toBeDeleted = true
1719- break
1720- }
1713+ /*
1714+ WITH
1715+ latest_builds AS (
1716+ SELECT
1717+ workspace_id, max(build_number) AS max_build_number
1718+ FROM
1719+ workspace_builds
1720+ GROUP BY
1721+ workspace_id
1722+ ),
1723+ */
1724+ latestBuilds := make (map [uuid.UUID ]int32 )
1725+ for _ ,wb := range q .workspaceBuilds {
1726+ if lastBuildNumber ,found := latestBuilds [wb .WorkspaceID ];found && lastBuildNumber > wb .BuildNumber {
1727+ continue
17211728}
1729+ // not found or newer build number
1730+ latestBuilds [wb .WorkspaceID ]= wb .BuildNumber
1731+ }
17221732
1723- if ! toBeDeleted {
1724- validLogs = append (validLogs ,log )
1733+ /*
1734+ old_agents AS (
1735+ SELECT
1736+ wa.id
1737+ FROM
1738+ workspace_agents AS wa
1739+ JOIN
1740+ workspace_resources AS wr
1741+ ON
1742+ wa.resource_id = wr.id
1743+ JOIN
1744+ workspace_builds AS wb
1745+ ON
1746+ wb.job_id = wr.job_id
1747+ LEFT JOIN
1748+ latest_builds
1749+ ON
1750+ latest_builds.workspace_id = wb.workspace_id
1751+ AND
1752+ latest_builds.max_build_number = wb.build_number
1753+ WHERE
1754+ -- Filter out the latest builds for each workspace.
1755+ latest_builds.workspace_id IS NULL
1756+ AND CASE
1757+ -- If the last time the agent connected was before @threshold
1758+ WHEN wa.last_connected_at IS NOT NULL THEN
1759+ wa.last_connected_at < @threshold :: timestamptz
1760+ -- The agent never connected, and was created before @threshold
1761+ ELSE wa.created_at < @threshold :: timestamptz
1762+ END
1763+ )
1764+ */
1765+ oldAgents := make (map [uuid.UUID ]struct {})
1766+ for _ ,wa := range q .workspaceAgents {
1767+ for _ ,wr := range q .workspaceResources {
1768+ if wr .ID != wa .ResourceID {
1769+ continue
1770+ }
1771+ for _ ,wb := range q .workspaceBuilds {
1772+ if wb .JobID != wr .JobID {
1773+ continue
1774+ }
1775+ latestBuildNumber ,found := latestBuilds [wb .WorkspaceID ]
1776+ if ! found {
1777+ panic ("workspaceBuilds got modified somehow while q was locked! This is a bug in dbmem!" )
1778+ }
1779+ if latestBuildNumber == wb .BuildNumber {
1780+ continue
1781+ }
1782+ if wa .LastConnectedAt .Valid && wa .LastConnectedAt .Time .Before (threshold )|| wa .CreatedAt .Before (threshold ) {
1783+ oldAgents [wa .ID ]= struct {}{}
1784+ }
1785+ }
1786+ }
1787+ }
1788+ /*
1789+ DELETE FROM workspace_agent_logs WHERE agent_id IN (SELECT id FROM old_agents);
1790+ */
1791+ var validLogs []database.WorkspaceAgentLog
1792+ for _ ,log := range q .workspaceAgentLogs {
1793+ if _ ,found := oldAgents [log .AgentID ];found {
1794+ continue
17251795}
1796+ validLogs = append (validLogs ,log )
17261797}
17271798q .workspaceAgentLogs = validLogs
17281799return nil