We have also considered implementing a community configurable threshold for this. Looking at other anti-vandalism bots:

• Salebot only ever reverts the same user once per article, unless another contributor also reverted them there in the meantime.
• ChenzwBot only reverts a user once per article.
• ClueBot NG will not revert the same user/page combination more than once per day.

We could imagine a configurable number for 'how many times to revert the same user on the same page'. Alternatively we could just take our lead from these bots and hardcode this value at 1.

@Ladsgroup

Hello! I was reaching out to you to get your feedback around the query that is being generated by the newSelectQueryBuilder in this patch and wanted to get your opinion around if you think it would create performance issues.

The query is to retrieve how many times a user has been reverted by AutoModerator on a page in the past 24 hours so that we can skip reverting the user if it has exceeded some configurable threshold. This query will fire from the onRevisionFromEditComplete hook.

$this->dbr->newSelectQueryBuilder()
			->select( [ 'COUNT(*)' ] )
			->from( 'revision', 'child' )
			->join( "revision", "parent", [ 'child.rev_parent_id=parent.rev_id' ] )
			->join( "actor", 'childActor', [ 'child.rev_actor=childActor.actor_id' ] )
			->join( "actor", "parentActor", [ 'parent.rev_actor=parentActor.actor_id' ] )
			->where( [
				'child.rev_page' => $this->wikiPageId,
				'parent.rev_page' => $this->wikiPageId,
				'childActor.actor_user' => $this->autoModeratorUser->getId(),
				'parentActor.actor_user' => $this->user->getId(),
				$this->dbr->expr( 'parent.rev_timestamp', '>', strtotime( "-1 day" ) ),
				$this->dbr->expr( 'child.rev_timestamp', '>', strtotime( "-1 day" ) ),
			] )->caller( __METHOD__ )->fetchField();

which generates the following sql:

SELECT  COUNT(*)
FROM `revision` `child` JOIN `revision` `parent` ON ((child.rev_parent_id=parent.rev_id))
    JOIN `actor` `childActor` ON ((child.rev_actor=childActor.actor_id))
    JOIN `actor` `parentActor` ON ((parent.rev_actor=parentActor.actor_id))
WHERE child.rev_page = 1 AND parent.rev_page = 1 AND childActor.actor_user = 3
  AND parentActor.actor_user = 109 AND (parent.rev_timestamp > 1728412702)
  AND (child.rev_timestamp > 1728412702);

Thanks! I currently do not have access to the stats server to test this query out with, but will be requesting access.

Hi, A couple of notes:

• This can get slow for various reasons, I suggested generally maybe move the whole hook into a job?
• Please make sure this is last resort. e.g. if the edit doesn't need to be reverted, if the user is auto patrolled or other rights, short circuit it. That way it's removed from the critical path.
• Don't query the master.
• Query recentchanges table instead. The chance of things go wrong is smaller and it would satisfy your needs since you're only looking for edits in the past 24 hours.
• Maybe use an abstraction from core, I know for querying revision table, there is RevisionStore and RevisionSelectQueryBuilder. Maybe something like that for rc table exists too, you can add it to core. That way, if we need to do schema improvements on rc table, it'd be less work for us.
• Another way that this can be done faster is to check if any edit is done by auto moderator bot in the past 24 hours, if so, then do the complex query. That way, you short circuit almost all cases out already.
• Once what I suggested above is done, so only a handful of edits a day would need to be checked, a rather simpler approach can be to write a query that load all revisions in the past 24 hours (not content of revisions, just parent_id, actor_id, etc.) and just iterate over it. I don't think that'd be expensive to do and it'd be much easier to understand a complex query. It also has less chance of mariadb optimizer having bugs and deciding to scan the whole revision table instead.
  • This is mostly a suggestion though.

HTH

@Ladsgroup thank you, this is very helpful!

Change #1070260 had a related patch set uploaded (by Scardenasmolinar; author: Kgraessle):

[mediawiki/extensions/AutoModerator@master] Implement a limit and configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1070260

Change #1083888 had a related patch set uploaded (by Kgraessle; author: Kgraessle):

[mediawiki/extensions/AutoModerator@master] Translations for configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1083888

Change #1070260 had a related patch set uploaded (by Scardenasmolinar; author: Kgraessle):

[mediawiki/extensions/AutoModerator@master] Implement a limit and configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1070260

Change #1070260 had a related patch set uploaded (by Scardenasmolinar; author: Kgraessle):

[mediawiki/extensions/AutoModerator@master] Implement a limit and configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1070260

Change #1070260 had a related patch set uploaded (by Kgraessle; author: Kgraessle):

[mediawiki/extensions/AutoModerator@master] Implement a limit and configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1070260

Change #1083888 merged by jenkins-bot:

[mediawiki/extensions/AutoModerator@master] Translations for configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1083888

Change #1084889 had a related patch set uploaded (by Jsn.sherman; author: Kgraessle):

[mediawiki/extensions/AutoModerator@wmf/1.44.0-wmf.1] Translations for configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1084889

Change #1084889 merged by jenkins-bot:

[mediawiki/extensions/AutoModerator@wmf/1.44.0-wmf.1] Translations for configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1084889

Mentioned in SAL (#wikimedia-operations) [2024-10-31T20:28:16Z] <jsn@deploy2002> Started scap sync-world: Backport for [[gerrit:1084889|Translations for configuration for same-user-same-page reverts in Automoderator (T370795)]], [[gerrit:1084891|Add follow-up message (T372476)]]

Mentioned in SAL (#wikimedia-operations) [2024-10-31T20:46:10Z] <jsn@deploy2002> jsn: Backport for [[gerrit:1084889|Translations for configuration for same-user-same-page reverts in Automoderator (T370795)]], [[gerrit:1084891|Add follow-up message (T372476)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Mentioned in SAL (#wikimedia-operations) [2024-10-31T20:55:27Z] <jsn@deploy2002> Finished scap sync-world: Backport for [[gerrit:1084889|Translations for configuration for same-user-same-page reverts in Automoderator (T370795)]], [[gerrit:1084891|Add follow-up message (T372476)]] (duration: 27m 10s)

The messages:

• communityconfiguration-automoderator-automoderatoruserrevertsperpage-label
• communityconfiguration-automoderator-automoderatoruserrevertsperpage-description
• communityconfiguration-automoderator-automoderatorenableuserrevertsperpage-label
• communityconfiguration-automoderator-automoderatorenableuserrevertsperpage-control-label
• automoderator-config-validator-user-reverts-per-page-not-number

have been backported; these should be translated on wikitranslate as soon as they are available there

Change #1070260 merged by jenkins-bot:

[mediawiki/extensions/AutoModerator@master] Implement a limit and configuration for same-user-same-page reverts in Automoderator

https://gerrit.wikimedia.org/r/1070260

This seems to be working as expected on test wiki. See https://test.wikipedia.org/w/index.php?title=Episcopal_Diocese_of_Hawaii&action=history

I set the community config param AutoModeratorUserRevertsPerPage to 1 and did not see any more reverts kick off.

@Bonaditya This feature is ready if you would like to enable it.

See configuration here.

In T370795#10345511, @Kgraessle wrote:

@Bonaditya This feature is ready if you would like to enable it.

See configuration here.

@BAPerdana-WMF :)

Oop sorry about that! Thanks @Samwalton9-WMF

In T370795#10345615, @Samwalton9-WMF wrote:

In T370795#10345511, @Kgraessle wrote:

@Bonaditya This feature is ready if you would like to enable it.

See configuration here.

@BAPerdana-WMF :)

Done! :)