Topic.reset_all_highest exhausts all available disk space

Contribute Bug
3

After the first two bits of my “split query” experiment went smoothly (X and Y for public topics), I tried with the Z one, and it froze - that is, the query was active according to postgres activity, and top showed the process running at 100%.
So I looked again at the SQL and I found the problem: both queries end like this

      WHERE
        topics.archetype <> 'private_message' AND
        X.topic_id = topics.id AND
        Y.topic_id = topics.id AND 
          (
          topics.highest_staff_post_number <> X.highest_post_number OR
          topics.highest_post_number <> Y.highest_post_number OR
          topics.last_posted_at <> Y.last_posted_at OR
          topics.posts_count <> Y.posts_count OR
          topics.word_count <> Z.word_count
        )

(the other has ‘private_message’ as archetype, of course)
Which means the query’s missing
Z.topic_id = topics.id - which causes the whole geometric increase.

Changing the queries WHERE clause to

      WHERE
        topics.archetype <> 'private_message' AND
        X.topic_id = topics.id AND
        Y.topic_id = topics.id AND 
        Z.topic_id = topics.id AND 
          (
          topics.highest_staff_post_number <> X.highest_post_number OR
          topics.highest_post_number <> Y.highest_post_number OR
          topics.last_posted_at <> Y.last_posted_at OR
          topics.posts_count <> Y.posts_count OR
          topics.word_count <> Z.word_count
        )

fixed the problem for me.

Should I open a PR?

2 Likes