Wikimedia Bugzilla is closed!

Created attachment 11457 [details]
Possible fixes for few postgres problems

Thanks for your effort.

We would really appreciate if you could make a gerrit commit [0] because that would help use to review the code through the appropriate channel.

We really like to support PostgreSQL in a more meaningful way but most of the involved developers don't have access to PostgreSQL (I'm guessing here) therefore we depend on people that can push things forward and actually test those changes through git/gerrit.

The gain access to gerrit, please see [1].

[0] http://www.mediawiki.org/wiki/Gerrit

[1] http://www.mediawiki.org/wiki/Developer_access

I'm sorry, I won't have time to take a look at it myself for next month or two. The patch was meant more like a hint where some of the issues are, not a proper fix.

What do you mean by developers not having access? PostgreSQL is free, is packaged in all major distros and even has a windows installer.

Anyway, can SMW documentation at least indicate that PostgreSQL is unsupported?

Access to ... means for someone to have a PostgreSQL installed base available to verify that your patch and SMW is working.

If someone wanted to apply your patch, he/she needs an installation that uses PostgreSQL and not MySQL together with someone who has PostgreSQL knowledge otherwise we are unlikely to make it work.

SMW is community work, anyone can edit respective resources or documentation.

Alright, I added a note to http://semantic-mediawiki.org/wiki/PostgreSQL.

The patch is really just a start. In principal there are currently the following problems with SMW and PostgreSQL:

* Indexes and sequences must be unique over a database (even though they are defined on a table). This requires changes in makeDatabaseId (SMW_Sql3SmwIds.php) and setupPredefinedProperties (SMW_SQLStore3_SetupHandlers.php) when handling sequences.
* PostgreSQL doesn't like '"tableName"' which makes it necessary to use rawTableName in some places, when building the name of an index for example or when looking for the list of indizes (getIndexInfo).
* Furthermore, when looking for indizes (getIndexInfo), the filter has to include the type of the index since some index must be UNIQUE on PostgreSQL (see patch by b42) but the getIndexInfo is not per-index but only per-table

Note: this patch could not be applied cleanly to the current master.

Another user reporting this on #mediawiki :


< LocalSettings.php
include_once("$IP/extensions/AdminLinks/AdminLinks.php");
require_once( "$IP/extensions/Validator/Validator.php" );
require_once( "$IP/extensions/DataValues/DataValues.php" );
include_once( "$IP/extensions/SemanticMediaWiki/SemanticMediaWiki.php" );
  $smwgQSubcategoryDepth=0;
  $smwgQPropertyDepth=0;
  $smwgQFeatures        = SMW_ANY_QUERY & ~SMW_DISJUNCTION_QUERY;
  $smwgQConceptFeatures = SMW_ANY_QUERY & ~SMW_DISJUNCTION_QUERY &
                          ~SMW_CONCEPT_QUERY;
enableSemantics('localhost/wiki');
include_once("$IP/extensions/SemanticForms/SemanticForms.php");
require_once( "$IP/extensions/Maps/Maps.php" );
require_once( "$IP/extensions/SemanticMaps/SemanticMaps.php" );

-> Main_Page

Database error
Dtabase  query syntax error has occurred. This may indicate a bug in the  software. The last attempted database query was: (SQL query hidden) from  within function "SMWSql3SmwIds::getDatabaseIdAndSort". Database  returned error "42P01: ERROR:  relation "smw_object_ids" does not exist  LINE 1: ...abaseIdAndSort Crima */  smw_id,smw_sortkey  FROM  "smw_objec...                                                               ^ ".


$ $ php maintenance/update.php

Setting up standard database configuration for SMW ...

Selected storage engine is "SMWSQLStore3" (or an extension thereof)

Checking table "smw_object_ids" ... 
   Table not found, now creating...
   ... done.
A database query syntax error has occurred.
The last attempted database query was:
"CREATE INDEX "smw_object_ids" ON "smw_object_ids" USING btree(smw_id)"
from within function "SMWSQLHelpers::createIndex".
Database returned error "42P07: ERROR:  relation "smw_object_ids" already exists


-> Special:SMWAdmin

 \-> initialize
 
 
Setting up standard database configuration for SMW ...

Selected storage engine is "SMWSQLStore3" (or an extension thereof)

Checking table "smw_object_ids" ...
   Table not found, now creating...
   ... done.
   
 \-> Data repair and upgrade

Setting up standard database configuration for SMW ...

Selected storage engine is "SMWSQLStore3" (or an extension thereof)

Checking table "smw_object_ids" ...
   Table not found, now creating...
   ... done.

Checking table "smw_object_ids" ...
   Table not found, now creating...
   ... done.
A database query syntax error has occurred.
The last attempted database query was:
"CREATE INDEX "smw_object_ids" ON "smw_object_ids" USING btree(smw_id)"
from within function "SMWSQLHelpers::createIndex".
Database returned error "42P07: ERROR:  relation "smw_object_ids" already exists
"

There is a commit related to this on gerrit: https://gerrit.wikimedia.org/r/#/c/88459/

Change 91375 had a related patch set uploaded by saper:
Sort out index creation for PostgreSQL

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

(In reply to comment #6)
> The patch is really just a start. In principal there are currently the
> following problems with SMW and PostgreSQL:
> 
> * Indexes and sequences must be unique over a database (even though they are
> defined on a table). This requires changes in makeDatabaseId
> (SMW_Sql3SmwIds.php) and setupPredefinedProperties
> (SMW_SQLStore3_SetupHandlers.php) when handling sequences.
> * PostgreSQL doesn't like '"tableName"' which makes it necessary to use
> rawTableName in some places, when building the name of an index for example
> or
> when looking for the list of indizes (getIndexInfo).
> * Furthermore, when looking for indizes (getIndexInfo), the filter has to
> include the type of the index since some index must be UNIQUE on PostgreSQL
> (see patch by b42) but the getIndexInfo is not per-index but only per-table

I think above points are addressed by gerrit change Ife21fd09aa6d0c74241dbd7b9130b10292f306ac

(In reply to comment #11)
> (In reply to comment #6)
> > The patch is really just a start. In principal there are currently the
> > following problems with SMW and PostgreSQL:
> > 
> > * Indexes and sequences must be unique over a database (even though they are
> > defined on a table). This requires changes in makeDatabaseId
> > (SMW_Sql3SmwIds.php) and setupPredefinedProperties
> > (SMW_SQLStore3_SetupHandlers.php) when handling sequences.
> > * PostgreSQL doesn't like '"tableName"' which makes it necessary to use
> > rawTableName in some places, when building the name of an index for example
> > or
> > when looking for the list of indizes (getIndexInfo).
> > * Furthermore, when looking for indizes (getIndexInfo), the filter has to
> > include the type of the index since some index must be UNIQUE on PostgreSQL
> > (see patch by b42) but the getIndexInfo is not per-index but only per-table
> 
> I think above points are addressed by gerrit change
> Ife21fd09aa6d0c74241dbd7b9130b10292f306ac


I meant I2d2cb63331d5ed785295d973b7f3124e1c289b53, sorry

Conclusion database can be created with the dumped sql stataments and using not name or the generated name: smw_object_ids_smw_id_idx
Table in mediawiki database fails to be created because there is a name collision in the sql statament: index name is the same that the name of the table.

Admin functions for Semantic MediaWiki;Database installation and upgrade
Initialise or upgrade:
"Setting up standard database configuration for SMW ...
Selected storage engine is "SMWSQLStore" (or an extension thereof)
Checking table "smw_object_ids" ...
   Table not found, now creating...
   ... done"

However database is not created:

Table in mediawiki database fails to be created because there is a name collision in the sql statament: 
Debug info{
tail /var/log/postgresql 
 GMT ERROR:  relation "smw_object_ids" already exists
 GMT STATEMENT:  CREATE /* SMWSQLHelpers::createIndex Omr */ INDEX

Looking at SMW SQL statements 
add the following variables to LocalSettings.php:

$wgDebugLogFile = "/path/to/log/file/debug.log";
$wgDebugDumpSql = true;
// http://semantic-mediawiki.org/wiki/Semantic_MediaWiki_database_tables#Looking_at_SMW_SQL_statements
//

CREATE TABLE "smw_object_ids" (smw_id  SERIAL NOT NULL PRIMARY KEY,smw_namespace  BIGINT NOT NULL,smw_title  TEXT NOT NULL,smw_iw  TEXT NOT NULL,smw_subobject  TEXT NOT NULL,smw_sortkey  TEXT NOT NULL,smw_proptab
le_hash  BYTEA) 

CREATE /* SMWSQLHelpers::createIndex Omr */ INDEX "smw_object_ids" ON "smw_object_ids" USING btree(smw_id)

This causes the name collision:  GMT ERROR:  relation "smw_object_ids" already exists  and therefore  Database fails to be created.

http://www.postgresql.org/docs/9.1/static/sql-createindex.html

CREATE INDEX -- define a new index 

The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table. If the name is omitted, PostgreSQL chooses a suitable name based on the parent table's name and the indexed column name(s).

Then 

 CREATE   INDEX "smw_object_ids" ON "smw_object_ids" USING btree(smw_id);

Causes postgres to name the index as: smw_object_ids_smw_id_idx
Pgadmin:
:-- Index: smw_object_ids_smw_id_idx

-- DROP INDEX smw_object_ids_smw_id_idx;

CREATE INDEX smw_object_ids_smw_id_idx
  ON smw_object_ids
  USING btree
  (smw_id );

Conclusion database can be created with the dumped sql stataments and using not name or the generated name: smw_object_ids_smw_id_idx

Yes, somebody moving the code from SQLStore2 to SQLStore3 did rename the table from "smw_ids" to "smw_object_ids" but left the ("smw_ids_smw_id_seq"). Easy to fix, but code review takes time.

Uising "php ./SMW_setup.php",  These are my findings so far:

  

Initial Problem:  relation already exists:

File:

SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php

 Supplied index name is the same the table name causing the whole postgres data base creation for SMW. to fail.

if ( $db->indexInfo( $tableName, $indexName ) === false ) {
	 $db->query( "CREATE $type $tableName ON $tableName USING btree($columns)", __METHOD__ );
 	}

However original  parameter: $indexName, seems to be processed  into a name that it is the same that the table name, causing the whole postgres data base creation for SMW. to fail.

Assigning  a null string value to the index name value,  causes postgres to generate a valid index name. Then tables seems to be created correctly.

$noindexname="";
$db->query( "CREATE $type $noindexname ON $tableName USING btree($columns)", __METHOD__ );



However post creation db update fails.


Function
 public static function setupIndex( $rawTableName, array $indexes, $db, $reportTo = null )
 suplied index name is the same that table 
BY: 
    public static function setupIndex( $rawTableName, array $indexes, $db, $reportTo = null )
AS & TO: 
    self::createIndex( $db, $type, "{$tableName}_index{$key}", $tableName, $columns, $reportTo );
IN LINES:
     if ( $db->indexInfo( $tableName, $indexName ) === false ) {
	 $db->query( "CREATE $type $tableName ON $tableName USING btree($columns)", __METHOD__ );
 	}
      


Next problem: 
relation "smw_ids_smw_id_seq" does not exist

FILE:
SemanticMediaWiki/includes/storage/SQLStore/SMW_Sql3SmwIds.php

“smw_ids_smw_id_seq”  renamed as  “smw_object_ids_smw_id_seq”

Thanks to  “Comment #14 from Marcin Cieślak”




Problem:

ERROR:  syntax error at or near ")"
 ...refreshPropertyStatistics  */ FROM "smw_prop_stats" WHERE  )

File:
SemanticMediaWiki/includes/storage/SQLStore/SMW_SQLStore3_SetupHandlers.php 

$dbw->replace(
				SMWSQLStore3::PROPERTY_STATISTICS_TABLE,
				'p_id',
				array (
					'p_id' => $row->smw_id,
					'usage_count' => $usageCount
				),
				__METHOD__
			);


Causes function Repalce in file: mediawiki-1.21.3/includes/db/Database.php
to generate a postgres sql syntactic error;
 ERROR:  syntax error at or near ")"
 ...refreshPropertyStatistics  */ FROM "smw_prop_stats" WHERE  )

Summary of changes that seems to make SMW work with PostgreSQL 9.1 on Debian Wheezy using  “php ./SMW_setup.php”.
 

Change n.1 to solve problem: name “relation "smw_object_ids" already exists”
 

$noindexname="";
$db->query( "CREATE $type $noindexname ON $tableName USING btree($columns)", __METHOD__ );

to lines 494-495 in  SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php

Changes in patch provided by b42 solves this problem as well. But postgres will manage index name generation if not name is provided. This seems less complex.



Change n.2 To solve: relation "smw_ids_smw_id_seq" does not exist

“smw_ids_smw_id_seq”  renamed as  “smw_object_ids_smw_id_seq” in

SemanticMediaWiki/includes/storage/SQLStore/SMW_Sql3SmwIds.php.

Thanks to  “Comment #14 from Marcin Cieślak”

Changes in path provided by b42 solve this problem. But following on “Comment #14 from Marcin Cieślak” it seems that renaming  will make it consistent with the table name, easier to follow.


Change n.3 To solve:  ERROR:  syntax error at or near ")"
 ...refreshPropertyStatistics  */ FROM "smw_prop_stats" WHERE  )

As per mediawiki-1.21.3/includes/db/Database.php docs second parameter is an array. Therefore  'p_id' is changed to array('p_id').

In line 127 of  the file:  SemanticMediaWiki/includes/storage/SQLStore/SMW_SQLStore3_SetupHandlers.php 

After the changes it will be as follows.

$dbw->replace(
				SMWSQLStore3::PROPERTY_STATISTICS_TABLE,
				array ('p_id'),
				array (
					'p_id' => $row->smw_id,
					'usage_count' => $usageCount
				),
				__METHOD__
			);
			
This is the same change as in b42 patch.

(System  used is Debian 7; SMP Debian 3.10.11-1~bpo70+1 (2013-09-24) x86_64 
 working in SMW: With mediawiki-1.21.3 downloaded from mediawiki.)

(In reply to comment #14)
> Yes, somebody moving the code from SQLStore2 to SQLStore3 did rename the
> table
> from "smw_ids" to "smw_object_ids" but left the ("smw_ids_smw_id_seq"). Easy
> to
> fix, but code review takes time.

Change has been applied to [1]

[1] https://github.com/SemanticMediaWiki/SemanticMediaWiki/tree/postgresql

Please test.

> $dbw->replace(
>                 SMWSQLStore3::PROPERTY_STATISTICS_TABLE,
>                 array ('p_id'),
>                 array (
>                     'p_id' => $row->smw_id,
>                     'usage_count' => $usageCount
>                 ),
>                 __METHOD__
>             );
> 
> This is the same change as in b42 patch.

This statement does no longer exist in SMW 1.9, please test against SMW 1.9 as SMW 1.8 is no longer under active development and only changes to the current master can be verified.

(In reply to comment #16)
> Summary of changes that seems to make SMW work with PostgreSQL 9.1 on Debian
> Wheezy using  “php ./SMW_setup.php”.
> 
> 
> Change n.1 to solve problem: name “relation "smw_object_ids" already exists”
> 
> 
> $noindexname="";
> $db->query( "CREATE $type $noindexname ON $tableName USING btree($columns)",
> __METHOD__ );
> 
> to lines 494-495 in  SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php
> 
> Changes in patch provided by b42 solves this problem as well. But postgres
> will
> manage index name generation if not name is provided. This seems less
> complex.

Needs verification for the current master (SMW 1.9).

Above changes have been applied to the [1] branch which now runs a successful installation [2] of PostgreSQL 9.1.x on the Travis-CI platform using MW=1.21.0, SMW 1.9beta.

Unit tests such as [3] indicate some remaining issues but we need the expertise of people running PostgreSQL to solve those issues. Please test the [1] branch and report back occurring problems together with suggestions on how to solve them. 

[1] https://github.com/SemanticMediaWiki/SemanticMediaWiki/tree/postgresql

[2] https://s3.amazonaws.com/archive.travis-ci.org/jobs/14545490/log.txt

[3] 1) SMW\Test\SQLStore\PropertyStatisticsTableTest::testInsertUsageCount with data set #42 (9001, 2147483648)
DatabasePostgres::reportQueryError: No transaction to rollback, something got out of sync! [Called from DatabaseBase::rollback in /home/travis/build/SemanticMediaWiki/phase3/includes/db/Database.php at line 3107]

Branch:
 https://github.com/SemanticMediaWiki/SemanticMediaWiki/tree/postgresql
Seems to work ok on:
3.10-0.bpo.3-amd64 #1 SMP Debian 3.10.11-1~bpo70+1 (2013-09-24) x86_64 GNU/Linux
with  mediawiki-1.21.3 and postgresql-9.1.

Furthermore php unit test :
 php   phpunit.php   includes/storage/sqlstore/PropertyStatisticsTableTest.php > PropertyStatisticsTableTest.log

Reproduces point  comment 20 pint [3] and gives another similar error:

error 1:testInsertUsageCount with data set #42 (comment 20 pint [3])
error 2::testAddToUsageCounts;

Both php uni-test errors are caused because the uni-test PropertyStatisticsTableTest is assigning an out bounds value, 2147483648, to a postgresql integer data type. Therefore postgresql system is working as it should, failing the transaction.

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html. 

Indeed  in the table: smw_prop_stats,  if the data type for the Colum: usage_count is changed to bigint, then the unit test PropertyStatisticsTableTest completes with no errors:
" 
  php   phpunit.php   includes/storage/sqlstore/PropertyStatisticsTableTest.php 
  PHPUnit 3.6.10 by Sebastian Bergmann.
  Configuration read from /a/d2/data/asa/mediawiki/mediawiki-1.21.3_testing/tests/phpunit/suite.xml
  ..............................................
  Time: 3 seconds, Memory: 23.75Mb
  OK (46 tests, 314 assertions)
 "
 
 
 Moreover the statement  usage_count=$dbtypes[j] defines as integer data type in postgresql.  Assuming the possibility of big data sets, the   a possible solution would convert usage_count to a bigint data type for postgresql. 
Perhaps it would be a reasonable exercise to consider for postgresql that for an statistical domain, all the integers (-2147483648 to +2147483647) should be converted to bigints (-9223372036854775808 to 9223372036854775807).

 
Then  $dbtypes  array is defined  in the function setupTables, line 63 of includes/storage/SQLStore/SMW_SQLStore3.php.
 
 $dbtypes = array(
			'b' => ( $wgDBtype == 'postgres' ? 'BOOLEAN' : 'TINYINT(1)' ),
			't' => SMWSQLHelpers::getStandardDBType( 'title' ),
			'l' => SMWSQLHelpers::getStandardDBType( 'blob' ),
			'f' => ( $wgDBtype == 'postgres' ? 'DOUBLE PRECISION' : 'DOUBLE' ),
			'i' => ( $wgDBtype == 'postgres' ? 'INTEGER' : 'INT(8)' ),
			'j' => ( $wgDBtype == 'postgres' || $wgDBtype == 'sqlite' ? 'INTEGER' : 'INT(8) UNSIGNED' ),
			'p' => SMWSQLHelpers::getStandardDBType( 'id' ),
			'n' => SMWSQLHelpers::getStandardDBType( 'namespace' ),
			'w' => SMWSQLHelpers::getStandardDBType( 'iw' )
		);
		
Now it seems that type 'i' should be  bigint, which is 8 bytes, 
Thus the  statement: 'usage_count' => $dbtypes['j']  in line 133 seems that should be changed to 'usage_count' => $dbtypes['i']; after type 
'i' being set to be bigint for postgresql.

Could you apply a patch-set to [1] so Travis-CI [2] can automatically run the unit tests and we can see whether they pass or not.

[1] https://github.com/SemanticMediaWiki/SemanticMediaWiki/tree/postgresql

[2] https://travis-ci.org/SemanticMediaWiki/SemanticMediaWiki/builds/14545484

PS: SMW 1.9 will be released soon but at the current state, I would reluctantly merge the [1] branch into master not until the remaining unit tests are cleared.

Created attachment 13926 [details]
Diff file containing the following redefinitions:    usage_count=$dbtypes[i].   'i' => ( $wgDBtype == 'postgres' ? 'bigint' : 'INT(8)'.


Diff file with the changes to $dbtypes  array, which is defined  in the function setupTables, in line 63 of the file : includes/storage/SQLStore/SMW_SQLStore3_SetupHandlers.php.

Description in comment n. 22. And as per request in comment n.23.

You could add this directly to the GitHub branch yourself otherwise it will take time before someone will manually apply this patch?

(In reply to comment #25)
> You could add this directly to the GitHub branch yourself otherwise it will
> take time before someone will manually apply this patch?

Hi
It seems that I would need be given access to push the changes into github...
Regards
J.A.

You can create a fork of the repo an push the branch with additional commit over there, then submit a pull request against the branch on the main repo.

Change 88459 had a related patch set uploaded by Nemo bis:
Fix some incompatibilities with PostgreSQL

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

This has been fixed now right? If someone still runs into this on master, please reopen.

Change 88459 abandoned by Jeroen De Dauw:
Fix some incompatibilities with PostgreSQL

Reason:
I'm abandoning this as I think the issue has already been fixed, and since the commit can no longer be merged into master without manual resolve. If I am wrong and the issue is still there, please reopen the commit and make it mergeable with master.

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

Change 91375 abandoned by Jeroen De Dauw:
Sort out index creation for PostgreSQL

Reason:
Please re-submit against https://github.com/SemanticMediaWiki/SemanticMediaWiki if you want to get this in. We have our tests run with postgres there

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