mirror of
https://gh.wpcy.net/https://github.com/discourse/discourse.git
synced 2026-06-18 19:31:16 +08:00
31ff0071f8
Previously, 437ab337d2 added the normalized_referrer column to the
browser_pageview_events table. That column stores the cleaned referrer
value used by the admin Top Referrers report, but existing
browser_pageview_events rows were not backfilled. Older rows still have
NULL normalized_referrer values, so historical dates can show little or
no referrer data even though the original referrer was recorded.
This PR backfills normalized_referrer for existing
browser_pageview_events rows and adds a mechanism to repeat that work if
the normalization rules change later.
Key technical changes:
1. Add the normalized_referrer_version column to the
browser_pageview_events table, which stores raw browser pageview events.
The new column records which version of the referrer normalization rules
processed each row. This gives the backfill a clear stopping condition
and lets a future rules change reprocess older rows by bumping the
version.
2. Replace the existing AggregateBrowserPageviewDailyRollups scheduled
job, which rebuilt browser pageview country and referrer daily rollups,
with the new MaintainBrowserPageviewRollups scheduled job. The new job
still keeps those rollups current, and it also backfills older
normalized referrers from the same execution path so separate jobs do
not rebuild the same rollups at the same time.
3. Backfill browser_pageview_events rows in batches so large sites do
not need to update all historical pageview events in one run. A day’s
referrer rollup is only rebuilt once every stale referrer row for that
day has been processed, so the Top Referrers report does not show
partial counts between batches.
4. Skip dates that no longer have source rows in the
browser_pageview_events table. Browser pageview events can be pruned by
cleanup, but daily rollups are the permanent report data. Before
rebuilding a date, the backfill checks that browser_pageview_events
still has events for that date. If no events remain, the existing rollup
is left untouched because the job can no longer safely reconstruct it.
66 lines
2.2 KiB
Ruby
Vendored
66 lines
2.2 KiB
Ruby
Vendored
# frozen_string_literal: true
|
|
|
|
class BrowserPageviewReferrerDailyRollup < ActiveRecord::Base
|
|
def self.aggregate(start_date:, end_date:)
|
|
DB.exec(<<~SQL, start_date: start_date.to_date, end_date: end_date.to_date + 1)
|
|
INSERT INTO browser_pageview_referrer_daily_rollups (date, normalized_referrer, count, logged_in_count)
|
|
SELECT
|
|
created_at::date AS date,
|
|
normalized_referrer,
|
|
COUNT(*) AS count,
|
|
COUNT(*) FILTER (WHERE user_id IS NOT NULL) AS logged_in_count
|
|
FROM browser_pageview_events
|
|
WHERE created_at >= :start_date
|
|
AND created_at < :end_date
|
|
GROUP BY date, normalized_referrer
|
|
ON CONFLICT (date, normalized_referrer) DO UPDATE
|
|
SET count = EXCLUDED.count,
|
|
logged_in_count = EXCLUDED.logged_in_count
|
|
SQL
|
|
end
|
|
|
|
def self.recompute(dates)
|
|
dates = Array(dates).map(&:to_date).uniq
|
|
return if dates.empty?
|
|
|
|
# The rollups are the permanent record, but their source events are pruned
|
|
# after a retention period (CleanUpBrowserPageviewEvents). Only rebuild
|
|
# dates that still have events so we never delete a rollup we can no longer
|
|
# reconstruct from events.
|
|
dates = DB.query_single(<<~SQL, dates: dates)
|
|
SELECT d.date
|
|
FROM unnest(ARRAY[:dates]::date[]) AS d(date)
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM browser_pageview_events e
|
|
WHERE e.created_at >= d.date
|
|
AND e.created_at < d.date + 1
|
|
)
|
|
SQL
|
|
return if dates.empty?
|
|
|
|
transaction do
|
|
DB.exec(<<~SQL, dates: dates)
|
|
DELETE FROM browser_pageview_referrer_daily_rollups
|
|
WHERE date IN (:dates)
|
|
SQL
|
|
|
|
dates.each { |date| aggregate(start_date: date, end_date: date) }
|
|
end
|
|
end
|
|
end
|
|
|
|
# == Schema Information
|
|
#
|
|
# Table name: browser_pageview_referrer_daily_rollups
|
|
#
|
|
# id :bigint not null, primary key
|
|
# count :bigint not null
|
|
# date :date not null
|
|
# logged_in_count :bigint not null
|
|
# normalized_referrer :string(2000)
|
|
#
|
|
# Indexes
|
|
#
|
|
# idx_bprd_rollups_date_referrer_unique (date,normalized_referrer) UNIQUE NULLS NOT DISTINCT
|
|
#
|
