Last modified: 2012-08-04 20:48:41 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 11136 - Postgres database code does not support multiple schemas
Postgres database code does not support multiple schemas
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
All All
: Normal normal (vote)
: ---
Assigned To: Nobody - You can work on this!
Depends on:
Blocks: postgres
  Show dependency treegraph
Reported: 2007-08-31 14:24 UTC by Gregory Murphy
Modified: 2012-08-04 20:48 UTC (History)
1 user (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Description Gregory Murphy 2007-08-31 14:24:25 UTC
With mysql, the wgDBprefix config parameter can be used to install more than one "instance" of mediawiki in the same database. There is a wgDBmwschema config parameter, but it has no effect on which postgres schema is used. Instead, this is determined by the database user's schema search path.

In other words, the only way to install more than one "instance" of mediawiki in the same postgres database is to assign each a different database user.

It would be simple matter to change this. The function tableName() in DatabasePostgres.php could use a prefix such that schema-qualified names are always returned, e.g. "schema.table". Alternately, the function open() in DatabasePostgres.php could be modified to reset the current schema search path.
Comment 1 Greg Sabino Mullane 2007-09-02 18:08:55 UTC
Resetting the current schema search path would be expensive, as it would need to happen for every single database call. The tableName change could work, but it seems no harder to simply change the wgDBuser config parameter. wgDBmwschema is currently only used for the setup: a note to that effect should probably be added to the LocalSettings.php file.
Comment 2 Gregory Murphy 2007-09-02 18:18:12 UTC
Couldn't the search path be set just once per request, when a connection is initialized? I don't think that this would be costly, certainly not when compared to the cost of parsing and executing a statement that retrieves data.

The problem with requiring that every schema have a unique user is that it can make it difficult to pool DB connections. In an environment where multiple instances of MediaWiki are hosted, for example, and postgres DB connections are pooled using something like pgbounce, each pooled connection is unique to a user. So if there is one DB user for all MediaWiki schemas, then the app need pool only N connections, where N is the expected number of simultaneous requests. If there is a user per schema, and there are M schemas, then the pool must contain N*M connections.
Comment 3 Greg Sabino Mullane 2007-09-02 18:44:20 UTC
Good point about pgbouncer, but I don't know that it will really affect that many users of MW. I'm hesitant to put something in that would require every statement to be schema prefixed. Setting the search path would be the better way to go, I suppose. Let me mull this over, perhaps we can have the default behavior stay the same, and append a different search path for those that use more than one MW instance with the same DB and user. Perhaps a $wgDBaltpgschema or something?
Comment 4 Gregory Murphy 2007-09-02 19:33:31 UTC
Agreed that the use case is not a common one (it just happens to be my use case, of course). I like the suggestion of an alternate configuration parameter. If not set, then MediaWiki need issue no SET command, so most users are not affected.

Comment 5 Greg Sabino Mullane 2007-09-06 17:10:31 UTC
How about a compromise? Setting wgDBmwschema has no effect unless it has been set as something other than "mediawiki", the default. A very slight overhead for those that have changed the default, and it should allow you to run more than one wiki on the same database and user. Something like this:

Index: DatabasePostgres.php
--- DatabasePostgres.php        (revision 25591)
+++ DatabasePostgres.php        (working copy)
@@ -486,6 +486,15 @@
                        $this->doQuery("SET client_min_messages = 'ERROR'");
+               global $wgDBmwschema, $wgDBts2schema;
+               if (isset( $wgDBmwschema ) && isset( $wgDBts2schema )
+                       && $wgDBmwschema !== 'mediawiki'
+                       && preg_match( '/^\w+$/', $wgDBmwschema )
+                       && preg_match( '/^\w+$/', $wgDBts2schema )
+               ) {
+                       $this->doQuery("SET search_path = $wgDBmwschema, $wgDBts2schema, public");
+               }
                return $this->mConn;
Comment 6 Greg Sabino Mullane 2007-09-19 02:32:08 UTC
Okay, applied this as r25929.

Note You need to log in before you can comment on or make changes to this bug.