I just realized that either I don’t fully understand the cases where an edit notification is created or this is a much more widespread problem than I thought.
Main assumptions
If User A edits what User B wrote, several things happen:
A new row gets added to the post_revisions table.
A new row gets added to the user_actions table with action_type = 11.
By going to /u/userB/notifications/edits, User B will be able to see that a new edit was made by User A (this depends on user_actions).
By clicking on the pencil icon in his post, User B will be able to see the actual edit that User A performed (this depends on post_revisions).
Test
If the above assumptions are correct, this query should show all the rows in the post_revisions table for posts created by User B (in this case, id 259) that were edited by any user (other than himself, or the system user), along with the corresponding rows in user_actions for action_type = 11.
with my_user_posts as (
select
p.id,
p.user_id
from
posts p
where
p.user_id = 259 -- choose a user id
)
select
up.user_id as my_user_id,
ua.user_id as target_user_id,
pr.post_id,
ua.target_post_id,
pr.user_id as editor_user_id,
ua.acting_user_id,
ua.action_type,
pr.created_at as edit_created_at,
ua.created_at as action_created_at
from
post_revisions pr
inner join my_user_posts up on up.id = pr.post_id
and up.user_id != pr.user_id -- no self edits
and pr.user_id != -1 -- no system edits
left join user_actions ua on ua.target_post_id = pr.post_id
and ua.action_type = 11 -- only EDIT actions
order by
pr.post_id,
pr.created_at;
Expected output
Every row has both post_revisions data along with user_actions data.
Actual output
Some of the post_revisions rows doesn’t have matching user_actions data. Therefore, the user can see the revisions by clicking on the pencil in each post, but wasn’t notified about receiving several edits.
Adding an additional edit to an old post without user_action data. Result: the user_action data also did not appear.
Creating a fake user, copying the pre-edit content of a post without user_action data, creating a post with it and applying the same edit that was done with a different user. Result: the user_action data appeared correctly.
Repeating the above procedures when the user is active or offline. Result: no change.
Repeating the above procedures changing the editing grace period. Result: no change.
Conclusions
The issue doesn’t appear to be:
user-specific. It happens to practically every user.
connection-specific. The fact that the user is active or offline doesn’t change the output.
time-specific. Changing the editing grace period had no effect.
The issue appears to be
action-specific. I have not seen any issues notifying any of the other actions (LIKE, WAS_LIKED, RESPONSE, REPLY, MENTION or QUOTE). The only problem is with EDIT actions.
post-specific. It doesn’t happen to every post, only specific ones (seemingly at random)
One possibility is that something is happening during the creation of specific posts to prevent EDIT user_actions from being saved, but I have no idea what this could be.
It’s also possible that this is happening by design and that there are specific conditions under which users are not notified of edits, but I haven’t seen this documented anywhere.
Next steps
If you know a reason why edit notifications might not get triggered every time there is an edit, please let me know.
If you have your own Discourse instance, could you run the above SQL query on some of your user ids to see if you also see missing user_actions data and report back?
(Yes, if user A edits user B’s post, there is always a forced edit revision, but that doesn’t mean that if user A edits user B’s post 6 times in 60 seconds, there are 6 revisions and 6 notifications created. There will be only one revision and one notification, as you can see in the above screenshot.)
Are each of these edits more than 5 minutes apart?
with my_user_posts as (
select
p.id,
p.user_id
from
posts p
where
p.user_id = 3 -- 选择用户 ID
)
select
up.user_id as my_user_id,
ua.user_id as target_user_id,
pr.post_id,
ua.target_post_id,
pr.user_id as editor_user_id,
ua.acting_user_id,
ua.action_type,
pr.created_at as edit_created_at,
ua.created_at as action_created_at
from
post_revisions pr
inner join my_user_posts up on up.id = pr.post_id
and up.user_id != pr.user_id -- 排除自编辑
and pr.user_id != -1 -- 排除系统编辑
left join user_actions ua on ua.target_post_id = pr.post_id
and ua.action_type = 11 -- 仅编辑操作
WHERE
pr.created_at between '2019-08-03' and '2019-08-04'
order by
pr.post_id,
pr.created_at
