Using schema prefixes with PostgreSQL
This page discusses usage of PostgreSQL schemas for prefixes. "Normal" prefixes can be used in the same way as in MySQL, so they won't be discussed here.
PostgreSQL has something called 'schemas' (http://www.postgresql.org/docs/current/static/ddl-schemas.html). They can be very handy sometimes, but if you don't know what they are, you probably don't actually need them and can stop reading here.
Schemas can be used as prefixes within Drupal. That is, with a multisite setup, each site can reside in its own schema, and shared tables can reside in a "shared" schema (or even in the public schema).
There is one annoyance: the upgrade will fail. This is unfortunate, but nothing can be done as the "normal" (not schema) and schema prefixes are just incompatibile. If you are interested in the details, please see http://drupal.org/node/40034.
But, don't worry. This can be easily fixed by changing the update script (update.php and updates.inc) a bit. The problem lies in the CREATE [UNIQUE] INDEX and ALTER TABLE ... DROP/ADD CONSTRAINT statesments. When schema prefixes are used, queries like this are executed:
CREATE INDEX prefix.search_total_word_idx ON prefix.search_total(word)
ALTER TABLE prefix.boxes DROP CONSTRAINT prefix.boxes_title_key
ALTER TABLE test.contact ADD CONSTRAINT test.contact_category_key UNIQUE (category)The prefix must be removed from the index and constraint name--that is they must be changed to:
CREATE INDEX search_total_word_idx ON prefix.search_total(word)
ALTER TABLE prefix.boxes DROP CONSTRAINT boxes_title_key
ALTER TABLE test.contact ADD CONSTRAINT contact_category_key UNIQUE (category)You can easily search for CREATE INDEX, CREATE UNIQUE INDEX and ADD/DROP CONSTRAINT statements and remove the {} from index/constraint names.
The best way is to run a test upgrade. You'll see a list of failed queries and it will be easier for you to change them.
Another remark: you can't use prefix.sh to prefix the tables, it will produce incorrect CREATE [UNIQUE] INDEX queries. This, also, can be easily fixed, by changing:
s/^CREATE INDEX \(.*\) ON /CREATE INDEX $PREFIX\\1 ON $PREFIX/;
s/^CREATE UNIQUE INDEX \(.*\) ON /CREATE UNIQUE INDEX $PREFIX\\1 ON $PREFIX/;to:
s/^CREATE INDEX \(.*\) ON /CREATE INDEX \\1 ON $PREFIX/;
s/^CREATE UNIQUE INDEX \(.*\) ON /CREATE UNIQUE INDEX \\1 ON $PREFIX/;An alternate approach
Here's what you need to do if you want to solve it this way:
1. Add the following at the top of your database.pgsql file:
CREATE SCHEMA schemaname;
SET search_path TO schemaname;2. Edit drupal/includes/database.pgsql.inc, replacing the function db_connect() with:
<?php
function db_connect($url) {
$url = parse_url($url);
$db_and_schema = explode(".",substr($url['path'], 1));
$conn_string = ' user='. $url['user'] .' dbname='. $db_and_schema['0'] .' password='. $url['pass'] . ' host=' . strtr($url['host'],'+','/');
$conn_string .= isset($url['port']) ? ' port=' . $url['port'] : '';
$connection = pg_connect($conn_string) or die(pg_last_error());
if(!empty($db_and_schema['1'])) pg_query('SET search_path TO '.$db_and_schema['1']);
return $connection;
}
?>3. Finally, use a db_url akin to such in your settings.php file(s):
$db_url = 'pgsql://user:password@+tmp/dbname.schemaname';- Not thoroughly tested, but works for me (on 4.6.3).
- This also fixes the inability to specify a Unix socket as the host - the +tmp gets replaced with /tmp for pg_connect().