- match post_update
- .set((
- post_aggregates::hot_rank.eq(hot_rank(post_aggregates::score, post_aggregates::published)),
- post_aggregates::hot_rank_active.eq(hot_rank(
- post_aggregates::score,
- post_aggregates::newest_comment_time_necro,
- )),
+#[derive(QueryableByName)]
+struct HotRanksUpdateResult {
+ #[diesel(sql_type = Timestamp)]
+ published: NaiveDateTime,
+}
+
+/// Runs the hot rank update query in batches until all rows have been processed.
+/// In `where_clause` and `set_clause`, "a" will refer to the current aggregates table.
+/// Locked rows are skipped in order to prevent deadlocks (they will likely get updated on the next
+/// run)
+fn process_hot_ranks_in_batches(
+ conn: &mut PgConnection,
+ table_name: &str,
+ where_clause: &str,
+ set_clause: &str,
+) {
+ let process_start_time = NaiveDateTime::from_timestamp_opt(0, 0).expect("0 timestamp creation");
+
+ let update_batch_size = 1000; // Bigger batches than this tend to cause seq scans
+ let mut processed_rows_count = 0;
+ let mut previous_batch_result = Some(process_start_time);
+ while let Some(previous_batch_last_published) = previous_batch_result {
+ // Raw `sql_query` is used as a performance optimization - Diesel does not support doing this
+ // in a single query (neither as a CTE, nor using a subquery)
+ let result = sql_query(format!(
+ r#"WITH batch AS (SELECT a.id
+ FROM {aggregates_table} a
+ WHERE a.published > $1 AND ({where_clause})
+ ORDER BY a.published
+ LIMIT $2
+ FOR UPDATE SKIP LOCKED)
+ UPDATE {aggregates_table} a {set_clause}
+ FROM batch WHERE a.id = batch.id RETURNING a.published;
+ "#,
+ aggregates_table = table_name,
+ set_clause = set_clause,
+ where_clause = where_clause