Page MenuHomePhabricator

Postgres database code does not support multiple schemas
Closed, ResolvedPublic

Description

Author: gregory.murphy

Description:
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.


Version: 1.10.x
Severity: normal

Details

Reference
bz11136

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
InvalidNone
ResolvedNone

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 9:53 PM
bzimport set Reference to bz11136.
bzimport added a subscriber: Unknown Object (MLST).

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.

gregory.murphy wrote:

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.

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?

gregory.murphy wrote:

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.

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;
}
Jdforrester-WMF subscribed.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.