anton.kochkov wrote:

little patch for fix postgresql offset query

Attached:

anton.kochkov wrote:

Schema for my wiki installation, which works (partially) with SocialProfile on PostgreSQL

User Board/User properties works ok.

Attached:

anton.kochkov wrote:

Some functions and sequencies needed for SocialProfile

Attached:

anton.kochkov wrote:

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).

anton.kochkov wrote:

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.

anton.kochkov wrote:

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.

yackushevas wrote:

(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*

anton.kochkov wrote:

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}

anton.kochkov wrote:

btw, PostgreSQL 8.* series is now deprecated.

yackushevas wrote:

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. ***

Change 617849 had a related patch set uploaded (by Jack Phoenix; owner: Jack Phoenix):
[mediawiki/extensions/SocialProfile@master] Wrap date() calls to be inserted into the DB in Database#timestamp for optimal PostgreSQL compatibility

https://gerrit.wikimedia.org/r/617849

Change 617849 merged by jenkins-bot:
[mediawiki/extensions/SocialProfile@master] Wrap date() calls to be inserted into the DB in Database#timestamp for optimal PostgreSQL compatibility

https://gerrit.wikimedia.org/r/617849