Last modified: 2013-09-02 20:21:43 UTC
Please add support of PostgreSQL database storage
Created attachment 8216 [details] little patch for fix postgresql offset query
Created attachment 8217 [details] Schema for my wiki installation, which works (partially) with SocialProfile on PostgreSQL User Board/User properties works ok.
Created attachment 8218 [details] Some functions and sequencies needed for SocialProfile
These schemas for my wiki and this small little patch works ok on some parts of SocialProfile features, but do crash for others. Hope you can fix that in future :)
SocialProfile has had (theoretical) PostgreSQL support for a while already, see the appropriate .postgres.sql files in the SocialProfile subdirectories (http://svn.wikimedia.org/viewvc/mediawiki/trunk/extensions/SocialProfile/UserBoard/user_board.postgres.sql?view=markup for example). As for your first patch, it looks OK but I'm not sure, and I certainly don't want to break MySQL compatibility (although if I did, I'm sure someone would spot it quickly and report back). In any case, I'd like to get rid of that raw SQL construction altogether instead of just patching the existing raw SQL. That way maybe one day SocialProfile will work with Oracle, too. :) Mark commented on SocialProfile's MySQL-isms on http://www.mediawiki.org/wiki/Special:Code/MediaWiki/75007#c11339; I implemented one of his suggestions on r77339 but later had to revert it in r79748 because the Database::unixTimestamp function was removed. Long story short, there is some support for PostgreSQL already, but there are plenty of code-related issues that should be fixed in order to support PostgreSQL and other DBMSes properly. I'd like to get rid of all the raw SQL queries first; IIRC there are also some rather nasty subqueries somewhere, too. I unfortunately don't know much about non-MySQL DBMSes, but I'm interested in building PostgreSQL support. Feel free to submit patches or even apply for commit access (see http://www.mediawiki.org/wiki/Commit_access for information about that).
Hm. main problems was with postgresql creating tables - your postgresql files are not work on postgresql. (at least on 9.0 and later). So, I'm attached my tables, functions (in SQL unixtime, etc), and sequences for PostgreSQL, which works ok.
And here an example of some error with LIMIT and OFFSET: A database error has occurred Query: SELECT UNIX_TIMESTAMP(rc_timestamp) AS item_date, rc_title, rc_user, rc_user_text, rc_comment, rc_id, rc_minor, rc_new, rc_namespace, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_log_action FROM recentchanges ORDER BY rc_id DESC LIMIT 0,50 Function: UserActivity::setEdits Error: 1 ERROR: LIMIT #,# syntax is not supported LINE 7: ORDER BY rc_id DESC LIMIT 0,50 ^ HINT: Use separate LIMIT and OFFSET clauses. Backtrace: #0 /usr/home/droid-dev/www/includes/db/Database.php(538): DatabasePostgres->reportQueryError('ERROR: LIMIT #...', 1, 'SELECT UNIX_TIM...', 'UserActivity::s...', false) #1 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(85): DatabaseBase->query('SELECT UNIX_TIM...', 'UserActivity::s...') #2 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(605): UserActivity->setEdits() #3 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivityClass.php(639): UserActivity->getActivityList() #4 /usr/home/droid-dev/www/extensions/SocialProfile/UserActivity/UserActivity.body.php(75): UserActivity->getActivityListGrouped() #5 /usr/home/droid-dev/www/includes/SpecialPage.php(559): UserHome->execute(NULL) #6 /usr/home/droid-dev/www/includes/Wiki.php(254): SpecialPage::executePath(Object(Title)) #7 /usr/home/droid-dev/www/includes/Wiki.php(64): MediaWiki->handleSpecialCases(Object(Title), Object(OutputPage), Object(WebRequest)) #8 /usr/home/droid-dev/www/index.php(117): MediaWiki->performRequestForTitle(Object(Title), NULL, Object(OutputPage), Object(User), Object(WebRequest)) #9 {main}
I've rewritten the SQL queries used by UserActivity to use MediaWiki's Database class in r84804; can you please try updating your SocialProfile to that revision and see if it helps? If that doesn't fix the problem(s), then a PostgreSQL guru should take a look at this bug; I'm not familiar with PGSQL and how it works.
(In reply to comment #8) > I've rewritten the SQL queries used by UserActivity to use MediaWiki's Database > class in r84804; can you please try updating your SocialProfile to that > revision and see if it helps? If that doesn't fix the problem(s), then a > PostgreSQL guru should take a look at this bug; I'm not familiar with PGSQL and > how it works. It hasn't helped me
*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*
MW: trunk, SocialProfile: trunk Warning: pg_query() [function.pg-query]: Query failed: ERROR: function unix_timestamp(timestamp without time zone) does not exist LINE 1: ...tatus,gift_name,gift_description,gift_given_count,UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: Invalid argument supplied for foreach() in /home/droid-dev/www/includes/resourceloader/ResourceLoaderWikiModule.php on line 176 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 Warning: Invalid argument supplied for foreach() in /home/droid-dev/www/includes/resourceloader/ResourceLoaderWikiModule.php on line 176 Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 253 MediaWiki internal error. Original exception: exception 'DBQueryError' with message 'A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: http://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script Query: SELECT ug_id,ug_user_id_from,ug_user_name_from,ug_gift_id,ug_date,ug_status,gift_name,gift_description,gift_given_count,UNIX_TIMESTAMP(ug_date) AS unix_time FROM "user_gift" INNER JOIN "gift" ON ((ug_gift_id = gift_id)) WHERE (ug_user_id_to = 4) ORDER BY ug_id DESC LIMIT 4 OFFSET 0 Function: UserGifts::getUserGiftList Error: 1 ERROR: function unix_timestamp(timestamp without time zone) does not exist LINE 1: ...tatus,gift_name,gift_description,gift_given_count,UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ' in /home/droid-dev/www/includes/db/Database.php:845 Stack trace: #0 /home/droid-dev/www/includes/db/Database.php(812): DatabaseBase->reportQueryError('ERROR: functio...', 1, 'SELECT ug_id,u...', 'UserGifts::getU...', false) #1 /home/droid-dev/www/includes/db/Database.php(1281): DatabaseBase->query('SELECT ug_id,u...', 'UserGifts::getU...') #2 /home/droid-dev/www/extensions/SocialProfile/UserGifts/UserGiftsClass.php(332): DatabaseBase->select(Array, Array, Array, 'UserGifts::getU...', Array, Array) #3 /home/droid-dev/www/extensions/SocialProfile/UserProfile/UserProfilePage.php(1043): UserGifts->getUserGiftList(0, 4) #4 /home/droid-dev/www/extensions/SocialProfile/UserProfile/UserProfilePage.php(73): UserProfilePage->getGifts('XVilka') #5 /home/droid-dev/www/includes/Wiki.php(436): UserProfilePage->view() #6 /home/droid-dev/www/includes/Wiki.php(211): MediaWiki->performAction(Object(UserProfilePage)) #7 /home/droid-dev/www/includes/Wiki.php(586): MediaWiki->performRequest() #8 /home/droid-dev/www/includes/Wiki.php(497): MediaWiki->main() #9 /home/droid-dev/www/index.php(74): MediaWiki->run() #10 {main} Exception caught inside exception handler: exception 'DBUnexpectedError' with message 'SQL error: ERROR: current transaction is aborted, commands ignored until end of transaction block' in /home/droid-dev/www/includes/db/DatabasePostgres.php:303 Stack trace: #0 /home/droid-dev/www/includes/WatchedItem.php(45): DatabasePostgres->numRows(false) #1 /home/droid-dev/www/includes/User.php(2408): WatchedItem->isWatched() #2 /home/droid-dev/www/includes/Title.php(1107): User->isWatched(Object(Title)) #3 /home/droid-dev/www/includes/SkinTemplate.php(1026): Title->userIsWatching() #4 /home/droid-dev/www/includes/SkinTemplate.php(501): SkinTemplate->buildContentNavigationUrls(Object(OutputPage)) #5 /home/droid-dev/www/includes/OutputPage.php(1929): SkinTemplate->outputPage(Object(OutputPage)) #6 /home/droid-dev/www/includes/Exception.php(183): OutputPage->output() #7 /home/droid-dev/www/includes/Exception.php(213): MWException->reportHTML() #8 /home/droid-dev/www/includes/Exception.php(413): MWException->report() #9 /home/droid-dev/www/includes/Exception.php(481): MWExceptionHandler::report(Object(DBQueryError)) #10 /home/droid-dev/www/includes/Wiki.php(500): MWExceptionHandler::handle(Object(DBQueryError)) #11 /home/droid-dev/www/index.php(74): MediaWiki->run() #12 {main}
btw, PostgreSQL 8.* series is now deprecated.
When installing SocialProfile get the error: ... Creating user_status table...Could not open"/var/www/test/extensions/SocialProfile/UserStatus/userstatus.postgres.sql". Backtrace: #0 /var/www/test/includes/installer/DatabaseUpdater.php(362): DatabaseBase->sourceFile('/var/www/test/e...') #1 /var/www/test/includes/installer/DatabaseUpdater.php(379): DatabaseUpdater->applyPatch('/var/www/test/e...', true) #2 [internal function]: DatabaseUpdater->addTable('user_status', '/var/www/test/e...', true) #3 /var/www/test/includes/installer/DatabaseUpdater.php(233): call_user_func_array(Array, Array) #4 /var/www/test/includes/installer/DatabaseUpdater.php(201): DatabaseUpdater->runUpdates(Array, true) #5 /var/www/test/maintenance/update.php(119): DatabaseUpdater->doUpdates(Array) #6 /var/www/test/maintenance/doMaintenance.php(105): UpdateMediaWiki->execute() #7 /var/www/test/maintenance/update.php(145): require_once('/var/www/test/m...') #8 {main} P.S. MediaWiki 1.18.2; PHP 5.3.3-7+squeeze8 (apache2handler); PostgreSQL 8.4.11; SocialProfile r92462
*** Bug 28279 has been marked as a duplicate of this bug. ***
