fallos wrote:

23:07:05Fri 29 Nov 13 23:07:07

The the fields to be use in outer joins need to be defined as not null. Then PostgreSQL can enforce data consistency without falling the outer left join transaction.

The root of the problem can be found in the the PostgreSQL log.

The test from where the fail arises is: public function testOnArticlePurgeOnDatabase()
in line 121 of
SemanticMediaWiki/tests/phpunit/integration/MediaWikiFunctionHookIntegrationTest.php.

GMT ERROR:
SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join.

This means in this context that the data fields used in the left join must be not nullable:
{
LEFT JOIN "unittest_mwuser" ON ((rev_user != 0) AND (user_id = rev_user)) WHERE page_id = '612' AND rev_id = '892' LIMIT 1 FOR UPDATE
}

ref: http://www.postgresql.org/message-id/flat/98882B9F-5C37-4C5B-9B15-5E51D8767CF2@socialserve.com
http://postgresql.1045698.n5.nabble.com/outer-joins-and-for-update-td1937029.html

GMT ERROR:
SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join.

GMT STATEMENT:
SELECT

rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,rev_content_format,rev_content_model,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name

FROM "unittest_revision" INNER JOIN "unittest_page" ON ((page_id = rev_page))

It seems that unit test reporting hides the real root of the problem that can be identified in the PostgreSQL log .Then in future It seems that it would very useful to include this log.
On a debian system the standard location is in: /var/log/postgresql/postgresql-9.1-main.log. Thus perhaps including the output of
tail -20 /var/log/postgresql/postgresql-9.1-main.log with unit test fails report could be something easy to achieve.

From above analysis it looks to be a dupe of bug 47055, adding dependency for now.

Be also aware of a quite nasty bug 37702, but I suppose it affects SMW to the lesser extent.

fallos wrote:

Thanks for the link to 47055 and pointing to 37702.

It seems that all the problems related to postgresql are rooted on creating data sets that are not compatible with the operations that smw wants to excute on them.

Indeed in this case mysql seems to enable outer joins on nullables.

Here It seems that the most more straight approach would be set “not null” for fields that are used in outer joins.

Regards.

J.A.

fallos wrote:

Hi,

According to my understanding the root of this bug is in
is in function selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); starting in line 1453 of file includes/db/Database.php.

In my understanding this function should check if an update on a select with outer joint is going to be requested . Thus avoiding the” select for update/share cannot be applied to the nullable side of an outer join.” exception thrown by postgresql.

This the secuence of calls that it seems to happen from
/
SemanticMediaWiki/tests/phpunit/integration/MediaWikiFunctionHookIntegrationTest.php
testOnArticlePurgeOnDatabase();

$this->deletePage( $wikiPage, METHOD ) starts an “forupdate” process.
$wikiPage->doDeleteArticle(
doDeleteArticleReal

[file includes/Hooks.php]
wfRunHooks( 'ArticleDelete', array( &$this wikipage,

&$user, &$reason, &$error, &$status ) )

$hooks = self::getHandlers( ArticleDelete );

[file includes/wikipage.php]
loadPageData( 'forupdate' );
pageDataFromTitle( wfGetDB( DB_MASTER ), $this->mTitle, array( 'FOR UPDATE' ) );

[includes/db/LoadBalancer.php]
connection -->db->last query lef joint → reused for update

[file includes/wikipage.php]

pageDataFromTitle
pageData

[file includes/db/Database.php]
selectRow( 'page', $fields, $conditions, METHOD, $options );
selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );

Regards.

J.A.

fallos wrote:

(In reply to comment #3)

Thanks for the link to 47055 and pointing to 37702.

It seems that all the problems related to postgresql are rooted on
creating
data sets that are not compatible with the operations that smw wants to
excute
on them.

Indeed in this case mysql seems to enable outer joins on nullables.

Here It seems that the most more straight approach would be set “not null”
for fields that are used in outer joins.

Regards.

J.A.

Setting a non null constraint for all the fields of the tables involved in the outer joint does not solve the problem.

fallos wrote:

(In reply to comment #4)

Hi,

According to my understanding the root of this bug is in
is in function selectSQLText( $table, $vars, $conds, $fname, $options,
$join_conds ); starting in line 1453 of file includes/db/Database.php.

In my understanding this function should check if an update on a select
with
outer joint is going to be requested . Thus avoiding the” select for
update/share cannot be applied to the nullable side of an outer join.”
exception thrown by postgresql.

This the secuence of calls that it seems to happen from
/
SemanticMediaWiki/tests/phpunit/integration/
MediaWikiFunctionHookIntegrationTest.php
testOnArticlePurgeOnDatabase();

$this->deletePage( $wikiPage, METHOD ) starts an “forupdate” process.
$wikiPage->doDeleteArticle(
doDeleteArticleReal

[file includes/Hooks.php]
wfRunHooks( 'ArticleDelete', array( &$this wikipage,

&$user, &$reason, &$error, &$status ) )

$hooks = self::getHandlers( ArticleDelete );

[file includes/wikipage.php]
loadPageData( 'forupdate' );
pageDataFromTitle( wfGetDB( DB_MASTER ), $this->mTitle, array( 'FOR UPDATE' )
);

[includes/db/LoadBalancer.php]
connection -->db->last query lef joint → reused for update

[file includes/wikipage.php]

pageDataFromTitle
pageData

[file includes/db/Database.php]
selectRow( 'page', $fields, $conditions, METHOD, $options );
selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );

Regards.

J.A.

If the real problem is in function selectSQLText then perhaps a bug should be raised in mediawiki...

Looks like related to bug 36759, bug 37600 and unfortunately bug 37702 to me.

Can you run the test again with 4b291909e0e91ad4e8ed98030c1312a872ca3bd4 reverted and post the error message again? (see bug 58095)

I can't because the current MW master is even more broken for PostgerSQL because of [1].

[1] Could not insert main page: 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: INSERT INTO "recentchanges" (rc_timestamp,rc_namespace,rc_title,rc_type,rc_source,rc_minor,rc_cur_id,rc_user,rc_user_text,rc_comment,rc_this_oldid,rc_last_oldid,rc_bot,rc_ip,rc_patrolled,rc_new,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_id) VALUES ('2013-12-05 20:33:02 GMT','0','Main_Page','1','mw.new','0','1','0','MediaWiki default',,'1','0','0','127.0.0.1','0','1','0','524','0','0',NULL,,,'1')
Function: RecentChange::save
Error: 23502 ERROR: null value in column "rc_cur_time" violates not-null constraint

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

PS: I do wonder if someone on the WMF site really runs tests (it's the second time this week Travis fails because of deployed core incompatibilities).

Yes, this is bug 40744 gone bad. The fix is in gerrit change I66034fad8a1cf5485b5daf0421378a28ca58beab but somehow having pains in review :)

I have some more interesting PostgreSQL fixes in the pipeline:

gerrit change 99349
gerrit change 99640
gerrit change 99648
gerrit change 99676
gerrit change 100141
gerrit change 100154

feel free to try them as all of them are still in the works.

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