Badge Backfill Code

AI-generated summary

Ghan is examining the badge backfill process in Discourse’s codebase, specifically questioning the logic in a SQL query that uses a LEFT JOIN followed by a WHERE clause filtering for NULL user_id — which seems to negate the join’s purpose. They raise two key questions: (1) Whether the current approach — completely deleting and rebuilding a badge — is necessary, and (2) how the distinction between post-targeting and non-post-targeting badges affects this process, especially since the LEFT JOIN may render the post clause irrelevant for identifying which badge_id to wipe. The concern stems from observing long-running Sidekiq jobs, suggesting potential inefficiency or logical redundancy in the current implementation as noted in a related topic.

I’m looking at the badge backfill process happening here:

In particular, the following query seems to have some issues (see Long-Running Sidekiq Jobs)

    sql = <<~SQL
      DELETE FROM user_badges
        WHERE id IN (
          SELECT ub.id
          FROM user_badges ub
          LEFT JOIN (
            #{badge.query}
          ) q ON q.user_id = ub.user_id
          #{post_clause}
          WHERE ub.badge_id = :id AND q.user_id IS NULL
        )
    SQL

This query does a LEFT JOIN so all results from user_badges will be returned regardless if there is a match, but this part seems very confusing:

ON q.user_id = ub.user_id
...
AND q.user_id IS NULL

The query joins on rows where the user_id matches, but then discards those rows via the WHERE clause, making the join there kind of moot. So I’m wondering:

  1. What is the purpose of the backfill function? It appears to completely wipe the badge being processed and then rebuilds it all at once. Is this necessary?

  2. What is the difference between a badge that targets posts and one that does not with regard to this function? With a LEFT JOIN, does this even matter when finding the correct badge_id to wipe for the rebuild?

3 likes