Notifications being incorrectly created for old posts

Support
1

Yesterday my site started generating notifications for old posts out of the blue. This is the number of ‘posted’ notifications (notification_type = 9) created later than an hour in the past few days:

image

Query I used to generate the table 
WITH pre AS (
	SELECT
		p.id as post_id,
		to_char(n.created_at,
			'YYYY-MM-DD') AS notified_day,
		p.topic_id,
		round(least(EXTRACT(EPOCH FROM n.created_at - p.created_at)::numeric, EXTRACT(EPOCH FROM n.created_at - p.updated_at)::numeric) / 3600)  AS hours_diff,
		p.created_at,
		p.updated_at,
		n.created_at,
		u.username
	FROM
		notifications n
	LEFT JOIN posts p ON p.post_number = n.post_number
		AND p.topic_id = n.topic_id
	left join users u on u.id = n.user_id
WHERE
	round(least(EXTRACT(EPOCH FROM n.created_at - p.created_at)::numeric, EXTRACT(EPOCH FROM n.created_at - p.updated_at)::numeric) / 3600) > 0
	AND n.notification_type = 9
ORDER BY
	n.created_at DESC
LIMIT 400
)
SELECT
	count(*), 
	notified_day
FROM
	pre
GROUP BY
	notified_day
ORDER BY
	notified_day DESC

Here are a few examples (from running the CTE in the query above):

image
image1796×576 87.2 KB

The first highlighted row was created on December 8 but a user who is watching that category received a notification on December 25 (and from what other users have told me, they are being notified about posts they had already seen).

The second highlighted row was created on September 29, modified a day later and notified on December 25.

I just rebuilt the container so I’m running the latest version of Discourse. For now I’ve disabled email notifications.

Any ideas on how to troubleshoot this? I assume it’s either a queue that got backed up for some reason or something like that. Is there a way to find out what is generating these notifications?

1 Like
2

My guess is that these are sidekiq jobs that failed in the past for some reason and that you “fixed” something so that those notifications are going out now rather than when they were supposed to. You can look at /sidekiq and See if there are now that you can delete before they are processed.

2 Likes
3

That’s a great guess, Jay. I can see an uptick for the past few days when users have reported the problem:

image
image2546×710 175 KB

I’ve checked the scheduled queue and it doesn’t seem to have a long unprocessed queue of messages. Rather, a few dozen of these stale notifications seem to get added every 10 minutes or so.

Is it possible to track down what might be sending these to Sidekiq?

1 Like
4

The issue seemed to get solved by itself (luckily) after a few days, but digging deeper helped me realize my initial SQL query was incomplete: the notifications.post_number field points to the first unread post in a given topic but every time a new post is created, its notification.created_at is updated to the new posts.created_at.

That explained some of the time discrepancies I was seeing, but not the uptick that mysteriously appeared and went away.

To fix the query, I’d have to first check post_timings to find the latest topic_id/post_number that the given user had seen, and use its created_at instead of the one coming from notifications.

1 Like