Manual talk:PostgreSQL

From MediaWiki.org

I would not say that PostgreSQL with MediaWiki is working smoothly. I am talking about the current stable release http://svn.wikimedia.org/svnroot/mediawiki/branches/REL1_13/phase3 (rev 45131). To do the following tests, I deactivated any extensions. I have Postgresql 8.3 (Debian) running with MediaWiki and mod_php with Apache 2.2:

• The way, string escaping is done causes warnings in the postgresql error log ("WARNING: nonstandard use of \\ in a string literal at character 147"). However, it works and the warning could be deactivated in the configuration file of PostgreSQL but the correct way would be to use the character E before the string if it contains encoded characters.
• The number of "BEGIN" and "COMMIT" is not equal. There is one "additional" COMMIT at the end that also causes a warning in the log. Again, this is ugly but it works.
• During one single request (view) of a very small, simple page without external links, several database accesses occur that are unnecessary in my opinion: E.g., the key "winterrodeln_wiki:messages:de-formal:status" from the objectcache table was 9 times deleted and 4 times inserted (with the equal content). (I told Postgresql to show me the SQL commands in the log file so I discovered it).
• For the described page view, 5 fresh connections to the database were made and they were not recycled on following requests.
• BLOBs are escaped twice (I'm not 100% sure about this, but I have the feeling that this is the case when I studied the code).
• In DatabasePostgres.php comments like "# FIXME: HACK HACK HACK HACK debug" or "TODO: [...] not sure if the following test really trigger if the object" can be found.
• The performance on our server with MediaWiki/PostgreSQL is very poor (1 GHz/1 GB Machine): "Save page" lasts several seconds (especially when many users are requesting pages) and we already had a server crash because MediaWiki could not provide the pages fast enough.
• After changing to mySQL (see link below), the performance was good so it for sure had to do with the database.
• I wanted to improve the MediaWiki-PostgreSQL code, but I gave up because the database abstraction layer seemed too tailored to MySql: E.g. the function "addQuotes" that is used inside "makeList" and other places should, well, encode strings. _But_: For postgresql, (1) I need a handle to the database to do to proper encoding and (2) I should use different encoding functions for strings and BLOBS - but how should I distinguish it? Maybe I don't understand the concept well enough - but it's not that easy because many functions have a "@todo document function" as their description...

Please don't think that I'm disappointed of MediaWiki: I think it's great! (thank you all!)

I just would not recommend using Postgresql with MediaWiki until the integration gets, well, more mature. ;-) Phispi 21:27, 7 January 2009 (UTC)

[edit] mySQL to PostgreSQL

I described the process of changing from mySQL to PostgreSQL at http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql.

Phispi 22:31, 14 January 2009 (UTC)

[edit] tsearch2

I did this once; if this is the right page to read about why the postgreSQL install of M-W is complaining about tsearch, it might be nice if it had a link to ... where did I find it? on postgresql.org I think ... hafta go back through that process I guess.

assuming you have already created the database and the user that will operate the wiki, then you have to, as a suitably priviledged DB-user, issue the command:

psql -U psql wikidb < /usr/share/postgresql/contrib/tsearch2.sql

assuming that the SQL command is located as above, in /usr/share/.... however the wiki dbuser needs also to be priviledged to access/use the newly created tsearch2 DB tables.

then when you hit 'install mediawiki' it actually works, and is using your postgres server instead of needing mysql

[edit] So, does it work well or not?

This page needs an executive summary.

Yes it works. 62.140.253.6 08:49, 23 August 2009 (UTC)