Last modified: 2014-02-08 00:38:11 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 42659 - Unable to install SMW 1.8 with PostgreSQL
Unable to install SMW 1.8 with PostgreSQL
Status: RESOLVED FIXED
Product: MediaWiki extensions
Classification: Unclassified
Semantic MediaWiki (Other open bugs)
unspecified
PC Linux
: Unprioritized normal (vote)
: ---
Assigned To: Nobody - You can work on this!
:
Depends on:
Blocks: postgres 25450
  Show dependency treegraph
 
Reported: 2012-12-03 21:09 UTC by b42
Modified: 2014-02-08 00:38 UTC (History)
9 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments
Possible fixes for few postgres problems (2.74 KB, patch)
2012-12-03 21:10 UTC, b42
Details
Diff file containing the following redefinitions: usage_count=$dbtypes[i]. 'i' => ( $wgDBtype == 'postgres' ? 'bigint' : 'INT(8)'. (890 bytes, patch)
2013-11-28 10:11 UTC, José Antonio
Details

Description b42 2012-12-03 21:09:18 UTC
OS: Fedora 17
MediaWiki: 1.20 (from source)
SMW: 1.8
PostgreSQL: 9.1.6-1.fc17

Following installation instructions, I unpacked the extensions and enabled them in LocalSettings.php. Now:

- Accessing the main wiki page results in blank page, nothing in apache's error_log and status 500 in access log
- Special:SMWAdmin can be accessed, but "Initialise or upgrade tables" results in the same error as below but without the backtrace (didn't know how to make php produce one).
- I figured that running "php maintenance/SMW_setup.php" should do the same. The result is:

Selected storage "SMWSQLStore3" for update!

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.
A database error has occurred.  Did you forget to run maintenance/update.php after upgrading?  See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
Query: CREATE INDEX "smw_object_ids" ON "smw_object_ids" USING btree(smw_id)
Function: SMWSQLHelpers::createIndex
Error: 42P07 ERROR:  relation "smw_object_ids" already exists


Backtrace:
#0 /var/www/mw/includes/db/DatabasePostgres.php(472): DatabaseBase->reportQueryError('ERROR:  relatio...', '42P07', 'CREATE INDEX "s...', 'SMWSQLHelpers::...', false)
#1 /var/www/mw/includes/db/Database.php(899): DatabasePostgres->reportQueryError('ERROR:  relatio...', '42P07', 'CREATE INDEX "s...', 'SMWSQLHelpers::...', false)
#2 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php(495): DatabaseBase->query('CREATE INDEX "s...', 'SMWSQLHelpers::...')
#3 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php(376): SMWSQLHelpers::createIndex(Object(DatabasePostgres), 'INDEX', '"smw_object_ids...', '"smw_object_ids...', 'smw_id', NULL)
#4 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SQLStore/SMW_SQLStore3_SetupHandlers.php(101): SMWSQLHelpers::setupIndex('smw_object_ids', Array, Object(DatabasePostgres))
#5 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SQLStore/SMW_SQLStore3_SetupHandlers.php(38): SMWSQLStore3SetupHandlers->setupTables(true, Object(DatabasePostgres))
#6 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SQLStore/SMW_SQLStore3.php(396): SMWSQLStore3SetupHandlers->setup(true)
#7 /var/www/mw/extensions/SemanticMediaWiki/includes/storage/SMW_Store.php(451): SMWSQLStore3->setup(true)
#8 /var/www/mw/extensions/SemanticMediaWiki/maintenance/SMW_setup.php(91): SMWStore::setupStore(true)
#9 /var/www/mw/maintenance/doMaintenance.php(110): SMWSetupScript->execute()
#10 /var/www/mw/extensions/SemanticMediaWiki/maintenance/SMW_setup.php(189): require_once('/home/mmilata/p...')
#11 {main}

I managed to progress a bit further by poking at the source code but eventually gave up. Attaching the changes I made -- they are probably useless as I'm unfamiliar with both SMW codebase and postgres.
Comment 1 b42 2012-12-03 21:10:17 UTC
Created attachment 11457 [details]
Possible fixes for few postgres problems
Comment 2 MWJames 2012-12-06 02:51:18 UTC
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
Comment 3 b42 2012-12-10 10:03:05 UTC
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?
Comment 4 MWJames 2012-12-10 10:33:12 UTC
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.
Comment 5 b42 2012-12-10 11:25:17 UTC
Alright, I added a note to http://semantic-mediawiki.org/wiki/PostgreSQL.
Comment 6 Tiziano Müller 2013-10-07 21:39:30 UTC
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
Comment 7 Merlijn van Deen (test) 2013-10-15 20:23:48 UTC
Note: this patch could not be applied cleanly to the current master.
Comment 8 Marcin Cieślak 2013-10-23 09:09:08 UTC
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
"
Comment 9 Jeroen De Dauw 2013-10-23 12:57:28 UTC
There is a commit related to this on gerrit: https://gerrit.wikimedia.org/r/#/c/88459/
Comment 10 Gerrit Notification Bot 2013-10-23 13:01:45 UTC
Change 91375 had a related patch set uploaded by saper:
Sort out index creation for PostgreSQL

https://gerrit.wikimedia.org/r/91375
Comment 11 Marcin Cieślak 2013-10-23 13:03:52 UTC
(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
Comment 12 Marcin Cieślak 2013-10-23 13:04:44 UTC
(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
Comment 13 José Antonio 2013-11-24 22:32:56 UTC
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
Comment 14 Marcin Cieślak 2013-11-25 07:41:57 UTC
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.
Comment 15 José Antonio 2013-11-26 08:46:17 UTC
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  )
Comment 16 José Antonio 2013-11-26 11:50:58 UTC
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.)
Comment 17 MWJames 2013-11-26 12:13:52 UTC
(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.
Comment 18 MWJames 2013-11-26 12:17:09 UTC
> $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.
Comment 19 MWJames 2013-11-26 12:21:11 UTC
(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).
Comment 20 MWJames 2013-11-26 14:14:35 UTC
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]
Comment 21 José Antonio 2013-11-27 18:19:15 UTC
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;
Comment 22 José Antonio 2013-11-28 00:40:10 UTC
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.
Comment 23 MWJames 2013-11-28 01:24:01 UTC
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.
Comment 24 José Antonio 2013-11-28 10:11:03 UTC
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.
Comment 25 MWJames 2013-11-28 10:17:45 UTC
You could add this directly to the GitHub branch yourself otherwise it will take time before someone will manually apply this patch?
Comment 26 José Antonio 2013-11-28 13:33:06 UTC
(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.
Comment 27 Jeroen De Dauw 2013-11-28 20:43:26 UTC
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.
Comment 28 Gerrit Notification Bot 2013-12-13 22:18:49 UTC
Change 88459 had a related patch set uploaded by Nemo bis:
Fix some incompatibilities with PostgreSQL

https://gerrit.wikimedia.org/r/88459
Comment 29 Jeroen De Dauw 2013-12-19 16:06:14 UTC
This has been fixed now right? If someone still runs into this on master, please reopen.
Comment 30 Gerrit Notification Bot 2013-12-19 16:07:41 UTC
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
Comment 31 Gerrit Notification Bot 2014-02-08 00:38:11 UTC
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

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links