MW 1.30/1.31 on Postgres, tests fail with "Error: 23505 ERROR: duplicate key value violates unique constraint ..."

[mwjames]

Setup and configuration

• SMW version: any
• MW version:1.31 (most likely 1.30)
• PHP version: 7.1
• DB system (MySQL, Blazegraph, etc.) and version: postgres

Issue

Using PHP 7.1.1

Semantic MediaWiki: 3.0.0-alpha, efe7b5a, {"store":"SMWSQLStore3","db":"postgres"}
MediaWiki:          1.31.0-alpha, c909231, MediaWiki vendor autoloader
Site language:      en

Execution time:     2017-12-23 09:41
Debug logs:         Disabled
Xdebug:             Disabled (or not installed)

PHPUnit 4.8.36 by Sebastian Bergmann and contributors.

Runtime:	PHP 7.1.1

1) SMW\Tests\Integration\MediaWiki\Import\Maintenance\RebuildDataMaintenanceTest::testRebuildData
Wikimedia\Rdbms\DBQueryError: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: INSERT INTO "sunittest_smw_object_ids" (smw_id,smw_title,smw_namespace,smw_iw,smw_subobject,smw_sortkey,smw_sort) VALUES (DEFAULT,'SMWUTDummyPage','0','','','SMWUTDummyPage','SMWUTDummyPage')
Function: SMWSql3SmwIds::makeDatabaseId
Error: 23505 ERROR:  duplicate key value violates unique constraint "sunittest_smw_object_ids_pkey"
DETAIL:  Key (smw_id)=(1) already exists.


...\includes\libs\rdbms\database\Database.php:1185
...\includes\libs\rdbms\database\DatabasePostgres.php:262
...\includes\libs\rdbms\database\Database.php:997
...\includes\libs\rdbms\database\DatabasePostgres.php:665
...\extensions\SemanticMediaWiki\src\MediaWiki\Database.php:432
...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_Sql3SmwIds.php:731
...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_Sql3SmwIds.php:677
...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3_Writers.php:255
...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3_Writers.php:173
...\extensions\SemanticMediaWiki\includes\storage\SQLStore\SMW_SQLStore3.php:261
...\extensions\SemanticMediaWiki\includes\storage\SMW_Store.php:220
...\extensions\SemanticMediaWiki\src\Updater\StoreUpdater.php:271
...\extensions\SemanticMediaWiki\src\Updater\StoreUpdater.php:170
...\extensions\SemanticMediaWiki\src\Updater\StoreUpdater.php:126
...\extensions\SemanticMediaWiki\src\ParserData.php:452
...\extensions\SemanticMediaWiki\src\Updater\DeferredCallableUpdate.php:236
...\extensions\SemanticMediaWiki\src\Updater\DeferredTransactionalUpdate.php:134
...\extensions\SemanticMediaWiki\src\Updater\DeferredCallableUpdate.php:265
...\extensions\SemanticMediaWiki\src\ParserData.php:468
...\extensions\SemanticMediaWiki\src\MediaWiki\Jobs\UpdateJob.php:276
...\extensions\SemanticMediaWiki\src\MediaWiki\Jobs\UpdateJob.php:218
...\extensions\SemanticMediaWiki\src\MediaWiki\Jobs\UpdateJob.php:134
...\extensions\SemanticMediaWiki\src\MediaWiki\Jobs\UpdateJob.php:92
...\extensions\SemanticMediaWiki\src\SQLStore\EntityRebuildDispatcher.php:204
...\extensions\SemanticMediaWiki\src\Maintenance\DataRebuilder.php:308
...\extensions\SemanticMediaWiki\src\Maintenance\DataRebuilder.php:267
...\extensions\SemanticMediaWiki\src\Maintenance\DataRebuilder.php:168
...\extensions\SemanticMediaWiki\maintenance\rebuildData.php:156
...\extensions\SemanticMediaWiki\tests\phpunit\Utils\Runners\MaintenanceRunner.php:96
...\extensions\SemanticMediaWiki\tests\phpunit\Integration\MediaWiki\Import\Maintenance\RebuildDataMaintenanceTest.php:127
...\extensions\SemanticMediaWiki\tests\phpunit\Integration\MediaWiki\Import\Maintenance\RebuildDataMaintenanceTest.php:102
...\extensions\SemanticMediaWiki\tests\phpunit\MwDBaseUnitTestCase.php:131
...\maintenance\doMaintenance.php:94

Cause

I raised concerns in May 2017 [0] about possible ramifications by removing Database::nextSequenceValue and now that we have a reproducible failure, the conclusion is that [1] causes the test failure.

$sequenceValue = $db->nextSequenceValue( $this->getIdTable() . '_smw_id_seq' ); // Bug 42659

// #2089 (MySQL 5.7 complained with "Data too long for column")
$sortkey = mb_substr( $sortkey, 0, 254 );

$db->insert(
	self::TABLE_NAME,
	array(
		'smw_id' => $sequenceValue,
		'smw_title' => $title,
		'smw_namespace' => $namespace,
		'smw_iw' => $iw,
		'smw_subobject' => $subobjectName,
		'smw_sortkey' => $sortkey,
		'smw_sort' => Collator::singleton()->getSortKey( $sortkey )
	),
	__METHOD__
);

$id = (int)$db->insertId();

Re-adding the specific PostgreSQL code to the nextSequenceValue method allows to pass the test again as it was the case before the [1] changes. Given that neither the test nor any other part has been modified, it is very likely that [1] causing some unexpected side effects.

+	 	// https://github.com/wikimedia/mediawiki/commit/0a9c55bfd39e22828f2d152ab71789cef3b0897c#diff-278465351b7c14bbcadac82036080e9f
+		$safeseq = str_replace( "'", "''", $seqName );
+		$res = $this->query( "SELECT nextval('$safeseq')" );
+		$row = $this->readConnection()->fetchRow( $res );
+
+		return is_null( $row[0] ) ? null : (int)$row[0];

[0] https://phabricator.wikimedia.org/T164900#3251932
[1] wikimedia/mediawiki@0a9c55b#diff-278465351b7c14bbcadac82036080e9f