Marking as perf recommendation for the additional queries required currently.

To reduce the overhead of this in the interim, @aaron added page.page_restrictions to LinkCache (see 93b24207c1cb / https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/496391/).

If the column isn't in used, I would prefer if we can remove it from tables.sql, merge it, and then get a ticket to get the schema change done in production (it doesn't have to be done right away, but at least created so we have it as a backlog).
Removing DBA (but I will remain subscribed) as there is not actionable for us here yet (until the schema change in prod needs to actually be done).

Thank you!

Moving to Backlog, but should be broken into tickets for each task before we work on this.

Change 537353 had a related patch set uploaded (by TK-999; owner: TK-999):
[mediawiki/core@master] Refactor the updateRestrictions.php maintenance script

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

@WDoranWMF This was initially set for later I believe, but given Wikia has started work on it for us, perhaps CR could be provided earlier. Just leaving this here for now :)

In T218446#5536013, @Krinkle wrote:

@WDoranWMF This was initially set for later I believe, but given Wikia has started work on it for us, perhaps CR could be provided earlier. Just leaving this here for now :)

Just to clarify, I contributed this patch in a volunteer capacity :) If in the future I submit a patch as a direct outcome of my work, I will indicate it as appropriate on the relevant ticket and the patch itself.

Change 537353 merged by jenkins-bot:
[mediawiki/core@master] Refactor the updateRestrictions.php maintenance script

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

Ticked off the "maintenance script" and "run by installer" boxes. The above patch fixes the maintenance script. And the installer seems to already be running it as well. E.g. MysqlUpdater::doRestrictionsUpdate since MW 1.10.

Next step unlocked: Run the maintenance script on all WMF wikis.

Change 541601 had a related patch set uploaded (by TK-999; owner: TK-999):
[mediawiki/core@master] [DNM] Remove usages of legacy page.page_restrictions field

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

Change 581141 had a related patch set uploaded (by TK-999; owner: TK-999):
[mediawiki/core@master] Add schema change to make page.page_restrictions column nullable

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

Change 581141 merged by jenkins-bot:
[mediawiki/core@master] Add schema change to make page.page_restrictions column nullable

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

Moving to "ready", since the next step seems to be "Run the maintenance script on all WMF wikis."

@Krinkle I don't see a second query being executed by Title::getRestrictions(). Has that been removed since the ticket was created?

@holger.knust Looking at the code now, I would expect the query happen via loadRestrictions → loadRestrictionsFromRows → LinkCache:

	public function loadRestrictions( $oldFashionedRestrictions =,  … ) {
		// …
		$loadRestrictionsFromDb = function ( IDatabase $dbr ) use ( $fname, $id ) {
			return iterator_to_array(
				$dbr->select(
					'page_restrictions',
					[ 'pr_type', 'pr_expiry', 'pr_level', 'pr_cascade' ],
					[ 'pr_page' => $id ],
					$fname
				)
			);
		};
		// …
		$rows = $loadRestrictionsFromDb( $dbr );
		$this->loadRestrictionsFromRows( $rows, $oldFashionedRestrictions );
	}

	public function loadRestrictionsFromRows( $rows, $oldFashionedRestrictions = null ) {
		// …
		if ( $this->mOldRestrictions === false ) { # always
			$linkCache = MediaWikiServices::getInstance()->getLinkCache();
			$linkCache->addLinkObj( $this ); # <!-- HERE
			$this->mOldRestrictions = $linkCache->getGoodLinkFieldObj( $this, 'restrictions' );
		}
		// …
	}

The LinkCache class then queries for page.page_protected as-needed. This code changed after this task was filed, in fact on the same day in 2019 (change 496391). Before that change, it was a direct sql query.

This change also made it so that the legacy page.page_restrictions field is in the default SELECT fields for the title cache, which means probably in most cases it no longer requires its own dedicated query, instead we load it upfront for all page table queries, which I guess was made sense as a trade-off since it's usually an empty string.

Mentioned in SAL (#wikimedia-operations) [2022-05-09T06:23:43Z] <Amir1> start of updateRestrictions.php on s5 (T218446)

Change 790257 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/extensions/Wikibase@master] tests: Remove references to legacy page_restrictions field in page table

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

Change 790258 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@master] Remove references to legacy page_restrictions field in page table

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

Change 790259 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/extensions/Babel@master] tests: Remove references to legacy page_restrictions field in page table

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

Change 790257 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@master] tests: Remove references to legacy page_restrictions field in page table

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

Change 790258 merged by jenkins-bot:

[mediawiki/core@master] Remove references to legacy page_restrictions field in page table

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

Change 790259 merged by jenkins-bot:

[mediawiki/extensions/Babel@master] tests: Remove references to legacy page_restrictions field in page table

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

I will let it deploy next week and then I move on to create the schema change patch.