b42 wrote:

Possible fixes for few postgres problems

Attached:

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

b42 wrote:

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.

b42 wrote:

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

tm wrote:

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-General :

< 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

fallos wrote:

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.

fallos wrote:

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 )

fallos wrote:

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]

fallos wrote:

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;

fallos wrote:

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.

fallos wrote:

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.

Attached:

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

fallos wrote:

(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

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.