Wikimedia Bugzilla is closed!

Found this in /var/log/everything.log:
Jul 26 15:04:17 box httpd: PHP Warning:  pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR:  syntax error at or near &quot;smw_tmp_unusedprops&quot;
Jul 26 15:04:17 box LINE: 1: ...ETE /* SMW::getUnusedPropertySubjects 10.0.0.8 */ smw_tmp_un...
Jul 26 15:04:17 box ^: in /srv/http/mediawiki/includes/db/DatabasePostgres.php on line 580

Ah, so you're using a Postgres database. This sounds like a bug in either Semantic MediaWiki's or Semantic Forms' handling of Postgres. What happens when you go to the page "Special:UnusedProperties" - do you get the same error?

Yes it looks like the same error.

index.php/Special:UnusedProperties
Internal error
A database error has occurred
Query: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''
Function: SMW::getUnusedPropertySubjects
Error: 1 ERROR: syntax error at or near "smw_tmp_unusedprops"
LINE 1: ...ETE /* SMW::getUnusedPropertySubjects 10.0.0.8 */ smw_tmp_un...
^
Backtrace:
#0 /srv/http/mediawiki/includes/db/Database.php(616): DatabasePostgres->reportQueryError('ERROR: syntax ...', 1, 'DELETE smw_tmp_...', 'SMW::getUnusedP...', false)
#1 /srv/http/mediawiki/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php(1006): Database->query('DELETE smw_tmp_...', 'SMW::getUnusedP...')
#2 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(65): SMWSQLStore2->getUnusedPropertiesSpecial(Object(SMWRequestOptions))
#3 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_QueryPage.php(49): SMWUnusedPropertiesPage->getResults(Object(SMWRequestOptions))
#4 /srv/http/mediawiki/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(18): SMWQueryPage->doQuery(0, 50)
#5 [internal function]: smwfDoSpecialUnusedProperties(NULL, Object(SMWSpecialPage))
#6 /srv/http/mediawiki/includes/SpecialPage.php(771): call_user_func('smwfDoSpecialUn...', NULL, Object(SMWSpecialPage))
#7 /srv/http/mediawiki/includes/SpecialPage.php(559): SpecialPage->execute(NULL)
#8 /srv/http/mediawiki/includes/Wiki.php(229): SpecialPage::executePath(Object(Title))
#9 /srv/http/mediawiki/includes/Wiki.php(59): MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest))
#10 /srv/http/mediawiki/index.php(116): MediaWiki->initialize(Object(Title), NULL, Object(OutputPage), Object(User), Object(WebRequest))
#11 {main}

Okay, I changed the bug title, and the extension (to Semantic MediaWiki), since this is really an SMW bug - I'm hoping this will work.

Also re-assigning to Markus...

Did you actually initialize the database after installing SMW? You should find a number of tables starting with "smw..." in your database, and basic operations such as adding simple annotations should work without errors. If this is the problem, please follow the SMW installation instructions (using either Special:SMWAdmin or SMW_setup.php). Also please make sure that the installation works normally without SMW enabled before switching on the extension.

If all of those basic checks are okay, then we can continue debugging. Do you already have any idea why the given query could be illegal in Postgres?

P.S.: Note that Postgres support in SMW is still somewhat experimental due to the smaller size of our Postgres user base. If you find Postgres support to be too shaky for your application then switching to MySQL might be the best option for now.

More information is needed here. What happens if you run the query:

 DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''

directly in Postgres (in the context of your wiki database)? You will have to create the table smw_tmp_unusedprops first. For this purpose, run the query:

 CREATE TEMPORARY TABLE smw_tmp_unusedprops ( title text );

1) The access rights of user for schemas (public and wiki) are correct. 
2) 
EXEC: CREATE TEMPORARY TABLE smw_tmp_unusedprops ( title text );
OK (La consulta se ejecutó con éxito sin resutado en 16 ms.)

EXEC: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_rels2 INNER JOIN
smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw=''
ERROR:  error de sintaxis en o cerca de «smw_tmp_unusedprops»
LINE 1: DELETE smw_tmp_unusedprops.* FROM smw_tmp_unusedprops, smw_r...
               ^
********** Error **********
ERROR: error de sintaxis en o cerca de «smw_tmp_unusedprops»
Estado SQL:42601
Caracter: 8
----

3) I have modified the request.

EXEC: select * FROM smw_tmp_unusedprops where title in (select smw_title from smw_tmp_unusedprops, smw_rels2 INNER JOIN
smw_ids ON p_id=smw_id WHERE title=smw_title AND smw_iw='')

OK (empty result - my "smw_rels2" is empty but smw_ids is not)

In Postgres you cannot specify more then one table in a DELETE statement.

I have re-written the delete statement to comply with the Postgres sql syntax:

delete from smw_tmp_unusedprops
  using smw_rels2 inner join smw_ids on smw_rels2.p_id = smw_ids.smw_id
  where smw_ids.smw_iw='' and smw_title=smw_tmp_unusedprops.title;

I tested it on the command line. It worked.

The proposed query did not work in MySQL, since it does not include smw_tmp_unusedprops in the USING part. Trying to find a query that works in either case, I came up with the following:

DELETE FROM smw_tmp_unusedprops
 USING smw_tmp_unusedprops INNER JOIN smw_rels2 INNER JOIN smw_ids
 ON p_id=smw_id AND title=smw_title AND smw_iw='';

I hope this still works on Postgres (if not, please re-open the bug).

The next SMW release 1.5.1 that will include this change is scheduled for Monday, May 31.

I can confirm this bug with Semantic Mediawiki 1.5.4 and Postgresql 8.5.4

When opening Special:UnusedProperties page with $wgShowExceptionDetails = true;
I get the following,

A database error has occurred
Query: DELETE FROM smw_tmp_unusedprops USING smw_tmp_unusedprops INNER JOIN
smw_rels2 INNER JOIN smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=''
Function: SMW::getUnusedPropertySubjects
Error: 1 ERROR: syntax error at end of input
LINE 1: ...OIN smw_ids ON p_id=smw_id AND title=smw_title AND smw_iw=''
^
Backtrace:

#0 /home/jkk/mediawiki-1.16.0/w/includes/db/Database.php(538):
DatabasePostgres->reportQueryError('ERROR: syntax ...', 1, 'DELETE FROM
smw...', 'SMW::getUnusedP...', false)
#1
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/includes/storage/SMW_SQLStore2.php(1106):
DatabaseBase->query('DELETE FROM smw...', 'SMW::getUnusedP...')
#2
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(100):
SMWSQLStore2->getUnusedPropertiesSpecial(Object(SMWRequestOptions))
#3
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_QueryPage.php(49):
SMWUnusedPropertiesPage->getResults(Object(SMWRequestOptions))
#4
/home/jkk/mediawiki-1.16.0/w/extensions/SemanticMediaWiki/specials/QueryPages/SMW_SpecialUnusedProperties.php(45):
SMWQueryPage->doQuery(0, 50)
#5 /home/jkk/mediawiki-1.16.0/w/includes/SpecialPage.php(559):
SMWSpecialUnusedProperties->execute(NULL)
#6 /home/jkk/mediawiki-1.16.0/w/includes/Wiki.php(254):
SpecialPage::executePath(Object(Title))
#7 /home/jkk/mediawiki-1.16.0/w/includes/Wiki.php(64):
MediaWiki->handleSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#8 /home/jkk/mediawiki-1.16.0/w/index.php(117):
MediaWiki->performRequestForTitle(Object(Title), NULL, Object(OutputPage),
Object(User), Object(WebRequest))
#9 /home/jkk/mediawiki-1.16.0/w/index.php5(1): require('/home/jkk/media...')
#10 {main}

It appears that Postgresql doesn't like USING to include the table in FROM, while mysql requires it. I think both support using a subquery, but that might be expensive. Maybe including more data in the temporary table might be the way forward.

I ended up changing both delete queries and removed "TEMPORARY" from the table drop to get Special:UnusedProperties working.

Created attachment 8095 [details]
Postgresql unused properties SQL fixes

I've split the two delete queries into postgres and other, and fixed up the postgres side to work. Also dropped the TEMPORARY syntatic sugar from the DROP TABLE as it's not necessary under mysql, and not supported by postgresql.

Works for me; between that and the #26202 patch, Postgresql backend works well.

I was having this issue, and John's patch worked great for me too.  Put it in the code!

If it hasn't been committed yet it's neither resolved nor fixed.

*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*

This problem is still in SMW 1.6, and John's patch still works (with some minor changes to match the latest code).  When will this patch (or something more robust) be committed?

Created attachment 9105 [details]
Postgresql unused properties SQL fixes, 1.6.1

A fresh patch against 1.6.1. What's holding up this bug's resolution?

I added the "need-review" tag so developers know that this patch needs reviewing.  I've added Greg Sabino Mullane & Andy Lester to the cc list so they'll know the patch needs reviewing; perhaps one of them can help test and review it?

Sorry for keeping you waiting. Patch applied in r111256. I hope this fixes the issue in PostgreSQL.

I kept the "TEMPORARY" keyword for non-postgres databases, since I am not certain that DROP can be done by users that have only DROP TEMPORARY privileges, even if the dropped table happens to be temporary.